Excel Dependent Drop-Down Lists in Microsoft 365

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365, you can make a dependent drop-down list by using Data Validation with a named range, or by using a dynamic-array formula to create the choices for a second list. The dynamic-array method is flexible for a single selection area; named ranges with INDIRECT are a familiar option when each category has its own fixed list.

What a dependent drop-down list does

A dependent drop-down list changes its available choices according to a selection in another cell. For example, a user might select a department in cell A2, then choose a role in B2. If A2 contains Sales, B2 can offer Account Manager and Sales Representative; if A2 contains Finance, B2 can offer Accountant and Analyst.

Excel creates these lists with Data Validation, a feature that limits what can be entered in a cell. The dependency is supplied by a formula or a named range: Data Validation itself does not create or organize the source data. Keeping the source values in a tidy table makes lists easier to update and reduces errors such as spelling the same category two different ways.

Prepare the source data

For a dynamic method, put the choices in a two-column Excel table. On a sheet named Lists, make columns called Category and Item, and enter one valid category-item pairing on each row. For example:

  • Fruit — Apple
  • Fruit — Pear
  • Vegetable — Carrot
  • Vegetable — Spinach
  • Drink — Tea
  • Drink — Water

Select the data and choose Insert > Table. Confirm that the table has headers, then use Table Design > Table Name to give it a concise name, such as Items. A table expands as you add rows, so formulas that refer to its columns can include new entries without changing a fixed range. Avoid blank rows and inconsistent category spelling in the source.

Create a dynamic dependent list

This approach uses a helper area to spill the choices into worksheet cells, then points Data Validation to that spill range through a defined name. It uses SORT, UNIQUE, and FILTER, which are available in Microsoft 365. The example is for one category selector in Entry!B2 and its dependent list in Entry!C2.

  1. On the Lists sheet, choose an unused cell such as C2 for the category list.
  2. In Entry!B2, the user will select a category. On Entry, use D2 as a helper cell for its matching items. Keep the cells below D2 clear so the formula can spill.
  3. Create a defined name for each spill range, then use those names as the Data Validation sources.

With the Items table containing Category and Item columns on Lists, enter this in Lists!C2 to produce a sorted, unique category list: =SORT(UNIQUE(Items[Category])). The result is a vertical spill range, such as Drink, Fruit, and Vegetable. On the Formulas tab, choose Define Name and name the range CategoryList. Set Refers to as =Lists!$C$2#. The # refers to the entire spill range.

With CategoryList defined as the spill range beginning at Lists!C2, select Entry!B2 and choose Data > Data Validation. In the Settings tab, set Allow to List and enter =CategoryList in Source. Leave In-cell dropdown selected if you want the arrow to appear in the cell. This sets up the first list.

With the category selection in Entry!B2 and the Items table on Lists, enter this formula in Entry!D2: =SORT(UNIQUE(FILTER(Items[Item],Items[Category]=B2,""))). The formula returns the distinct items whose category matches B2, in alphabetical order. If B2 contains Fruit, the example returns Apple and Pear. The final empty-text argument means FILTER returns a blank rather than an error if no rows match.

With that result spilling from Entry!D2, create another defined name using Formulas > Define Name. Call it ItemChoices and set Refers to as =Entry!$D$2#. Select Entry!C2, open Data > Data Validation, choose List, and set Source to =ItemChoices. Now the choices in C2 update when the category in B2 changes. Keep the helper cells below D2 empty; any existing content that blocks a spill produces a #SPILL! error.

Use named ranges for fixed category lists

A traditional alternative is to make a separate range for each category, give each range a name, and use INDIRECT in the dependent cell’s validation source. For example, place the Fruit choices in a range named Fruit and the Vegetable choices in a range named Vegetable. Select the dependent cell, open Data > Data Validation, choose List, and use this source: =INDIRECT($A2). With the category in A2, Excel treats its text as the name of the corresponding range.

This method is straightforward when categories and their lists are stable, and it can support many entry rows because the row reference can adjust. However, Excel range names cannot contain spaces, and category text must match the range name. One way to handle labels such as Home Office is to use a name such as Home_Office and a helper value that converts the displayed category to that name. INDIRECT also depends on the named ranges continuing to exist, so changing category labels may require maintenance.

Choosing a method and supporting more rows

Use the dynamic formula method when the source list changes regularly, you want distinct sorted choices, and you can give each dependent selector its own helper spill range. Use separate named ranges when the categories are fixed, the lists are short, or the workbook already uses that structure. Neither method automatically changes a dependent cell’s existing value when its parent category changes. If a user changes Fruit to Vegetable, a previously selected Apple may remain in the dependent cell even though it is no longer a valid choice; clear or replace that entry.

The one-helper example above is designed for one dependent selection row. For several independent rows, each row needs a helper formula tied to its own category cell and a validation source that points to that row’s results. A spill range cannot overlap another helper range, so reserve enough space or place helpers on a separate sheet. For a larger data-entry form, a carefully designed named-range approach may be easier to copy down. Test the validation on several rows before distributing the workbook.

If there are just a few choices and they rarely change, a typed list in the Data Validation Source box may be sufficient for the first dropdown. But a comma-separated list is not a practical way to maintain a long, changing set of dependent options. A table-based source keeps the data separate from the form and is easier to audit and extend.

Common problems and fixes

  • The arrow is missing: Check that Allow is set to List and In-cell dropdown is selected in the validation settings. Selecting the cell may be necessary to see its arrow.
  • The second list is empty: Confirm the selected category exactly matches the Category values in the Items table, and check that the helper formula is not returning an error.
  • You see #SPILL!: Clear cells in the intended spill area. Merged cells or other content in the way can also prevent results from spilling.
  • The defined name is not recognized: Check its spelling and its Refers to setting in Formulas > Name Manager. The validation source should refer to the name, such as =ItemChoices, not to a cell containing a formula as plain text.
  • A stale item remains after changing the category: Data Validation restricts future entries; it does not automatically erase an existing selection. Clear the dependent cell or add a separate process to check it.

For an error alert, open Data > Data Validation and use the Error Alert tab. A Stop-style alert can prevent users from entering values outside the list through normal cell entry, while an input message can explain what to select. Validation is a data-entry aid, not a substitute for reviewing imported or pasted data; pasting can bypass expected prompts or introduce values that need checking.

Excel for the web and Mac

The steps above describe Excel for Microsoft 365 on Windows. Excel for the web supports data validation lists, but creating and managing defined names or more involved workbook setups may be easier in the desktop application. If a workbook’s dependent list does not behave as expected in a browser, open it in desktop Excel and check the names, formulas, and validation settings there.

Excel for Microsoft 365 on Mac has the same core ideas and supports dynamic-array formulas, but ribbon layout and dialog details can differ from Windows. Look for Data Validation on the Data tab and for defined names in the Formulas tools. Keyboard shortcuts can differ on Mac, so the ribbon commands are a reliable way to follow these steps across platforms.

Frequently Asked Questions

Can I make a dependent drop-down list without INDIRECT?
Yes. In Microsoft 365, a helper formula using FILTER can return the choices for the selected category. A defined name referring to that formula’s spill range can then serve as the second list’s validation source.

Do I need a separate worksheet for the lists?
No. The source data and helper formulas can be on the same sheet as the form. A separate Lists sheet is often neater and makes it less likely that users will overwrite source values.

Will the second drop-down clear itself if I change the first choice?
No. The list of permitted new choices updates, but Excel does not automatically remove an existing value in the dependent cell. Clear or reselect that value after changing the parent choice.

Can I use spaces in category names?
Yes, the displayed category can contain spaces. For the named-range-and-INDIRECT method, the corresponding range name cannot contain spaces, so you need matching helper values or a naming convention such as replacing spaces with underscores.

Why does my list not include a new source row?
Check that the new row was added inside the Excel table and that its category is spelled consistently. Table-based formulas generally expand with the table; a manually specified fixed cell range may not.

Keep the workbook maintainable

For a reliable workbook, keep one clean source table, use clear category labels, and test both valid and invalid selections. Protecting the source sheet from accidental edits can help when many people use the form, though protection should not be treated as a security boundary. For a small one-category form, the dynamic helper method is often convenient; for a fixed set of lists or many entry rows, named ranges may be simpler to manage. Choose the structure that users can maintain correctly, and document where new categories and items should be added.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.