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. Treat “linked drop down list excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected dropdown shows up while it is still harmless. 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 turns a data step that keeps your analysis trustworthy into a method you can reuse, explain, and defend when the workbook leaves your screen.
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. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. For “linked 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.