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

Yes. In Excel for Microsoft 365 on Windows, turn a table column into a drop-down list by creating a workbook-level name that refers to the column, then use that name in Data Validation. Excel generally will not accept a structured table reference directly in the validation Source box, but the named range updates as the table grows.

Why use a table for a validation list?

A table gives the list a clear, expandable source. Instead of pointing validation at a fixed range such as A2:A10, you can refer to the whole table column. Add another category to the table and the named source includes it, without changing the validation rule on every cell. This is useful for frequently maintained lists such as departments, project statuses, product types, or staff names.

Tables also make source data easier to manage. They have a header row, can be filtered and sorted, and expand when data is added directly below them. A table is not necessary for a drop-down, but it helps prevent a common problem: a fixed source range failing to include new entries.

Set up the table and name its column

Put the available choices in one column, with a descriptive header such as Category. Avoid merged cells in the source list.
Select the header and choices, then choose Insert > Table. Confirm that My table has headers is selected, and choose OK.
With a cell in the table selected, open Table Design and note the Table Name shown on the ribbon. Give the table a simple name, such as tblCategories, if you prefer. Table names cannot contain spaces.
Choose Formulas > Name Manager > New. Enter a name such as CategoryList. Names cannot contain spaces, and a name should not look like a cell address.

Assuming the table is named tblCategories and its column header is Category, enter this in the Refers to box in Name Manager:

=tblCategories[Category]

Choose OK, then close Name Manager. This is a defined name referring to the table column, not a formula to enter in a worksheet cell. If your table or header has a different name, use the exact names shown in your workbook. The bracketed column name must match the header, including spaces and punctuation.

Apply the name as the drop-down source

Select the worksheet cells where people should make a choice. You can select a range such as B2:B100 before setting up the rule, so the same validation applies to all those cells. With that destination range selected, go to Data > Data Validation. In the Settings tab, set Allow to List, keep In-cell dropdown selected, and enter the following in Source:

=CategoryList

This source assumes the workbook-level name CategoryList has already been created to refer to tblCategories[Category]. Select OK. The selected cells should now show a drop-down arrow when active. Choose a value from the arrow to check that the rule works.

In the Data Validation dialog, the Input Message tab can show a short instruction when a validated cell is selected. The Error Alert tab controls what happens if someone types a value that is not on the list. For a strict list, keep the alert enabled and use the Stop style. A warning or information alert may allow an unlisted value after the user confirms, so it is less restrictive.

What happens when the table changes?

The named reference follows the table column, so a new choice added to that column becomes available in the drop-down. Add the value in the first row immediately below the table, or use the table’s last row and press Tab to create another table row. The table usually expands as you type directly beneath it; if it does not, add the row through the table’s Insert options or check that the entry is actually inside the table.

The validation rule on the destination cells does not automatically spread to every new worksheet row just because the source table expands. These are separate things: the source list can grow while the cells that use the list remain the same. Apply validation to a suitably large destination range, or extend the validation when adding destination rows. If the destination itself is an Excel table, Excel often carries cell formatting and validation into new rows, but check the new row rather than assuming the rule was copied.

Removing an item from the source table removes it from the choices shown going forward. It does not automatically erase that item from cells where a user selected it earlier. If historical entries must remain valid, consider keeping retired choices in the list or using a separate active-choice list.

Important details about blanks and duplicates

Excel displays duplicate entries as separate choices. If a list contains “Open” twice, the drop-down can show “Open” twice; data validation does not deduplicate the source. Keep the source list clean if clarity matters. You can remove duplicates from the source using Data > Remove Duplicates, but do that only if removing repeated values is appropriate for the data.

Blank cells in the table column can produce an empty-looking choice or affect how the list appears. Keep the source choices together without blank rows, especially at the end. The Ignore blank setting in Data Validation has a different purpose: it controls whether the destination cell is allowed to be empty; it does not clean blank entries out of the source list.

Also distinguish a list rule from a lookup or a formula result. A validation list limits or guides what can be entered in a cell; it does not automatically fill related information. For example, choosing a product code will not populate its price unless you add a separate lookup formula.

Common problems and fixes

The Source box rejects the table reference: Use the defined name, such as =CategoryList, rather than entering =tblCategories[Category] directly in the validation dialog.
The name is not recognized: Open Formulas > Name Manager and check the spelling, scope, and Refers to entry. A worksheet-scoped name may not be available from another sheet; a workbook-level name is the simplest choice for a list used in several places.
New choices do not appear: Confirm that the new value is in the table column, not beside the table, and that the name refers to the table column rather than a fixed cell range.
The arrow is missing: Reopen Data > Data Validation for the cell and make sure Allow is List and In-cell dropdown is checked. The arrow is normally visible when the validated cell is selected.
Users can still type other values: Check the Error Alert tab. A disabled alert, or an alert style other than Stop, may permit entries that are not in the list.
The name formula shows an error: Check the exact table name and column header. A renamed or deleted table column can break the reference.

When troubleshooting, test one destination cell first. This helps separate a problem with the named source from a problem with how validation was applied to a larger range. You can also use Formulas > Name Manager to select the name and inspect its reference.

Alternatives and platform notes

A workbook-level defined name is usually the clearest approach because it avoids placing a long structured reference in the validation dialog and can be reused on multiple sheets. A fixed cell range is simpler for a short list that will never change, but you must update the source when the list grows. A comma-separated list entered directly in the Source box can work for a very small, stable set of choices, but it is awkward to maintain and has a character limit; it is not a good substitute for a growing table.

In Excel for Microsoft 365 on Windows, the paths above use the standard ribbon. On Mac, the Data Validation command is also on the Data tab, while the exact arrangement of ribbon controls and the route for managing names can differ by Excel release. The same principle applies: make a defined name refer to the table column and use that name as the list source. In Excel for the web, you can use many existing validation lists, but name-management and validation-editing controls may be more limited or arranged differently than in desktop Excel. If you cannot create or repair the defined name in the browser, open the workbook in desktop Excel.

Sharing the workbook does not change the basic rule: the table and its defined name travel with the workbook. If another person adds source choices, ask them to add rows within the table rather than typing below it without expanding it. For shared workbooks, protecting the source sheet can prevent accidental edits, but protection settings should still allow the people maintaining the list to change its table.

Frequently Asked Questions

Can I use a table reference directly in Data Validation?
Usually, Excel’s List Source box does not accept a structured reference such as =tblCategories[Category] directly. Define a name that refers to the table column, then enter the name with a leading equals sign in Source.

Will the drop-down update when I add a table row?
Yes, when the defined name refers to the table column and the new choice is added as part of that table. The destination cells still need to have the validation rule applied.

Can I use the same table list on another worksheet?
Yes. Create the name with workbook scope in Name Manager, then use =CategoryList as the source on the other sheet. A worksheet-scoped name is restricted to its sheet and can cause confusion if the list is used elsewhere.

Why can someone enter a value that is not in the list?
The Error Alert may be turned off, or its style may be Warning or Information rather than Stop. Review Data > Data Validation > Error Alert if the goal is to block unlisted entries.

Can I make the drop-down show unique choices only?
Not by relying on table-based validation alone; repeated source entries can appear repeatedly. Remove duplicates from the source list or build a separate unique-choice range, then point a name at that range.

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.