How to make a dependent drop-down list in Excel

Set up the lists as one two-column table

The example is an expense claim. Column D asks for a category (Travel, Meals, Office supplies), and column E should only offer the items that belong to it: someone who picked Meals should see Client lunch and Team dinner, not Flight.

Keep the source as one long list with a Category column and an Item column, as in A1:B10. It is easier to maintain than one column per category: to add Parking under Travel you type one new row, in any position. In a real workbook this list usually lives on its own sheet, often called Lists.

The first drop-down needs the categories once each. In Excel 365 and 2021, put a helper formula in an empty cell, say H2. It spills Meals, Office supplies and Travel down three cells and updates when you add a category.

Excel 365: a FILTER helper for the second list

In I2, filter the items whose category matches the choice in D2. Travel has four items, so the formula spills four cells. Change D2 to Meals and the helper shrinks to two.

While D2 is still empty, FILTER finds no match and shows #CALC! (the same happens for a category with no items, such as Parking before you add any rows for it). To keep the helper blank until a category is chosen, wrap it: =IF(D2="","",SORT(FILTER(B2:B10,A2:A10=D2))).

A Data Validation source has to be a range, so you cannot type the FILTER formula into the Source box itself. Point to the helper instead, and add # after the cell to mean "the whole spill, however long it is".

1. Select the category cells D2:D4, choose Data > Data Validation, set Allow to List and type =$H$2# in Source. Click OK.
2. Select E2, open Data > Data Validation again, set Allow to List and type =$I$2# in Source.
3. Pick Travel in D2 and open the arrow in E2: it shows only Flight, Hotel, Taxi and Train.

More than one row: one helper per row

The helper in I2 only follows D2. A claim form with several rows needs a helper for each row, because row 3 might be Meals while row 2 is Travel. Lay the helpers out sideways, one row each, with TRANSPOSE, so each one spills across instead of into the next row's helper.

Put the formula, pointing at D2, in I2 and fill it down to I4. Lock the two list ranges with $ so they stay on the list while D2 becomes D3 and D4. Then select E2:E4 and set the Data Validation source to =$I2#, with the column locked and the row left relative. Excel adjusts it for each row, so E3 uses the spill in I3 and E4 the spill in I4.

If the form has hundreds of rows, this means hundreds of helper rows. That still works, but the INDIRECT method below needs no helpers at all and is often simpler for a long list.

Any version: named ranges and INDIRECT

The classic method works in every version of Excel. Give each category its own column of items with the category name as the header, sorted so nothing is mixed: a Travel column, a Meals column and so on. Select the block and choose Formulas > Create from Selection, tick Top row only, and click OK. Excel creates one named range per header.

Then set the item cell's validation to List with the source =INDIRECT(D2). INDIRECT turns the text "Travel" into the range named Travel, so the second list follows the first. Because D2 is relative, applying it to E2:E50 makes each row look at its own category.

Names cannot contain spaces, so Create from Selection turns "Office supplies" into Office_supplies, and =INDIRECT(D2) can no longer find it. Use =INDIRECT(SUBSTITUTE(D2," ","_")) as the source instead. Names also cannot look like a cell address, so a category called Q1 or ABC1 will not work as a name. The named ranges also do not grow by themselves: a new item typed under a column is left out until you edit the name in Formulas > Name Manager, unless the lists are Excel Tables.

Catch a second choice that no longer fits

Data Validation only checks a cell when someone types or picks a value in it. If you choose Travel and Taxi, then change the category to Meals, Taxi stays in E3 even though it is no longer in the list. That is the most common complaint about dependent drop-downs, and it is how row 3 in the sample ended up as Meals / Taxi.

Add a check column that tests whether the pair exists in the source list. COUNTIFS counts rows with both the category and the item; 0 means they do not belong together. You can also use Data > Data Validation > Circle Invalid Data to draw red circles around every cell that breaks its rule. Clearing the item cell automatically when the category changes needs a macro.

Blanks and spaces in the source list

People often point the category helper at a long range so it has room to grow, such as A2:A30. UNIQUE then returns the empty cells as one extra entry, which shows up as 0 at the end of the first drop-down. Filter out blanks before taking the unique values.

Trailing spaces cause the other surprise: "Travel " with a space is a different category from "Travel", so it appears twice in the first list and each one shows only part of the items. Clean the source with TRIM or Find and Replace. The same applies to INDIRECT, where "Travel " does not match the name Travel at all.