In Excel: name each second-level list after the first-level item it belongs to, then give the second drop-down Data ▸ Data Validation ▸ Allow: List with Source =INDIRECT(A2), so its options change with the choice in A2.
On this page7
Put each category's items in its own column with the category name above.
Select each item block and name it exactly as its category in the Name Box (no spaces — use underscores, e.g. Soft_Drinks).
Give the first cell Data ▸ Data Validation ▸ Allow: List with the category headers as Source.
Give the dependent cell Allow: List with Source =INDIRECT(A2), adjusting A2 to the first cell.
In Microsoft 365, alternatively put =FILTER(Items,Categories=A2) in a helper cell and use its spill reference (e.g. =$J$2#) as the Source.
What this does
A dependent drop-down shows options that depend on another cell. The classic build uses named ranges: the first list offers categories, each category has a named range holding its items, and the second list's Source is INDIRECT pointing at the first cell — INDIRECT turns the chosen text into a reference to the range with that name. In Microsoft 365 you can instead let FILTER build the second list in a helper cell and point the validation Source at its spill range. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “dependent dropdown lists in excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected dropdown change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a data step that keeps your analysis trustworthy useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
F1:H1 hold Fruit, Vegetable and Grain, with their items underneath (F2:F4 Apple, Banana, Cherry; G2:G3 Carrot, Pea; H2:H3 Rice, Oat). Select F2:F4 and type Fruit in the Name Box, then name G2:G3 Vegetable and H2:H3 Grain. Give A2 a list with Source =$F$1:$H$1 and B2 a list with Source =INDIRECT(A2). Choosing Vegetable in A2 makes B2 offer only Carrot and Pea. Dependent lists stop impossible combinations — a city that is not in the chosen country, a product outside the chosen category — at the moment of entry, which keeps lookups and reports clean. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
In Google Sheets
Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. If you take one thing from this page on “dependent dropdown lists in excel”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
Common mistakes
- Category text that does not exactly match a range name (spaces, typos), so
INDIRECTreturns an error and the list is empty. - Changing the first choice without clearing the second, leaving an item from the old category.
- Using spaces in names, which named ranges do not allow — substitute underscores in both the names and the category text.
- Referencing a table column directly in the Source box; wrap it in a named range or
INDIRECTinstead.
Frequently asked questions
Why does my dependent drop-down show nothing?
INDIRECT cannot find a range named exactly like the first choice. Check the Name Manager for spelling and spaces.
Can I avoid named ranges?
In Microsoft 365, build the list with FILTER in a helper cell and point the validation Source at its spill range with the # reference.
How many levels can I chain?
As many as you need: each further list uses INDIRECT on the cell before it, with ranges named after the previous level's items.