How to Make a Dependent Drop-Down List in Excel

A dependent drop-down list in Excel is a second list whose choices change with the first: pick "Veg" in one cell, and the next cell offers only Carrot, Kale and Leek. You build it with Data Validation plus a formula that returns the matching items. This guide shows the modern way with FILTER and UNIQUE (Microsoft 365 and Excel 2021), the classic INDIRECT way that works in every version, and how to stop the two lists getting out of step.

How a dependent drop-down works#

Every drop-down in Excel is a Data Validation rule with a list source. A dependent drop-down simply uses a source that changes with another cell. You need three parts:

1. A source table listing every category and its items.
2. A first drop-down for the category.
3. A second drop-down whose source is "the items for the category in the first cell".

Keep the source table on its own sheet, here called Lists:

The drop-downs go on a sheet called Form: category in B2, item in C2. If you have not made a drop-down before, start with how to create a drop-down list in Excel, which covers Data Validation step by step.

Method 1: FILTER and UNIQUE (Microsoft 365 and Excel 2021)#

This method reads straight from the source table, so new rows in the table appear in the drop-downs automatically.

Step 1: Build the category list#

On the Lists sheet, in D2, type:

=SORT(UNIQUE(A2:A8))

It spills Dairy, Fruit and Veg into D2:D4, each once. The UNIQUE function guide explains how it removes repeats.

Step 2: Make the first drop-down#

On the Form sheet, select B2 and go to Data > Data Validation (Windows shortcut: Alt, A, V, V). Choose Allow: List and type this in Source:

=Lists!$D$2#

The # is a spill reference: it means "the whole spill range that starts in D2", so the list grows if a new category appears.

Step 3: Build the matching items#

Back on the Lists sheet, in E2, type:

=SORT(FILTER(B2:B8,A2:A8=Form!B2))

FILTER returns the items whose category equals the choice on the Form sheet. If B2 says Veg, E2:E4 shows Carrot, Kale and Leek. The FILTER function guide covers its arguments, and the FILTER lesson lets you practise it.

Step 4: Make the second drop-down#

Select C2 on the Form sheet, open Data Validation again, choose List, and use:

=Lists!$E$2#

Now pick a category in B2 and the arrow in C2 offers only the matching items.

This method has one limit: each helper formula follows one cell. For a table where every row needs its own pair of drop-downs, use Method 2. For more spill-based lists, see dynamic drop-down lists that update themselves.

Method 2: Named ranges and INDIRECT (every version)#

This classic method works in any version of Excel and copies down to as many rows as you like. It needs the items arranged in columns, one per category, with the category name on top:

Step 1: Name each column#

Select G1:I4 and choose Formulas > Create from Selection, tick Top row only and click OK. Excel creates three names: Fruit (G2:G4), Veg (H2:H4) and Dairy (I2:I4). A name is a label for a range, and any sheet in the workbook can use it.

Step 2: Make the first drop-down#

Select B2 on the Form sheet and set its Data Validation list source to the headers: =Lists!$G$1:$I$1.

Step 3: Make the second drop-down with INDIRECT#

Select C2 and set its list source to:

=INDIRECT($B2)

INDIRECT turns the text in B2 into a reference. If B2 says Veg, INDIRECT("Veg") points at the named range Veg, and the drop-down shows its items. The $ before B locks the column but lets the row change, so you can copy C2 down to C3, C4 and so on, and each row follows its own category.

Two cautions:

• Spaces. A name can't contain spaces, so Create from Selection turns "Frozen Food" into Frozen_Food. Use =INDIRECT(SUBSTITUTE($B2," ","_")) to match.
• Blanks. Dairy covers I2:I4 but only I2 is filled, so its drop-down shows two empty lines. To avoid that, name just the filled cells with Formulas > Define Name.

How to keep the two lists in step#

Changing the first drop-down does not clear or change the second. If someone picks Veg and Kale, then switches the category to Fruit, C2 still says Kale. Data Validation only checks a cell when you type into it.

Without a macro, catch mismatches with a check formula next to each row:

=IF(COUNTIFS(Lists!A:A,B2,Lists!B:B,C2)=0,"Check","")

COUNTIFS looks for a row in the source table with both the category and the item. If there isn't one, the cell says Check. You can also use the same test in a conditional formatting rule to colour bad rows red.

Drop-downs that use another sheet#

Both methods keep their lists on a separate sheet, which keeps the form clean and makes the lists easy to edit. Data Validation accepts references to another sheet directly (=Lists!$D$2#), and named ranges work from any sheet in the workbook. You can hide the Lists sheet once it works (right-click its tab > Hide). The guide to a drop-down list from another sheet covers the details, and the worksheets and cross-sheet references lesson practises sheet references.

Why isn't my dependent drop-down working?#

Most problems come from one of these:

1. #SPILL! in the helper cell. Something is in the way of the FILTER or UNIQUE results. Clear the cells below.
2. #CALC! from FILTER. No item matches, usually because the first cell is empty. Add a third argument for that case, such as =FILTER(B2:B8,A2:A8=Form!B2,"Pick a category").
3. Missing #. =Lists!$E$2 without # gives a one-item list.
4. The source "evaluates to an error". INDIRECT can't find a matching name. Check spelling and spaces.
5. Spill references in older versions. The # and FILTER need Microsoft 365 or Excel 2021. Use Method 2 in older versions.

Practise it#

Try the exercise on this page: it is the helper formula from Method 1, and you can check that Veg returns Carrot, Kale and Leek. Then practise FILTER in a live spreadsheet and the UNIQUE lesson, where you type the formulas yourself and get instant feedback. The article on the FILTER function and the UNIQUE function guide go deeper into both.