# How to create a dependent dropdown list in Excel

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#`.

**Excel:** `=SORT(FILTER(B2:B12,A2:A12=E1))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Category | Item |  | Category | Vegetable |  | Items for the list |
| Fruit | Mango |  | Item |  |  |  |
| Vegetable | Spinach |  |  |  |  |  |
| Dairy | Yogurt |  |  |  |  |  |
| Fruit | Apple |  |  |  |  |  |
| Vegetable | Carrot |  |  |  |  |  |
| Fruit | Pear |  |  |  |  |  |
| Dairy | Butter |  |  |  |  |  |
| Vegetable | Kale |  |  |  |  |  |
| Fruit | Banana |  |  |  |  |  |
| Dairy | Milk |  |  |  |  |  |
| Vegetable | Onion |  |  |  |  |  |

Formula in G2: `=SORT(FILTER(B2:B12,A2:A12=E1))`

Result (spills 4 × 1): Carrot; Kale; Onion; Spinach

## 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`.

## Other ways

- `=SORT(UNIQUE(FILTER(B2:B12,A2:A12=E1)))`: 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)=0` to flag it.
- Keep the helper cells below the spill empty. Anything typed there makes the list #SPILL! and the dropdown stops working.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/create-a-dependent-dropdown-list