Nested dropdown lists in Excel

05 Jun, 2026

Overview

Assumed knowledge:

• How to use data validation to create a dropdown list.
• Tables and how to refer to them in formulas.

Process:

1. Create a worksheet with categories as columns and items as rows. (We'll automate it.)
2. For top-category dropdown, apply data validation as a list.
3. For the secondary dropdown, apply data validation as a list.

1. Create a worksheet of source data

We want to create something like this:

We want the user to select Dogs, Fruits or Vegetables from one dropdown, and then select an option from a secondary dropdown (that displays only the items pertaining to the option selected in the primary dropdown).

We could create the above table by hand, but why make things harder for ourselves? We can automate it.

Automatically generate columns from another table of data

1. Create a source worksheet with a table of all your items and categories for easy addition. We will refer to this table as tableItems.

2. Create another worksheet; this will become the source for the dropdown lists. We will refer to this worksheet as Dropdown items.

3. Stick this formula in cell A1. (There's nothing magical about A1 in particular; this note just assumes you're using it.)

=TRANSPOSE(SORT(UNIQUE(tableItems[Category])))

(Where tableItems[Category] represents whatever table column reference your project uses.)

This will generate a row that (in our example) says

| Dogs | Fruits | Vegetables |

4. Stick this formula in cell A2 (i.e. just below the cell where you placed the formula in the previous step). Drag it across!

=FILTER(tableItems[[Item]:[Item]], tableItems[[Category]:[Category]]=A1, "Not found")

Notes:

• Note the nested brackets [[]:[]]. This 'locks' the table column references like $ does for cell refs, so the formula doesn't break when you drag it across.
• The Not found text is optional; replace it with whatever you want Excel to display instead of a generic error.

2. Create the top-category dropdown

Apply data validation as a list:

='Dropdown items'!$A$1#

Where Dropdown items is the worksheet from the previous step. The # indicates to accept the spill.

3. Create the secondary dropdown

Apply data validation as a list:

=XLOOKUP($A1, 'Dropdown items'!$A$1:$C$1, 'Dropdown items'!$A$2:$C$2)#

Notes:

• You can replace the naked refs in your formulas ('Dropdown items'!$A$1:$C$1 and 'Dropdown items'!$A$2:$C$2) with named ranges.

#excel