Creating and Using Dropdown Menus in Excel

Thomas Zucker-ScharffSenior Data Analyst
CERTIFIED EXPERT
Veteran in computer systems, malware removal and ransomware topics.  I have been working in the field since 1985.
Published:
Updated:
Do you want to increase the data integrity of your Excel worksheets?  Do you input something repeatedly?  Would it be handy to pick the item from a predetermined (by you) list?  READ ON!

Every time I sit down with my wife I learn something new (that’s 40 years of new stuff!). Recently, she brought home a problem from work. She wanted to find out how many classes there were in total and how many people there were in each type of class. The way the data was set up listed each class separately with a column denoting how many people were in each class. 


The answer to this seemed relatively simple, query the class name and return the class total if it matched certain criteria. There was a problem though. Because each class name had been entered manually, there were errors in spelling, formatting, etc. This made exact matching problematic. I suggested a solution – one that I had learned a previous time we sat down together.


I suggested that she put all the class names in another tab in the spreadsheet and make the class name field a dropdown list. In this way each time she chose a class it would be the same. The procedure for doing this is as follows (The images and steps used are from Excel 2016):


  1. Create a new tab

  2. In the new tab list each of the items you want in the list (this can be changed later)


  3. In the original tab click on the cell you want to have the dropdown list appear in
    1. Note that if the cell already has data in it, it will keep it as long as that data item is in the list created in step 2 above

  4. Click on the data menu
    1. Select data validation
    2. Select data validation again



      • Select list


      • Click the icon next to “Source” to get the single line dialog (2nd Picture below)


      • Go back to the tab with the list of names
      • Highlight the list (this makes the formula in the Data Validation box)



      • Click the icon again
      • Click ok


Now that you have created a cell with a dropdown list, you might want to copy the result to the next cell or group of cells. Copying is the easiest way to do this (you can use the key combinations CTRL-C to copy and CTRL-V to paste). Using the format painter does NOT work. 


Alternatively, you can highlight an entire section in step 3 above, instead of one cell. It is best to use this alternative method on a new worksheet WITHOUT data in it. Also, before making any changes to an existing spreadsheet tab always make a copy and make the changes in the copy.


How to make a copy of a tab


  1. Right click on the source tab and choose “Move or copy…”
  2. Check the “Create a copy” checkbox
  3. Highlight the sheet you want the new copy to appear BEFORE (I always recommend selecting the tab you are copying)
  4. Click okay


The new tab will be named the same as the old one followed by “(2)” to denote it is the second tab with that name. Work on this copy. If you like the changes you can always delete the original first and rename this tab to the original’s name by deleting the “ (2)”.


Once you have determined that everything is working as expected, you can delete the original tab as outlined in the previous paragraph.  It is important to check BEFORE deleting the original tab that any formulas are now pointing to the new tab (click in the cell and look in the bar at the top to check the formula, you can also do a search on the name of the tab using CTRL-F).  If the tab is deleted and there are formulas pointing to it, you will see the dreaded "#REF!" result in the spreadsheet - which means the formula REFers to something that is no longer there!


If you are at all confused or wish to watch how this type of dropdown is created, check out the mico-tutorial video I made illustrating this.


The Takeaway


Although this article shows you how to create dropdown lists in Excel, if you are doing so, it means that you should probably be using a relational database (Microsoft Access, is one of the easier ones to use).  That said, using dropdowns or lookup tables is the best way to assure data integrity.  If I use Excel for this, I tend to make one tab that houses all my lists (I also tend to name the list ranges to make it a little easier to read the formulas - "=totalfunds" is easier to understand than "=AA72").


If you have any questions Don't hesitate to use the blue "Ask a Question" button at the top of the page, or comment on this article!


If you think this article was helpful, please do click the Thumbs Up icon to the bottom left of this text. It helps me out and lets me know the direction I should take for future articles that I write. 


3
1,800 Views
Thomas Zucker-ScharffSenior Data Analyst
CERTIFIED EXPERT
Veteran in computer systems, malware removal and ransomware topics.  I have been working in the field since 1985.

Comments (4)

Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2023

Commented:
Although this article shows you how to create dropdown lists in Excel, if you are doing so, it means that you should probably be using a relational database
Are you saying that for simple Data Validation that I should use Access instead of Excel? If so, why.
Thomas Zucker-ScharffSenior Data Analyst
CERTIFIED EXPERT

Author

Commented:
Yes that is what i am saying.
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2023

Commented:
I don't mean to be argumentative but can you explain your reasoning?
Thomas Zucker-ScharffSenior Data Analyst
CERTIFIED EXPERT

Author

Commented:
Sure. The purpose of a database is to enter data once and have it displayed in many different ways and also relate that data to other areas of the relationship db (using tables and queries).  When you use a dropdown list in excel, you are basically using it like a simple database.   Di why not use a db like access? It is as easy to use as excel.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.