Dependent drop-down list in Excel: the second list follows the first

How a drop-down list works in Excel

A drop-down list is a data validation rule. Select the input cell, open Data > Data Validation, choose Allow: List and enter a Source. The source can be a typed list such as Fruit,Herbs,Vegetables, a range such as =$D$2:$D$4, a spill reference such as =$D$2#, or a formula that returns a reference, such as INDIRECT or OFFSET.

Two details matter for dependent lists. First, the Source box does not accept a formula that returns an array, such as FILTER or UNIQUE written directly into the box, so the dynamic list has to live in helper cells that the source points to. Second, the rule only checks entries typed or picked in the cell. Values pasted into the cell overwrite the rule, and a value that was valid stays in the cell when the first list changes. Both points are covered by a check formula in the example.

Step by step: the FILTER method (Microsoft 365, Excel 2021+)

1. Keep the choices as a two-column table: category in A2:A11, item in B2:B11. New items are simply new rows; the order does not matter.
2. In D2, enter =SORT(UNIQUE(A2:A11)). It spills the three categories Fruit, Herbs and Vegetables into D2:D4.
3. Select E14, the first drop-down cell, and set Data Validation to List with the source =$D$2#. The # sign refers to the whole spill range, so a new category appears in the list automatically.
4. In E2, enter =SORT(FILTER(B2:B11,A2:A11=E14)). With Fruit in E14 it spills Apple, Cherry, Pear and Plum into E2:E5.
5. Select E15, the second drop-down cell, and set the source to =$E$2#. Pick Herbs in E14 and the second list immediately shows Basil, Mint and Thyme.

This method needs no named ranges and no special sorting, and it works for any number of categories. Hide the helper columns or move them to a separate sheet if they should not be visible; the drop-down cells keep working. If the first cell is still empty, FILTER finds nothing and returns #CALC!; add a third argument, for example FILTER(B2:B11,A2:A11=E14,""), to show an empty list instead.

The classic INDIRECT method (all Excel versions)

Before dynamic arrays, the standard way was one named range per category. Put the items of each category in their own column, select them and type the category name into the Name Box, for example Fruit for the four fruit cells. The second drop-down then uses the source =INDIRECT($E$14): INDIRECT turns the text "Fruit" in E14 into a reference to the range called Fruit.

This works in every version, but it is fragile. Names cannot contain spaces, so a category such as "Root vegetables" needs a name like Root_vegetables and the source =INDIRECT(SUBSTITUTE($E$14," ","_")). Each new category needs a new name, and a typo in a name silently gives an empty list. INDIRECT is also volatile, which means it recalculates with every change in the workbook. For new files the FILTER method is easier to maintain; see named ranges for how names and their scope work.

Worked example: categories and items with two helper lists

The yellow cells are the source table (A2:B11) and the two drop-down cells E14 and E15. Columns D and E are the helper lists the drop-downs point to. Hover over a green cell to see its formula.

Worked example: categories and items with two helper lists

	A	B	C	D	E
1	Category	Item		List 1: categories	List 2: items
2	Fruit	Apple		Fruit	Apple
3	Vegetables	Carrot		Herbs	Cherry
4	Fruit	Pear		Vegetables	Pear
5	Herbs	Basil			Plum
6	Vegetables	Leek
7	Fruit	Plum
8	Herbs	Mint
9	Vegetables	Kale
10	Fruit	Cherry
11	Herbs	Thyme
12
13	Drop-down cells and checks				Value
14	Drop-down 1: category (Source =$D$2#)				Fruit
15	Drop-down 2: item (Source =$E$2#)				Plum
16	Number of items in the chosen category				4
17	Does the item still belong to the category?				Valid
18	Category of the chosen item (reverse lookup)				Fruit
19	Number of categories in drop-down 1				3
20	Items of the chosen category as one line				Apple, Cherry, Pear, Plum

Fictional lists. In Excel, D2 and E2 each hold one spilling formula and the drop-downs use the sources =$D$2# and =$E$2#; the grid shows each spilled value in its own cell with INDEX(…,k) so it can be checked.

Keep the two cells consistent

Data validation does not clear the second cell when the first one changes. If someone picks Fruit and Plum and then switches the category to Herbs, E15 still says Plum. A check formula catches this: =IF(COUNTIFS(A2:A11,E14,B2:B11,E15)>0,"Valid","Pick again") counts the rows with exactly this category and item, so it returns Valid for Fruit and Plum and Pick again after the switch. Use the same test in a conditional formatting rule to colour the cell, or in a total that should only count valid rows.

On the Error Alert tab of the validation dialog, the style Stop blocks entries that are not in the list, while Warning and Information allow them after a message. For an input form, Stop is usually right. The option Ignore blank lets the cell stay empty, and In-cell dropdown must be ticked for the arrow to appear.

More than two levels, tables and other tips

A third level, such as category, item and variety, follows the same pattern: a third column in the source table and a helper list with two conditions, =SORT(FILTER(C2:C50,(A2:A50=E14)*(B2:B50=E15))). Each level is just another FILTER condition, multiplied for AND.

If the source data is an Excel table, the validation Source box does not accept structured references such as Table1[Category] directly. The helper formulas can use them, though, and the spill references =$D$2# and =$E$2# then pass the result to the drop-downs. To show the chosen category's items on a form, =TEXTJOIN(", ",TRUE,SORT(FILTER(…))) joins them into one line: Apple, Cherry, Pear, Plum.

Common mistakes

• Typing FILTER into the Source box. Data validation rejects formulas that return arrays. Put the FILTER formula in a helper cell and use its spill reference, such as =$E$2#, as the source.
• Forgetting the # in the source. =$E$2 gives a list with one entry. =$E$2# includes the whole spill range and grows with it.
• Relative references in the rule. A source like =INDIRECT(E14) applied to several rows shifts in each row. Use $E$14 for one fixed input, or a relative reference on purpose when every row has its own first drop-down.
• Names that do not match the category. With the INDIRECT method, "Root vegetables" cannot be a name, and an extra space in the category text breaks the link. Use SUBSTITUTE and clean the values with TRIM.
• Relying on validation alone. Pasted values and changed first selections are not checked. Add a check formula like the COUNTIFS test in the example.