In a previous post we learned how to make an Excel drop-down menu in 3 minutes. In this post I will show a very easy way to make a drop-down list in Excel that changes depending on what you have chosen in another cell. It’s easy, and it only takes five minutes!
Step 1: Enter the data
It is absolutely critical that the data table is set up correctly: The main category (the countries in this example) should be in one column, and the sub-categories (the cities) in the other columns, with the countries repeated as headings.
Important: The heading of each sub-category must be exactly the same as the name in the main category.
Step 2: Name the ranges
Select all the entries in a column and use the shortcut Ctrl + Shift + F3 to create a name for the range. Repeat this with every column. It is very important that the names of the sub-category ranges are the same as the names in the first column, otherwise the dynamic drop-down list won’t work.
Step 3: Make a drop-down menu for the main category
Let’s make the first drop-down, the one for the countries. Select a cell, e.g. I3, and choose Data Validation on the Data tab (or shortcut Alt→D→L). Choose List, put the cursor in the Source field and press F3 to open the Paste Name window. Choose the range Categories.
Step 4: Make the drop-down menu for the sub-categories
Select a cell, e.g. J3, and choose Data Validation on the Data tab (or shortcut Alt→D→L). Choose List, put the cursor in the Source field and type this formula: =INDIRECT(I3)
That’s it, we have created a dynamic drop-down menu! If you change countries in I3, the list in J3 will update!
With this approach the sub-categories don’t reset when you change the main category. I found a great article at Chandoo.org that deals with that problem with some VBA code: Dynamic (Cascading) Dropdowns that reset on change
- Create a drop-down list in Excel in 3 minutes!
- Youtube (in Norwegian): Lag en Drop-down-meny i Excel på 1 minutt!
- Create a search field in Excel in 5 minutes
Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.