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!