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):
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
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.
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.