Excel Dropdown List From a Table Column That Updates

Yes. In Excel for Microsoft 365 on Windows, turn the source data into a table, define a workbook name that refers to the table column, and use that name as the Data Validation list source. The dropdown can then include entries added to the table later, without editing the validation rule each time.

Why use a table column for a dropdown?

A dropdown list made from a fixed cell range can become outdated when you add choices beyond the range you originally selected. An Excel table is designed to expand as data is added, and a structured reference to one of its columns follows that column as its size changes. Combining the table with a defined name gives Data Validation a source it can use reliably.

This is useful for lists such as departments, request types, product names, locations, or project statuses. For example, a workbook might have a table of approved expense categories and a separate entry sheet where staff choose a category from a dropdown. If a new category is added to the table, the list can reflect it as the table grows.

One limitation matters: the table reference makes the list expandable, but it does not automatically remove duplicates, sort the choices, or exclude blank cells. Those are separate list-cleanup decisions.

Set up the source table

Put the choices in one column, with a clear heading such as Category. Avoid blank rows within the choices.
Select the choices and their heading, then choose Insert > Table. Confirm that My table has headers is selected if the first row contains the heading.
Click inside the table and open the Table Design tab. In the Table Name box, give it a short name such as Categories. Table names cannot contain spaces, so use an underscore if needed.
Check that the heading is the one you intend to use and that the table contains only the source values you want offered in the dropdown.

If the data is already an Excel table, you do not need to create another one. Click inside it and check its table name and column heading on the Table Design tab. In Excel for Mac, the table tools and ribbon labels can differ slightly by build, but the same table and defined-name approach applies. Excel for the web can work with tables too; if a particular name-management command is unavailable in your web interface, create the name in desktop Excel.

Create a name for the table column

Data Validation’s List source box generally does not accept a structured table reference directly. A defined name acts as a bridge: it points to the table column, and the validation rule refers to the name.

With the choices in the Category column of a table named Categories, open Formulas > Name Manager, select New, and enter a name such as CategoryList. Use a short name without spaces. Set Scope to Workbook so the name can be used from other worksheets. In Refers to, enter the table-column reference.

With the choices in the Category column of the table named Categories, the defined name’s Refers to field should be =Categories[Category]. Replace both the table name and the column heading with the exact names in your workbook. The structured reference points to the table’s data cells, not its header.

Choose OK, then close Name Manager. If you rename the table or its column later, Excel normally updates structured references, but it is sensible to check the name in Name Manager if the dropdown stops working.

Apply the dropdown with Data Validation

Select the cell or range of cells where users should make a choice. For example, select B2:B100 on an entry sheet.
Choose Data > Data Validation. In the Settings tab, set Allow to List.
In Source, enter the defined name with a leading equals sign. If the name is CategoryList, use =CategoryList.
Leave In-cell dropdown selected. Optionally configure the Input Message and Error Alert tabs to explain what users should enter and whether invalid values should be blocked.
Select OK, then click a validated cell and open its dropdown arrow to check the choices.

The source formula above assumes you created the workbook-level name CategoryList to refer to the intended table column. It is the name—not the table’s visible heading—that goes in the Source box. To apply the rule to a whole input column, select the appropriate cells before opening Data Validation. Avoid selecting the header or unrelated cells.

What happens when the table changes?

When you add a choice as a new row in the table, the table grows and the column reference used by the defined name includes the new data row. The validation rule can continue to use the same name; there is no need to rewrite its source for each addition. Excel commonly expands a table when you type directly in the row beneath it or paste data immediately below it. If new data is entered somewhere else, it may remain outside the table until you extend or resize the table.

To verify that an item is included, click in the source table and look for the table formatting and Table Design tab. If the new value is outside the table boundary, add it inside the table or use Table Design > Resize Table to include the row. Then check the validated cell again.

Changing a source value also changes the dropdown choice because the list reflects the current contents of the source column. However, changing the source list does not automatically revise values already selected in other cells. If an old choice is removed from the table, existing cells can still contain that text; review or validate those entries separately if the change matters.

Blanks, duplicates, and order

A table-based dropdown reflects its source column; it is not a data-cleaning tool. Repeated entries generally appear more than once, and blank cells within the table can create empty choices. A long list may also be harder to use if it is not arranged in a helpful order.

Remove duplicates: If each choice should appear once, clean the source column or build a separate, deduplicated helper list. Do not assume Data Validation will collapse repeated values.
Avoid blank rows: Keep the source entries together without empty rows. A table can contain blank data cells, and those may be reflected as blank options.
Sort intentionally: Sort the table column if alphabetical order is useful. Be mindful that sorting a table can move entire records if the table has other columns.
Use clear labels: Consistent spelling and capitalization make the choices easier to scan and reduce reporting problems caused by near-duplicate categories.

If the table contains other fields that must stay aligned with each choice, sort the entire table rather than sorting just one column. That preserves the relationship between each row’s values.

Common problems and fixes

The Source box rejects the table reference: Use the defined-name method instead of typing a structured reference such as a table-and-column reference directly into Data Validation.
The dropdown arrow is missing: Reopen Data > Data Validation and confirm Allow is List and In-cell dropdown is selected. Also check that you selected a cell with the rule, not a neighboring cell.
New choices do not appear: Confirm that the new rows are inside the table, check the exact spelling of the table and column names in Name Manager, and confirm the validation source uses the correct defined name.
The name works on one sheet but not another: Check Scope in Name Manager. A name scoped to one worksheet may not be available as expected elsewhere; a workbook-level name is the straightforward choice for a shared list.
The list contains blank options or repeated choices: Inspect the source column. The name follows the column but does not filter empty cells or remove duplicates.
A pasted value is not on the list: Review the Data Validation Error Alert settings. If the alert is disabled, users may be able to enter values that were not offered in the dropdown.

Table-based lists compared with fixed ranges

A fixed range is simple for a list that will not change. For instance, a source of A2:A8 remains adequate if the choices are permanent and no future additions are expected. Its weakness is maintenance: a new item in A9 is not included unless the validation source is extended.

A table-based source is more convenient when the list changes over time, especially when several sheets use the same choices. It takes a little more setup because of the defined name, but the validation rule remains stable while the table grows. A named fixed range can also make a Data Validation source easier to manage, but it will not expand with new data unless it is defined in a way that does so.

For a list that must show only choices matching another cell—for example, cities filtered by a selected country—a single table-column name is not enough. That is a dependent or cascading dropdown. It needs additional logic, often a helper list and newer dynamic-array functions, and is a different setup from a simple list based on one table column.

Frequently Asked Questions

Can I put a table column directly in the Data Validation Source box?
Usually, no. Data Validation’s List source field does not reliably accept a structured table reference directly. Define a workbook name that refers to the column, then use that name in Source.

Does the dropdown update when I add a row to the table?
Yes, when the new choice is included in the table. The defined name follows the table column as it expands, so the validation rule can keep using the same name. A row outside the table is not part of the list.

Can the dropdown show unique values only?
Not automatically from the table column reference. The basic setup reflects duplicates in the source. Use a cleaned source list or a separate helper list if each option must appear only once.

Can I use the dropdown on another worksheet?
Yes. Create the defined name with Workbook scope, then use that name as the Source for validation on the other sheet. A worksheet-scoped name may be limited to its own sheet.

Will removing a choice erase it from cells where it was already selected?
No. Removing a source entry changes the dropdown choices, but it does not clear existing cell contents. Search or review previously completed cells if you need to update old selections.

Can I use the same method in Excel for Mac or the web?
The table-and-name principle is the same, although ribbon layouts and name-management controls can vary by version. The steps here use the Microsoft 365 Windows ribbon; if you cannot create or edit the name in Excel for the web, open the workbook in desktop Excel and set it up there.

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.