Dynamic Dependent Dropdown List in Excel: How to Build One

How-to basics · Updated November 19, 2026

To create a dynamic dependent dropdown in Microsoft 365 Excel, store category and item pairs in an Excel table, use FILTER with UNIQUE to spill the matching items, and point Data Validation to that spill range through a named range. The second dropdown will then change when the user chooses a different category in the first.

What a dependent dropdown does

A dependent dropdown limits the choices in one cell according to a selection in another. For example, a person choosing “Fruit” in a Category cell might see Apple, Banana, and Orange in the next cell; choosing “Drinks” instead would show Tea and Coffee. This is useful in order forms, surveys, inventory sheets, and other workbooks where users should select from a controlled set of related values rather than type them freely.

“Dynamic” means the choices can respond to a changed parent selection and, with a well-designed source, include new records as the source data grows. A dependent dropdown is still a Data Validation list: the formulas prepare its options, while Data Validation displays them in the cell. It does not automatically erase an old selection when its parent changes, a distinction that matters when designing the sheet.

Prepare the source data

On a worksheet named Lists, create two columns with the headers Category and Subcategory. Enter one valid category-item pairing per row. For example, the rows might be Fruit–Apple, Fruit–Banana, Fruit–Orange, Drinks–Coffee, Drinks–Tea, and Snacks–Nuts. Repeating a category on several rows is expected: each row describes one permitted pairing.

Select the data and choose Insert > Table, or press Ctrl+T on Windows, then confirm that the table has headers. On Mac, use Command+T to create a table. In Table Design, set the table name to Items. The table is important because its structured references expand when you add rows, unlike a fixed range such as A2:A100. Keep the helper formulas outside the table so their results can spill into neighboring cells.

Create a worksheet called Entry. Use D2 for the category choice and D3 for the dependent subcategory choice. On Lists, reserve F2 for the unique category list and G2 for the matching subcategory list. Ensure the cells below these formulas are clear; otherwise Excel cannot display the full spill range.

Build the dynamic lists with formulas

With the source table named Items and its headers Category and Subcategory, enter this formula in Lists!F2. It returns a sorted list of distinct, nonblank categories; with the example data, the results are Drinks, Fruit, and Snacks.

=SORT(UNIQUE(FILTER(Items[Category],Items[Category]<>"")))

In Lists!G2, enter a formula that checks the selected category in Entry!D2 and returns its distinct matching subcategories. With D2 set to Fruit, it spills Apple, Banana, and Orange. If D2 is blank, the IF condition leaves the helper cell blank rather than showing unrelated choices.

=IF(Entry!$D$2="","",SORT(UNIQUE(FILTER(Items[Subcategory],Items[Category]=Entry!$D$2,""))))

FILTER selects rows that meet the category condition, UNIQUE removes duplicate subcategory entries, and SORT orders the resulting list. Because the source uses table columns, adding a new row to Items extends the data being searched. The spill operator, written as a # after a formula cell reference, refers to the entire variable-length result rather than just its first cell.

Connect the lists to Data Validation

Data Validation list sources are more reliable when a spill range is exposed through a defined name. In Excel for Microsoft 365 on Windows, open Formulas > Name Manager > New. Create the workbook-scoped name CategoryChoices. Assuming the category formula is in Lists!F2, set Refers to to the following. The name refers to every category in that spill range, not only F2.

=Lists!$F$2#

Create a second workbook-scoped name, SubcategoryChoices. Assuming the dependent formula is in Lists!G2, set Refers to as shown; the name expands or contracts with the selected category’s results.

=Lists!$G$2#

Select Entry!D2 and choose Data > Data Validation. Set Allow to List, enable In-cell dropdown, and enter =CategoryChoices in Source. Select Entry!D3, repeat the steps, and enter =SubcategoryChoices. With the example table, D2 offers Drinks, Fruit, and Snacks. When D2 contains Fruit, D3 offers Apple, Banana, and Orange. Optionally, use the Error Alert tab to display a Stop message if someone types an item that is not on the list.

Why the named spill range matters

A regular formula can spill a dynamic array onto a worksheet, but the Data Validation Source box has restrictions and may reject a direct reference to a list on another sheet. A workbook-level defined name gives the validation rule a simple, reusable source while the name itself points to the spill range. This also keeps the source lists and helper calculations away from the entry area.

The # reference is essential: a reference such as Lists!$G$2 points to one cell, while Lists!$G$2# means the whole current spill. If the selected category has three matching items, the spill reference covers those three; if another category has two, it covers two. Do not type the # reference as ordinary text in the cell. It belongs in the defined name’s Refers to setting.

Keeping the dropdown current

Add new category-item pairs as new rows inside the Items table. The table’s structured references include those rows automatically, and the formulas recalculate in normal automatic calculation mode. If a new category is added, it appears in the first dropdown; if a new item is added under an existing category, it appears in that category’s dependent list.

For best results, keep category labels consistent. “Drinks” and “Drink” are different text values, so they produce different lists. Avoid blank rows in the middle of the table and check for leading or trailing spaces if an expected match is missing. Excel treats text with extra spaces as a different value. If the workbook is set to manual calculation, recalculate it after changing the source; otherwise, verify that Formulas > Calculation Options is set to Automatic.

Common problems and practical fixes

  • The validation source is rejected: Check that both names exist in Name Manager, have workbook scope, and refer to the intended spill cells. In the validation Source box, use the name with a leading equals sign, not a worksheet address typed as plain text.
  • The spill formula shows #SPILL!: Clear cells below and beside the helper formula that are blocking its output. Move notes or other content away from the expected spill area.
  • The second dropdown is empty: Confirm that the parent cell exactly matches a value in the Category column and that the corresponding Subcategory cells are populated. Check spelling and spaces as well as the table’s column headers.
  • An old child choice remains after changing the category: Data Validation controls future entries; it does not automatically remove a value already in D3. Clear D3 manually after changing D2, or add a separate workflow to manage that behavior. A formula cannot both serve as a normal user-entered dropdown value and automatically clear that cell.
  • Options appear more than once: The UNIQUE function in the helper formula removes duplicate subcategory values for the selected category. If duplicates remain, check whether apparently identical labels contain different spaces or characters.

For a workbook used by others, consider adding a short instruction such as “Choose a category, then clear and reselect the subcategory if needed.” If the sheet must enforce that the child selection always agrees with the parent, add a validation or review step; a dropdown alone is not a complete data-integrity system.

Modern formulas versus the older INDIRECT method

Older dependent-dropdown instructions often create a separate named range for every category, then use INDIRECT to select the matching range. That approach can work for a small, stable list, but it requires maintaining many names and ensuring category labels map safely to valid range names. It is less convenient when categories are frequently added or renamed.

For example, if Entry!D2 contains “Hot Drinks” and a named range called Hot_Drinks contains Tea and Coffee, this Data Validation source formula converts the space to an underscore and refers to that named range. In that layout, the dropdown returns Tea and Coffee.

=INDIRECT(SUBSTITUTE(D2," ","_"))

The modern FILTER, UNIQUE, and SORT method keeps the relationship in one two-column table and creates the relevant list with a formula. It is generally easier to expand and audit, but it needs a version of Excel that supports dynamic arrays. Neither approach clears a previously selected child value when the parent changes.

Version and platform considerations

FILTER, SORT, and UNIQUE are available in Microsoft 365 and Excel 2021 or later. The instructions above target Microsoft 365 Excel for Windows. Excel for Mac has these functions in supported Microsoft 365 and Excel 2021-or-later releases, and the core Data Validation workflow is also available there; ribbon placement and dialog presentation can vary by release. The Windows shortcuts given above have Mac equivalents where noted.

Excel for the web can calculate many dynamic-array formulas, but when a workbook depends on spill-based defined names in Data Validation, check the behavior in the version and environment where people will edit it. If recipients use older Excel versions, consider the named-range/INDIRECT design or provide a non-dropdown entry process, and test the workbook in the oldest version that must be supported.

Frequently Asked Questions

Can I make a dependent dropdown without INDIRECT?

Yes. In Microsoft 365 or Excel 2021 and later, use FILTER to return matching values, optionally combine it with UNIQUE and SORT, and expose the spill range through a defined name for Data Validation. This avoids creating a separate named range for every category.

Why does my dependent dropdown not clear when I change the first dropdown?

Data Validation updates the permitted choices but does not erase a value already in the dependent cell. Clear the old value manually, or use a separate controlled process if the workbook must enforce that behavior.

Can the source list grow when I add new items?

Yes, if the source is an Excel table and the formulas refer to its structured columns. Add the new pair as a table row; the dynamic formulas can then include it without changing a fixed range address.

Do I need a separate worksheet for the helper formulas?

No. You can place the table, formulas, and entry cells on one worksheet, provided the spill areas are unobstructed. A separate Lists sheet is often tidier and makes it easier to keep source data and helper calculations out of the way.

What if the dependent list should allow only one matching item?

The same setup works. FILTER returns the matching value, and the spill reference can represent a one-item list. If there is no match, check the category text and source data; the example formula returns a blank result when it finds none.

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.