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. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “conditional drop down list excel”. Start on a copy or a tiny sample, keep the affected dropdown visible, and compare the result with the outcome you expected before you touch the real workbook. 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. The point is a data step that keeps your analysis trustworthy, but the practical win is that someone else can open the file and understand what happened without asking you.
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. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.
In Google Sheets
Function availability: FILTER: Excel 2021+. These are Microsoft's version markers; earlier perpetual releases do not include these functions. Check the actual product and update channel before using the formula. A marker does not establish an identical Web/Mac workflow or Google Sheets support. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. For “conditional drop down list excel”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
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.