Excel Data Validation List from a Table: How to Set It Up

Yes. In Excel for Microsoft 365 on Windows, create a workbook-level name that refers to the table column, then use that name as the Data Validation list source. For example, if the table is named tblRequests and its column is Category, define CategoryList to refer to =tblRequests[Category], then enter =CategoryList as the list source. The dropdown can then include new table entries as the table grows.

Why use a table for a validation list?

A dropdown list helps keep entries consistent. If a team types a department as “Customer Support” in one row and “Cust. Support” in another, later filtering or summarizing those records becomes less reliable. A validation list offers approved choices and reduces accidental spelling differences.

A normal cell range can also supply a dropdown, but its address is fixed. If the source is A2:A10, adding a value in A11 will not necessarily make it part of the list. An Excel table expands when rows are added directly below it or entered as part of the table. A defined name that points to a table column follows that changing column, so the validation source does not need to be edited each time the list grows.

This is useful for shared workbooks, intake forms, task trackers, and other sheets where one column contains a controlled set of choices. The table also gives the source list a clear heading and makes it easier to maintain than an unexplained block of cells.

Set up a table-based dropdown in Excel

First, make sure the source values are in an Excel table and that the table and column have names you can identify. The example below uses a table named tblRequests with a column headed Category.

Select a cell in the source list and choose Insert > Table, or press Ctrl+T on Windows. Confirm that the table has headers. On Mac, use Command+T or the Insert tab’s Table command, depending on the ribbon layout.
With a table cell selected, open the Table Design tab and check the Table Name box. Give the table a simple name such as tblRequests. Table names cannot contain spaces. Confirm the column heading is Category, or note the exact heading you intend to use.
Choose Formulas > Name Manager > New. Enter CategoryList in the Name box. Leave Scope set to Workbook so the name can be used from other sheets. In Refers to, enter =tblRequests[Category], then choose OK and close Name Manager. This reference includes the table’s data entries, not its header.
Select the cells that should have the dropdown. Open Data > Data Validation. On the Settings tab, set Allow to List, leave In-cell dropdown checked, and enter =CategoryList in Source. Choose OK.

Now select one of the validated cells and open its arrow to check the available choices. If the arrow is not visible, select the cell; Excel generally shows the arrow only while a validated cell is active. Add a new value as a row in the source table and check the dropdown again. The added entry should be available without changing the name or validation rule.

How the table reference and name work

tblRequests[Category] is a structured reference: the table name identifies the table, and the square-bracketed heading identifies the column. Unlike a cell address such as $A$2:$A$20, it describes a table column by name. When the table adds or removes data rows, the column reference adjusts with it.

Excel’s Data Validation Source box does not accept every structured reference in the same way a worksheet formula does. In particular, typing =tblRequests[Category] directly into Source may produce an error. The defined name acts as a bridge: Name Manager stores the structured reference, and Data Validation uses the simpler name =CategoryList. The equals sign tells Excel that Source contains a reference rather than a literal comma-separated list.

Use a name that is easy to recognize and has no spaces. Names are not case-sensitive, but they must not look like a cell reference. If you already use CategoryList for something else, choose a distinct name, such as RequestCategories, and use that same name in the validation source.

Keep the source list clean and current

A table-based dropdown grows with its source, but it does not automatically clean the entries. If the source column has duplicate values, the dropdown can show duplicates. If it contains blank cells, users may see an empty choice. Remove duplicates and unwanted blanks from the source table if every dropdown option should be distinct and populated.

To add an option, enter it in the next row of the source table, not in an unrelated cell below it. Excel normally expands a table when data is entered immediately beneath it, but if the new value remains outside the table, the defined name will not include it. You can also add a row from within the table using its last cell and pressing Tab, or use the table’s insert-row controls.

Changing a source value changes the corresponding dropdown choice. Deleting a source row removes that choice from the list, but it does not rewrite older entries that people already selected in the destination cells. Those existing cells can therefore contain a value that is no longer offered. Review old records before removing an option if the history matters.

Make the dropdown unique or sorted

If you want one copy of each choice, keep the original table as the maintained source and create a cleaned list in a helper area. In Microsoft 365, the following formula can produce a sorted, duplicate-free list while ignoring empty cells. With tblRequests[Category] holding entries such as Travel, Hardware, and Travel, place this in cell G2 on a sheet named Lists:

=SORT(UNIQUE(FILTER(tblRequests[Category],LEN(tblRequests[Category])>0)))

For that example layout, the result spills down as Hardware, Travel: UNIQUE removes the repeated Travel entry, FILTER excludes zero-length cells, and SORT puts the remaining choices in alphabetical order. This formula requires Microsoft 365 or Excel 2021 for these functions. If the source column can contain cells with spaces that should count as blank, clean those entries too; a space is not the same as an empty cell.

To use the result as a dropdown source, create another workbook-level name, such as CategoryOptions, and set Refers to to =Lists!$G$2#. The spill operator # refers to the full output range beginning at G2. Then use =CategoryOptions in the Data Validation Source box. Keep the spill area clear so the formula has room to return all choices. This approach adds a helper formula and name, but it is useful when duplicate entries or sorting would otherwise make the dropdown confusing.

Common problems and fixes

Excel rejects the table reference in Source. Create the defined name in Name Manager and use =CategoryList in Source instead of entering the structured reference directly.
New values do not appear. Check that the new entry is actually inside the source table, and confirm the name still refers to the correct table and heading in Formulas > Name Manager.
The name is not recognized. Check the spelling in both Name Manager and the Source box. If the name has worksheet scope rather than workbook scope, it may not work from a different sheet; change the scope or create a suitable workbook-level name.
The dropdown contains blanks or repeats. Remove blanks and duplicates from the source, or use the helper formula approach for a distinct, sorted list.
The arrow seems to be missing. Select a cell with the validation rule. Also reopen Data Validation and check that In-cell dropdown is selected.
Some entries can still be typed into the cell. Data Validation is a guardrail, not an absolute security boundary. Check the Error Alert tab and use the Stop style if Excel should reject entries that do not match the list. Pasting data can also bypass or replace validation, so check imported or pasted records when accuracy is important.

If a cell still behaves unexpectedly, select it and open Data > Data Validation to inspect the rule. The rule may have been applied to only part of the intended range, or copied cells may have replaced it. Selecting the complete destination range before setting validation helps apply one consistent rule to all its cells.

Table-based lists compared with other methods

A table with a defined name is a good choice for a list that should grow as users maintain it. It is clearer and more adaptable than a fixed address, without requiring a separate formula for a simple list. It is especially convenient when the values belong to the workbook and are edited occasionally by its owner.

A typed list, such as entering options separated by commas in the Source box, is quick for a few stable choices. It is harder to maintain when the list changes and is unsuitable for larger or frequently edited sets. A fixed cell range is easy to understand but needs attention when the list grows. A dynamic helper formula can create unique or sorted choices, but it adds a formula and spill range to maintain.

For most users, start with a source table and a workbook-level defined name. Add the helper formula only if you need to remove duplicates, sort the options, or otherwise transform the source. Whichever method you choose, keep the source list in a place that workbook editors can find and understand.

Excel for Mac and Excel for the web

The table-reference and defined-name approach is intended for Microsoft Excel for Microsoft 365 on Windows. Excel for Mac has the same core concepts, though the ribbon layout and dialog presentation can vary; use the Data tab for validation and the Formulas tab for name management. Keyboard shortcuts also differ: Ctrl+T creates a table on Windows, while Command+T is commonly used on Mac.

Excel for the web can display and use many existing dropdown rules, but advanced setup and editing options may not match the desktop dialogs. If the web version does not let you create the required name or configure the rule as described, open the workbook in Excel for Windows or Mac to set it up. Avoid assuming that a dropdown appearing in a browser means every part of its source configuration is editable there.

Frequently Asked Questions

Can I use a table column directly in Data Validation?

Often the direct structured reference is rejected in the Source box. Define a name that refers to the table column, then use the name prefixed with an equals sign, such as =CategoryList.

Will the dropdown update when I add a table row?

Yes, if the new value is inside the source table and the defined name still points to that table column. A value typed outside the table will not automatically become part of the list.

Can the list avoid showing duplicate values?

Not by using the table column alone: repeated source entries can appear more than once. In Microsoft 365 or Excel 2021, create a helper list with UNIQUE, optionally combined with FILTER and SORT, then name its spill range.

Can the dropdown source be on another worksheet?

Yes. A workbook-level name can refer to a table column on another sheet and be used as the validation source elsewhere. Workbook scope is generally the simplest choice when the list is shared across sheets.

Why can someone enter a value that is not in the list?

The Error Alert setting may be turned off or set to a warning rather than Stop, and pasting can bypass or replace validation. Review the rule’s Error Alert settings and verify data after bulk pastes or imports.

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.