How to Create a Dependent Drop-Down List in Excel

The idea in one minute

A dependent drop-down is a pair of lists: the first cell chooses a group, and the second list changes to match. We’ll use Department in OrderForm!B2 and Item in OrderForm!C2. A helper formula filters the choices in the background; a workbook name lets Data Validation use that changing list.

If you’ve used a drop-down to control a chart, the same “selection drives the result” idea is at work here. See how a drop-down can drive a dynamic Excel chart.

1. Put each choice on its own row

On a sheet named Lists, create two columns named Department and Item. Use one row for every valid pair:

1. Select a cell in the list and press Ctrl + T on Windows or ⌘ + T on Mac.
2. Confirm My table has headers, then choose OK.
3. On Table Design, change the table name to tblOptions.

2. Build the Department drop-down

On Lists, enter this in cell D2. It creates a sorted list with each department shown once:

=SORT(UNIQUE(tblOptions[Department]))

Next, give the spilled results a workbook name:

1. Choose Formulas → Name Manager → New.
2. Name it DepartmentChoices. In Refers to, enter =Lists!$D$2#, then save.
3. Select OrderForm!B2 and choose Data → Data Validation.
4. Set Allow to List, set Source to =DepartmentChoices, and choose OK.

Click B2. You should be able to choose Finance or HR. The # after D2 means “use the full spilled list,” even if it grows.

3. Filter the Item choices

On Lists, enter this formula in F2. It reads the selection in B2, returns only matching items, removes duplicates, and sorts the result:

=IF(OrderForm!$B$2="","",SORT(UNIQUE(FILTER(tblOptions[Item],tblOptions[Department]=OrderForm!$B$2,""))))

When B2 is Finance, F2 spills Budget Template, Invoice Log, and Monthly Report. Choosing HR changes the spill to its two items.

To connect the filtered list to the second drop-down, create another workbook name: go to Formulas → Name Manager → New, name it ItemChoices, and set Refers to to =Lists!$F$2#.

Select OrderForm!C2, open Data → Data Validation, set Allow to List, and set Source to =ItemChoices. Choose OK. Now C2 should offer the items for the department selected in B2. Using a workbook name also avoids entering a direct cross-sheet spill reference in the validation box.

4. Test it before using the form

1. Choose Finance in B2 and confirm C2 has three Finance items.
2. Change B2 to HR and confirm the choices in C2 update.
3. Add a new Finance row to tblOptions; check that it appears in the helper list and C2.
4. After changing B2, clear any old value in C2 before choosing again. Data Validation changes the next choices; it does not erase a value already entered.

If your Excel version doesn’t have FILTER and UNIQUE

For older Excel versions, create a named range for each department’s items, such as Finance and HR. Set C2’s Data Validation Source to this formula:

=INDIRECT(SUBSTITUTE($B$2," ","_"))

The names must match the department labels; replace spaces in a label with underscores in its range name. This fallback is handy for a small, stable list. The Table and FILTER method is easier to extend.

For related skills, see how to return a live list with FILTER and how UNIQUE removes repeated values. Microsoft’s drop-down list guide covers the Data Validation settings, and its spilled-range operator guide explains the # reference.

Frequently asked questions