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
Related articles:
- Create a drop-down list in Excel in 3 minutes!
- How to find cells that contain a formula in Excel
- Create a search field in Excel in 5 minutes
Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:
Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish
Hello,
Thank you for explaining this! However, after following the instructions, I get an error when I try to create the drop-down menu for the sub-categories. It says “The source currently evaluates to an error. Do you want to continue?”
I don’t think I am missing anything, but perhaps you can enlighten me!
I had the same problem and changed category names to being one word only (no ‘_’ or ‘-‘ as well).
I realised as some categories worked and others didn’t before I changed all to one word names. An error message still appeared but all seems to be in order!
Hi Robert,
Check if your INDIRECT formula points to the right cell, i.e. the cell with the drop-down for the main categories.
/Audun
Is there an easy way to extend this validation to an entire column, so that the value in T3 formats the dropdown in V3, T4 formats the dropdown in V4 and so on? I tried using the format painter and just copying and pasting the formula and neither works. Thanks!
I was just wondering the same… Do you have an answer for that?
Pingback: Dynamic Drop Down Menu |
Very nice and easy.
Maybe add that the names of the categories can’t exists of multiple words.
You have to add _ between the separate words instead of a space, or you will get errors.
Pingback: Creating a list of unique items, which fit multiple criteria
My values in the drop-down menu don’t match the values that appear in my worksheet. How do I make adjustments so I get what I expect? Thanks.
If you have named the ranges with exactly the same names as in the Category column it should work.
Great article! I am creating an expenditure tracking sheet with categories and subcategories and have something to add. I was having trouble with 2 but not all of my subcategories. It seems that the titles have to be all one word. GROCERY FOOD or GROCERY (FOOD) do not work, but GROCERY_FOOD works 🙂