Excel Data Validation Dynamic List: Setup and Tips

To make an Excel data-validation list update when its options change, put the options in an Excel Table, create a workbook name that returns the table’s nonblank values, and use that name as the validation source. In Microsoft 365 for Windows, the core setup is Data > Data Validation, choose List, and enter =ListItems in Source after defining the name.

What a dynamic validation list does

A data-validation list adds a dropdown to a cell so a user can select from permitted entries instead of typing a value freely. A dynamic list adjusts as its source options change. For example, if a dropdown contains department names and a new department is added to the source table, the new name can become available without manually editing the validation rule.

“Dynamic” can mean more than one thing. The source range might expand when you append an item, or the list might also sort, remove duplicates, and omit blank cells. A Table-based source handles expansion particularly well; a dynamic-array formula can add the other behaviors. The choice depends on how clean and controlled the source data needs to be.

Validation is a useful input aid, not a complete security or data-integrity system. Users can paste values into validated cells, and other operations may bypass the dropdown. For important workflows, check the resulting data as well as providing a list.

Build a source table

Start by placing the permitted choices in one column, with a header. For example, enter the heading Choice in A1 and options such as Open, In progress, and Complete in A2:A4. Select the range and choose Insert > Table, or press Ctrl+T on Windows. Confirm that the table has headers. On Mac, Ctrl+T is commonly the shortcut as well; the Insert tab also provides the Table command.

On the Table Design tab, set the table name to tblChoices. Table names cannot contain spaces. The table is valuable because adding a new value in the row immediately below it normally expands the table, and the structured reference tblChoices[Choice] continues to refer to that column. Keep the header spelling consistent with the formula you will use.

Use one option per row rather than putting several choices in one cell.
Avoid blank rows within the source list, especially if other formulas depend on its contents.
Use consistent spelling and spacing. “In progress” and “In progress ” are different text values.
Keep the source table on a dedicated worksheet if that makes the workbook easier to maintain.

Create a dynamic named formula

In Microsoft 365, a formula can filter out blank cells, remove duplicate entries, and sort the remaining choices. With the table named tblChoices and its column named Choice, the formula is:

With the source options in the table column tblChoices[Choice], use this formula as the name’s Refers to value:

=SORT(UNIQUE(FILTER(tblChoices[Choice],tblChoices[Choice]<>"")))

To create the name in Excel for Microsoft 365 on Windows, open Formulas > Name Manager > New. Enter ListItems as the name, leave the scope as Workbook, and put the formula in Refers to. Select OK, then close Name Manager. Workbook scope lets validation rules on different worksheets use the same name. Excel for Mac also has Name Manager under the Formulas tab, though the dialog layout can differ.

FILTER keeps nonblank entries, UNIQUE removes repeated entries, and SORT orders the result. These functions are available in Microsoft 365 and Excel 2021 or later. If every source cell is blank, the formula can return a calculation error; keep at least one option in the table or adjust the formula to handle an empty source before relying on that state.

Connect the name to the dropdown

Select the cell or range that should have the dropdown. Choose Data > Data Validation. In the Settings tab, set Allow to List, make sure In-cell dropdown is selected, and enter =ListItems in Source. Select OK. The dropdown arrow appears when a validated cell is selected. You can apply the rule to a range such as B2:B100 by selecting that range before opening Data Validation.

Try the result by adding a new choice in the first row directly below the source table. The table should expand, and the named formula should recalculate. Open a validated cell’s dropdown and confirm the new option is available. Because the formula sorts its output, the entry appears in its alphabetical position rather than necessarily at the bottom.

Excel’s Data Validation dialog does not always accept a dynamic-array expression typed directly into Source in the same way it accepts a fixed range. The defined-name method gives the rule a simple source reference and is a dependable way to use the calculated list. A worksheet formula can also spill results into cells, but keep that output in a clear area and test the named-reference approach in the Excel version used by everyone opening the workbook.

Choose the right kind of dynamic source

The sorted, unique formula is useful when the source may contain duplicates or blanks. If the source is already curated and the desired order matters, sorting and deduplication can be undesirable. For example, a priority list may intentionally run High, Medium, Low, rather than alphabetically. In that case, preserve the source order instead of wrapping the values in SORT.

A simple formula that filters blank entries but keeps the original order is:

With the same source table column tblChoices[Choice], use this alternative in the name’s Refers to box:

=FILTER(tblChoices[Choice],tblChoices[Choice]<>"")

Use one formula or the other for the name; do not create two names with the same name. If duplicates are meaningful, omit UNIQUE. If the list is short, fixed, and unlikely to change, a direct source range such as =$A$2:$A$10 may be simpler and easier for another workbook editor to understand. It is not dynamic when entries are added beyond row 10.

Use a spill range as an alternative

You can place a dynamic-array formula in a worksheet cell and let its results spill into adjacent cells. For example, put the formula in H2 and use the spill range as a source through a defined name. With options in tblChoices[Choice], enter this in H2:

=SORT(UNIQUE(FILTER(tblChoices[Choice],tblChoices[Choice]<>"")))

The results occupy H2 downward as needed. A name referring to =$H$2# can then be used as the Data Validation source by entering that name with a leading equals sign. The # spill-range operator means the whole current spill range, not just H2. This approach makes the generated choices visible on a worksheet, which can help with inspection, but it uses worksheet space and can fail if something blocks the spill area. The named-formula approach avoids needing a visible output column.

Manage edits, blanks, and errors

When someone changes an option inside the source table, the dropdown’s available entries update after Excel recalculates the formula. If a new item is typed immediately below the table but does not become part of it, check that it was entered in the adjoining row and that the table expanded. A gap or a separate object below the table can prevent the expected expansion.

Blank options are a frequent nuisance. The FILTER condition excludes empty strings, including cells whose formulas return an empty string. It does not clean up every unwanted value: a cell containing spaces is not empty, and spelling differences remain separate choices. Clean the source entries if the dropdown contains near-duplicates or apparently blank items.

A validation rule controls what Excel prompts a user to enter; it does not erase values already in the target cells when the source list changes. If a choice is removed from the source, existing cells can retain that now-unlisted value. To identify such cases, use a separate check, such as filtering the data or applying conditional formatting, rather than assuming the validation rule retroactively repairs entries.

To inspect or change a rule, select a cell that uses it and open Data > Data Validation. If Excel reports that the source currently evaluates to an error, inspect the defined name in Formulas > Name Manager, verify the table and column names, and check whether the formula returns a valid list. Also check whether the workbook is opened in a version that supports the functions used.

Compatibility and sharing considerations

FILTER, UNIQUE, and SORT are available in Microsoft 365 and Excel 2021 or later. A workbook using these functions may show compatibility problems or formula errors in older Excel releases that do not support dynamic arrays. If colleagues use older versions, choose a method that those versions can calculate, maintain a fixed list, or agree on a supported Excel version before distributing the workbook.

Excel for the web supports many Microsoft 365 formulas and data-validation features, but the exact editing controls can differ from desktop Excel. Mac users can create tables, names, and validation lists, although ribbon placement and dialog details may differ. If a workbook is shared across desktop, Mac, and web, test the dropdown and the process for maintaining its source in the actual environments used by its editors.

There is no separate charge for using data validation or these formulas in an Excel edition that includes them. Availability depends on the Excel license and version. A recipient may be able to select an existing dropdown entry even if their version cannot recalculate the dynamic formula, but do not assume the list will update correctly in unsupported software.

Common misconceptions and practical limits

A dropdown does not guarantee that every stored value came from the list. Pasting data can bypass validation prompts, and a user can replace or remove the rule. Validation also does not automatically create a dependent dropdown—for example, a second list whose entries depend on the first selection. Dependent lists require additional formulas and setup, and the appropriate design depends on the relationship between the categories.

Nor does a dynamic list automatically update every existing selection when an option is renamed. It changes which choices are offered going forward; it does not rewrite historical entries. Keep stable identifiers or review old records if renaming options could affect reporting. For a shared workbook, protect the source sheet or limit editing access if users should be able to choose values but not change the permitted choices.

Frequently Asked Questions

How do I make an Excel dropdown list expand automatically?
Put the choices in an Excel Table, create a workbook-level name that refers to the table column or a formula based on it, then use that name in Data > Data Validation under List. Appending a choice to the table updates the source.

Can I use a table column directly in the Source box?
Structured references such as =tblChoices[Choice] are not accepted consistently as direct list sources. A defined name referring to the table column or a formula that returns its values is a practical workaround.

Can Excel remove duplicates from a validation dropdown?
Yes. In Microsoft 365 or Excel 2021 and later, use UNIQUE in a dynamic-array formula. Add SORT if you also want alphabetical order, and use FILTER to exclude blank entries.

Why does my dropdown still show an option I deleted?
The validation rule does not clear existing values from cells that already contain the deleted option. Check whether the source table still includes it, whether the formula has recalculated, and whether the cell contains an older selection that remains in place.

Can a cell allow multiple selections from the dropdown?
Standard Data Validation allows one selected value per cell. It does not provide a built-in multi-select setting. A different data-entry design or VBA customization is needed for multiple selections, and macros require care when workbooks are shared.

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.