How to create a dependent dropdown list in Excel
Formula (Excel and Google Sheets)
=SORT(FILTER(B2:B12,A2:A12=E1))Build the second list with FILTER in a helper cell, =SORT(FILTER(B2:B12,A2:A12=E1)), then point the second dropdown's Data Validation source at it with =$G$2#.
- Result on the example
- a 4-row list that spills from G2 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER returns the items in the chosen category and SORT puts them in order. The list spills from G2, and the reference $G$2# means "the whole spilled range", however long it is. Use that as the Source of a List validation on E2, and the second dropdown always offers the items of the category picked in E1.
=SORT(FILTER(B2:B12,A2:A12=E1))Result{4 rows × 1 col} "Carrot", "Kale", "Onion", "Spinach"
B2:B1211 rows × 1 col: "Mango", "Spinach", "Yogurt", "Apple", …A range: 11 cells in one column.
A2:A1211 rows × 1 col: "Fruit", "Vegetable", "Dairy", "Fruit", …A range: 11 cells in one column.
E1"Vegetable"The value in E1.
A2:A12=E1{11 rows × 1 col} FALSE, TRUE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
FILTER(B2:B12,A2:A12=E1){4 rows × 1 col} "Spinach", "Carrot", "Kale", "Onion"FILTER: Rows of a range that meet every condition. #N/A when none match.- range
B2:B1211 rows × 1 col: "Mango", "Spinach", "Yogurt", "Apple", … - condition1
A2:A12=E1{11 rows × 1 col} FALSE, TRUE, FALSE, FALSE, …
- range
SORT(FILTER(B2:B12,A2:A12=E1)){4 rows × 1 col} "Carrot", "Kale", "Onion", "Spinach"SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.- range
FILTER(B2:B12,A2:A12=E1){4 rows × 1 col} "Spinach", "Carrot", "Kale", "Onion"
- range
In Google Sheets
The same formula works in Google Sheets: =SORT(FILTER(B2:B12,A2:A12=E1)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 4 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORT(UNIQUE(FILTER(B2:B12,A2:A12=E1))) | spills 4 × 1 | Add UNIQUE when an item can appear more than once in a category, such as a raw sales list. |
Common mistakes
- Build the list in cells and point the dropdown at it with #. Typing the FILTER into the Data Validation source box is not a reliable substitute.
- Changing the category doesn't clear the item already chosen in
E2, so it can be left showing an item from the old category. Add a check such as=COUNTIFS(A2:A12,E1,B2:B12,E2)=0to flag it. - Keep the helper cells below the spill empty. Anything typed there makes the list #SPILL! and the dropdown stops working.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Create a dependent dropdown list
G2, list the items whose category matches the choice in E1, A to Z, so the list can feed the Item dropdown in E2.Questions
How do I make the first dropdown list the categories automatically?
Spill them into another helper cell with =SORT(UNIQUE(A2:A12)) and use that cell with # as the first dropdown's source.
How do I do this in Google Sheets?
Use the same FILTER formula in G2, then Data > Data validation > Dropdown (from a range) with G2:G20. Sheets ignores the empty cells.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.