How to Create a Drop-down Box in Excel

My husband called me into his office recently and asked me how to do a drop-down box in Excel.  When I finished showing him, he asked me how I knew how to do it.  I am not sure....I just do.  lol!  So, I thought I'd do a blog post on how to do it easily.  This way he can refer to my post if he needs to remember how to do it.  There are other ways of doing drop-downs, but I find this to be the easiest. 

Drop-down boxes are VERY convenient if you have to choose the same items over and over.  Since a great Coupon blog recently linked to my site and because I am just getting into the whole "couponing" thing.....I'm going to use a "Coupon" spreadsheet as my example.

My Coupon Spreadsheet (on Sheet1):

I do not like to type things out if I don't have to.  I would rather click a drop down or just type the first letter.  With drop-downs you can do either.

I'm going to create the items for my drop-down on a different sheet than my actual coupon sheet.  If you have a blank "Sheet2" sheet at the bottom of your workbook this is where we will create the drop-down items.  If your Sheet2 is not blank just add a new sheet by right-clicking a sheet and choosing Insert...and then worksheet.  Now, I've created two columns on Sheet2.  One column with my "Store" names and one with my "Coupon Price".  These items will be what shows up in my drop-down when I click it from my main spreadsheet.

Now that I have the items in my drop-down entered into my Sheet2 I will need to tell Excel that these are the items I want to use in my first drop-down.  Highlight the store names (do not include the header) and then click on the Formulas ribbon/toolbar and choose Define Name (see below).

Now, I want to give my drop-down list a name.  I named mine "store" and then click OK.  The items in "Scope" and "Refers to" will be automatically entered for you.

Now, do the same thing for the "Coupon Price" items in column B of Sheet2.  Highlight the price items in column B (do not highlight the header), click on Define Name and then name your drop-down list.

You should now have both of your drop-down lists named.  Go to your original sheet (mine is Sheet1).  Now, highlight column C, then click on the Data ribbon/toolbar and choose Data Validation.

This is where we tell Excel what drop-down list we want it to use for column C.  Once the Data Validation window opens choose "List" in the Allow field (see orange arrow below).  Then, tell it your list source (red arrow) which we named "store" earlier.  You will need to enter an "=" before your source name.  So, it will be "=store". Click OK.

Now, when you click on a cell in column C you will have your drop-down list of stores available to you.

For column B you will do the same thing.  Highlight column B, choose Data Validation, choose List from the drop-down and then enter in "=" followed by what you named your second drop-down list earlier.  You will see below that we now have our second drop-down with our list of coupon amounts.  You can either click the button on the drop-down to add your items or just type the first few letters/amounts and it will pop-up with your item.

This was VERY easy.  Here is my spreadsheet all filled in.  I am all ready to go to the store. I am all done and it is ready for me to reuse next time!

If you need to add items to your drop-downs at a later time it is very easy.  Go to your spreadsheet where you listed out your drop-down items.  In the steps above our sheet was sheet2.  Add your items.  Then,  click on the Name Manager button on your Formulas ribbon/toolbar.

Once it pops up with your name manager, highlight which list you need to update followed by the Edit... button.

It will pop up with the Edit Name window (below).  You would just need to change your "Refers to" location to include the new rows of items.  If your data ended at A8 before and now it is A10.  Just change the $A$8 to $A$10 and you will now have the new items in your drop-down box.

Thank you for reading my Tech blog!  Good luck!

Excel (Combining or Separating Data in Columns)

Are you ever working in a spreadsheet and need to combine two columns or separate two columns? Below you will find the instructions on how to add two columns together as well as how to separate two columns to meet your needs. 

Let's pretend we have a spreadsheet that contains the information for 300 of our clients.  We would like to pull together a mailing list, but the Last Name and First Name fields are separated.  We would need to combine them.  Of course we can do this automatically in Word using a Mail Merge, but lets just say for this example that is not an option.

Below is an example of our spreadsheet.

We need to add a blank column to column C (to do this, highlight column C, right-click and choose Insert.  Now, add a column header of "Full Name").  Now, in C2 we will start the Concatenate function.

On the Formula toolbar you will see a button called "Insert Function".  DO NOT be afraid of this button.  It is a wonderful tool!  While cell C2 is selected click on the Insert Function button (I've highlighted it in red below).  The "Insert Function" window will pop-up.  In the "Search for function:" field type in Concatenate and hit the the "Go" button.  The Concatenate function will now show up in the "Select a function" box.  Highlight it and then hit the "OK" button.

Now, you will see the function wizard (see below).  In the "Text1" box enter in "B2" or click the B2 cell and it will automatically add B2.  Now, if we put A2 into Text2 you will see that the function has concatenated the two columns, but they are all one word, "DarleneSmith".  See red box below.  This shows you what your function will look like if you hit OK.  Do not hit OK.  We we would like a space between the First and Last Name.

So, we would need to add a space. In Text1 we will leave it as is, "B2" and in Text2 we will add " " (double quote, followed by a space, followed by a double quote).  All text in a formula should be between double quotes.  Then, in Text3 we will add in A2 (to add the First Name in).  Now, click OK.

Once you click OK you will see "Darlene Smith" in C2.  You may now copy your function from C2 and place it in the remaining C cells so that the remaining names will combine.

Please note, if you wanted your field for Full Name to be "Last Name, First Name" you would've put "A2" in Text1, ", " in Text2 and then B2 in Text3.  This would've changed the Full Name for Darlene to "Smith, Darlene".  It really depends on what your needs are.

Now, if you had a full name field and you wanted to separate the names out you can use the "Text to Columns" button on the Data toolbar.  I've deleted columns A and B because we will be pretending we do not have a separate First Name and Last Name field.  That is what we are wanting in our spreadsheet.

What I would do first is move your Full Name column so it appears on the end of your spreadsheet (I moved it from column A to column F by highlighting column A, right-clicking and choose Cut, right-click column H and choose Insert Cut Cells).  My current spreadsheet looks like the below.

The reason we moved the column is because when it separates the names it will put the first name in column F and the last name in column H.  If we had left the Full Name field in column A the last name would've overwritten column B.

Now we need to highlight all the names in column F.  Then, click the Data toolbar and choose the "Text to Columns" button (I've highlighted the button in red below to show you which button it is).

Now, The Text Wizard is now up on your screen.  The first step is to tell the wizard if we want to use Delimited or Fixed width.  For the this example we are using "Delimited".  We have a character (the space) that we are going to have Excel use to determine where we want it to break our columns.  It could be any character (comma, semi-colon, a certain letter, a dash, etc).  Fixed width would be used if your field contained a value that was the same number of characters (for example, social security number with no dashes).  You could have excel parse the data to give you just the last 4 digits, but that isn't what we want here.  So, click on "Delimited" and then choose the "Next" button.

You will now see the 2nd step in the Convert Text to Columns Wizard.  The "Delimiter" may have  defaulted to "Tab".  We want it to be "Space" (see arrow below).  So, please uncheck anything that is checked and make sure only "Space" is checked.  Now, don't change anything else.  If you look down in the red box that I have highlighted below you will see that the "Data Preview:" line is separating the data right on the 1st character of the Last Name.  This is EXACTLY what we want.  Click the "Next" button.

Now, do not let the next step scare you.  This is just Excel wanting know what type of data you want your data to be.  Since we are dealing with text (and not numbers) we will choose "Text" from the "Column data format" and then click the Finish button.

You will now see that Excel has parsed the field and now First Name and Last Name are separate (see below). 

You would now need to change your column headers to match your data and do any formatting necessary.  I would just use the format painter for formatting the Last Name field.

Do not be afraid to play around with Excel functions and get to know them.  Just back up your data so that if you do mess something up you can go back to your original.  Have fun with Functions!

Also, please keep in mind that if you send me a message or comment below and tell me something you would like me to cover I will do a post on it.  I always welcome ideas!  I can post on Microsoft Word, Excel, Power Point, OneNote, Facebook or most basic computer questions. 

Let me know if I've helped you or if you've learned anything.  This will encourage me to do more posts.