A dependent drop-down narrows the second list based on the first — pick a Country and the State list shows only that country’s states. The classic build uses named ranges plus INDIRECT.
How to set it up
Lay out each category’s items in its own column, then create a named range for each column whose name exactly matches the category value.
| A | B | C | |
|---|---|---|---|
| 1 | Fruit | Veg | Dairy |
| 2 | Apple | Carrot | Milk |
| 3 | Banana | Pea | Cheese |
| 4 | Cherry | Kale | Yogurt |
The formula
The second drop-down’s Source (where A2 holds the category chosen in the first drop-down):
How it works
The build has three parts:
- Create the first drop-down normally — a List validation of the category names (Fruit, Veg, Dairy).
- Select each category’s list of items and create a named range whose name is exactly that category (select B2:B4, type
Vegin the Name Box). Names must match the first list’s values exactly — no spaces. - For the second cell, set Data Validation → List → Source to
=INDIRECT(A2). - Now choosing “Veg” in A2 makes INDIRECT return the Veg named range, so the second drop-down lists only Carrot, Pea, Kale.
Spaces are the usual culprit. Named ranges can’t contain spaces, but a category like “New York” can. Substitute the space out in both the name and the formula: name it New_York and use =INDIRECT(SUBSTITUTE(A2," ","_")).
Try it: interactive demo
Pick a category; the second list updates to match — exactly what INDIRECT does.
Variations
Handle spaces in category names
Swap spaces for underscores in both name and formula:
Modern alternative: FILTER (Excel 365)
Skip named ranges — drive the second list from a spill:
Three levels deep
Chain it: the third drop-down’s Source is =INDIRECT(B2), where B2 is the second selection.
Pitfalls & errors
Second list is empty or errors. The named range name doesn’t exactly match the first cell’s value — check spelling, capitalization, and stray spaces.
Changing the first cell doesn’t clear the second. An old selection can remain invalid. Add a formula or a small macro to blank the dependent cell when the parent changes, or just re-pick.
INDIRECT is volatile. It recalculates a lot; on huge models that can slow things down. The Excel 365 FILTER approach avoids INDIRECT entirely.
Practice workbook
Frequently asked questions
How do I create a dependent drop-down list in Excel?
Why is my dependent drop-down empty?
Can I make cascading drop-downs without INDIRECT?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class