AI Excel Generator
← ALL GUIDES

Excel: Create a Dynamic Dropdown List That Updates Automatically

Updated November 4, 2026 · AI Excel Generator

To create a dynamic dropdown list in Excel, use a source that grows as you add options, then connect that source to Data Validation.

The right method depends on your Excel version and whether the dropdown should include every entry or only unique, nonblank values.

What makes an Excel dropdown list dynamic?

A standard dropdown list often points to a fixed range such as $A$2:$A$10.

If you add an option in A11, it will not appear until you update the validation source.

A dynamic dropdown adjusts its source as the list changes, reducing maintenance and helping prevent inconsistent entries.

For example, suppose a worksheet named Lists contains product categories in column A, with a header in A1 and options below it.

On another sheet, you want cells in column C to offer those categories.

You can make the source expand automatically by storing the options in an Excel Table.

How do you build a dynamic dropdown with an Excel Table?

  1. Enter a header such as Category in Lists!A1, then enter options like Hardware, Software, and Services below it.
  2. Select the header and options, then choose Insert > Table.

    Confirm that the table has headers.
  3. With a table cell selected, open the Table Design tab and set the table name to CategoryList.
  4. Open Formulas > Name Manager > New.

    Name the range CategoryOptions and set Refers to to =CategoryList[Category].
  5. Select the cells that need the dropdown.

    Choose Data > Data Validation, select List under Allow, and enter =CategoryOptions in Source.

Now, when you type a new category directly below the table, Excel expands the table and includes the new value in the named range.

The dropdown uses that range.

Table names and column names must match the names in the formula exactly.

Can you use a dynamic array formula for the dropdown source?

In Microsoft 365 and Excel 2021 or later, dynamic array functions can create a cleaned, unique list.

Suppose the source entries are in Lists!A2:A100; this formula covers only that range, so increase the endpoint or use an expanding source if entries may be added below row 100.

In Lists!C2, enter:

=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

This formula filters out blank cells, removes duplicates, and sorts the remaining options.

For example, if A2:A5 contains Hardware, Software, Hardware, and a blank, the spilled result in C2:C4 is Hardware and Software (two values, in sorted order, occupying C2:C3).

To use the spilled list in Data Validation, create a workbook-level name such as SortedCategories that refers to =Lists!$C$2#.

The # spill operator refers to the entire array starting at C2.

Set the validation source to =SortedCategories.

Keep the spill area clear so Excel can display the results.

If another value blocks the spill, Excel returns a #SPILL! error and the dropdown source will not work as intended.

The FILTER, UNIQUE, and SORT functions are not available in older perpetual Excel versions such as Excel 2019.

What if your Excel version does not support dynamic arrays?

Use a Table and named range for a straightforward expanding list.

If you need a formula-based option and have a single-column list without blanks in the middle, a name can use INDEX and COUNTA to find its current endpoint.

For options in Lists!A2:A100, define a name called CategoryOptions with:

=Lists!$A$2:INDEX(Lists!$A$2:$A$100,COUNTA(Lists!$A$2:$A$100))

Then use =CategoryOptions as the Data Validation source.

This works when the entries form a continuous block beginning in A2.

A blank within the list can make COUNTA count fewer cells than the actual last entry, so it may omit later values.

It also has a fixed upper limit of row 100; increase that limit if the list may grow further.

Excel 2010 and later support INDEX for this pattern.

How can you troubleshoot a dynamic dropdown?

  • The source is rejected: Check that Data Validation refers to a defined name, such as =CategoryOptions, and that the name exists in the correct workbook scope.
  • New options do not appear: Confirm that entries were added within or directly below the Table, or that the formula’s range covers the new rows.
  • Blank choices appear: Remove empty table rows or use the FILTER approach to exclude blank cells.
  • Duplicates appear: A Table-based list includes duplicates; use UNIQUE in a supported Excel version if the dropdown should show each option once.
  • The list stops growing: Check for a fixed endpoint in the named-range formula and make sure the source column does not contain unexpected blank gaps.

For a small inventory tracker, place Item in Lists!A1 and values such as Notebook, Monitor, and Keyboard below it.

Convert the range into a Table named InventoryItems, define ItemOptions as =InventoryItems[Item], and set the input cells’ validation source to =ItemOptions.

Add Mouse beneath the last table row; the next dropdown should offer it along with the existing items.