Excel Data Validation: Create a List from a Table Column

To use an Excel table column as a data-validation dropdown source in Microsoft 365 for Windows, create a workbook-level name that refers to the table column, then use that name in the validation rule. For example, define a name that refers to =tblRequests[Category], and enter =CategoryChoices as the list source. The table reference expands as you add or remove table rows.

Why use a table column for a dropdown?

A table is a practical home for approved choices such as departments, product types, project statuses, or expense categories. Unlike a fixed range such as A2:A20, a table column adjusts when its rows change. That makes it less likely that a newly added choice will be left out of the dropdown, or that a deleted choice will remain in a stale range.

Data validation can display a dropdown in a cell and restrict manually entered values according to a rule. A list rule is useful when people should choose from a controlled set of labels rather than type their own versions. For example, a shared list can help prevent “In progress,” “In Progress,” and “In-Progress” from becoming separate categories in later summaries.

Excel tables also provide meaningful column references. In the examples below, the table is named tblRequests and has a column named Category. Replace those names with the actual table and column names in your workbook.

Set up the table and name

Put the choices in one column with a single header, such as Category. Avoid merged cells and duplicate headers.
Select the list and choose Insert > Table, or press Ctrl+T on Windows. Confirm that My table has headers is selected.
Click in the table, open the Table Design tab, and check the table name in the Table Name box. Give it a clear name such as tblRequests. Table names cannot contain spaces.
Choose Formulas > Name Manager > New. Enter a clear name, such as CategoryChoices, set the scope to Workbook, and set Refers to to =tblRequests[Category]. Select OK.

The structured reference points to the table’s data cells in the Category column, rather than its header. As the table gains or loses rows, the reference adjusts. Workbook scope makes the name available to validation rules on different worksheets; a worksheet-scoped name is limited to its sheet.

In Name Manager, use the table’s actual name and the exact column header. If a header contains spaces, Excel still permits a structured reference such as =tblRequests[Request Type]. Avoid adding quotation marks around the reference in the Refers to box.

Apply the data-validation dropdown

After defining the name, select the cells that should contain the dropdown. On Windows, open Data > Data Validation > Data Validation. On the Settings tab, set Allow to List, make sure In-cell dropdown is checked, and enter =CategoryChoices in Source. Select OK.

Click a validated cell to check that the dropdown arrow appears and the expected options are listed. If you want to explain the field to users, use the Input Message tab in the same dialog. The Error Alert tab controls the message shown when someone enters a value that does not meet the rule. The Stop style blocks ordinary invalid typing; Warning and Information alerts allow users to proceed after a prompt.

On a Mac, the data-validation controls are also on the Data tab, though their placement and dialog layout can vary by Excel release. The same named-range method works. Excel for the web has data-validation tools too, but some name-management and workbook-editing options may be less convenient than in the desktop app; use desktop Excel if you cannot create or edit the required defined name in your web version.

Why the name is needed

A common question is whether the table reference can be typed directly into the validation Source box. Excel’s list-source box does not reliably accept a structured table reference such as =tblRequests[Category] directly. It may report that the source evaluates to an error or reject the entry. The defined name acts as an intermediary: it refers to the table column, while the validation rule refers to the simple name.

This is different from formulas in ordinary worksheet cells, where structured references are commonly used directly. It is also different from entering a fixed range such as =$A$2:$A$20 in the source box. The fixed range can work, but it will not automatically follow table growth unless you also arrange for the range to expand.

When a source list is on another worksheet, a defined name is especially useful. Excel’s validation dialog can restrict direct cross-sheet range references, while a workbook-level name can refer to a range on another sheet. The same pattern—name first, then use the name in the source—keeps the setup easier to maintain.

Keep blanks and duplicates under control

The basic table-column method includes the table’s data cells, including any blank cells within the table. A blank row can therefore appear as an empty choice in the dropdown. Remove unnecessary blank rows from the table, or use a cleaned helper list if blank and duplicate entries need to be excluded.

Duplicate values in a table column can appear more than once in the dropdown. They do not usually stop validation from working, but they make the choices harder to scan and can conceal inconsistent source data. Keep the source list distinct when practical. You can sort it manually, or use a helper formula to produce a sorted list without blanks.

For a Microsoft 365 workbook, suppose tblRequests[Category] contains Hardware, Software, Services, a blank, and another Hardware entry. With that table column as the input, enter the following formula in cell H2 on a worksheet named Lists:

=SORT(UNIQUE(FILTER(tblRequests[Category],tblRequests[Category]<>"")))

The formula spills a sorted, unique list into the cells below H2: Hardware, Services, and Software. It uses FILTER, UNIQUE, and SORT, which are available in Microsoft 365 and Excel 2021 or later. If there are no nonblank source values, FILTER can return an error unless you provide its optional if-empty argument; ensure the source has at least one choice or adapt the formula for an empty list.

To use the spill list as a validation source, create a workbook-level name such as CategoryOptions that refers to =Lists!$H$2#, then enter =CategoryOptions in the validation Source box. The spill reference follows the changing length of the helper list. This method is more elaborate than naming the table column, but it is useful when the dropdown should omit blanks and duplicates or present sorted options.

Apply validation to table entry cells

Apply the rule to the cells where users will enter categories, not to the source column itself. If the entry area is also an Excel table, select its data cells in the relevant column and apply the rule. A table may expand when a user types directly beneath it, but do not assume every newly added row will inherit validation in every editing situation. Test the workflow by adding a row, then confirm that its cell has the dropdown. If it does not, extend or copy the validation to the new cells.

For a worksheet that is not a table, select a suitably sized entry range before opening Data Validation. You can also apply a rule to a larger range than is currently in use, but balance future capacity against maintaining a tidy workbook. Selecting an entire worksheet column can include the header and may make the rule awkward to manage, so a deliberate entry range is often clearer.

To inspect or adjust an existing rule, select a cell that uses it and open Data > Data Validation > Data Validation. The dialog shows the settings for the selected cell. If the selected cells have different rules, Excel may show a warning or make it harder to identify one consistent setup; inspect a representative cell at a time.

Common problems and what to check

Excel rejects the source. Confirm that the defined name exists, has workbook scope, and is spelled exactly as entered. The source should be =CategoryChoices, including the leading equals sign.
The dropdown is empty or incomplete. Check that the name refers to the intended table and column, that the table has data rows, and that the displayed cells are the ones with the validation rule.
A new table choice does not appear. Confirm that the new choice was added inside the table, not merely below it. If necessary, resize the table from Table Design > Resize Table, then check the name’s reference in Name Manager.
The dropdown contains an empty option. Look for blank rows inside the source table column. Remove them or use the helper-list approach to filter blanks.
Typing an unlisted value still seems possible. Review the Error Alert settings. A Warning or Information alert permits an override, and pasted values can bypass ordinary validation prompts. Validation is a data-entry aid, not a security boundary.
The rule appears on some rows but not others. Select a cell in each affected area and inspect its validation settings. Copying cells, inserting rows, or extending a table can result in inconsistent rules depending on how the workbook is edited.

Validation does not automatically repair existing entries that are already outside the list. To find such values, use Data > Data Validation and the Circle Invalid Data command where available, or compare entries against the approved list with a separate review process. Also remember that a user can paste data into validated cells in ways that bypass the normal typing check. For important reporting, periodically review the actual values rather than assuming every cell is valid.

Choosing between a table name and a helper list

Use a name that refers directly to the table column when you want the simplest maintainable setup and the source list is already clean. It needs no extra worksheet formula, and additions to the source table are reflected in the reference. Its main trade-off is that blank and duplicate source entries are not automatically removed.

Use a dynamic-array helper list when the dropdown should show only distinct, nonblank choices, or when you want to sort the options without changing the source table’s order. It adds a formula and a defined name, so it is more complex to troubleshoot. In Microsoft 365 or Excel 2021 and later, the relevant functions are available; in older releases, use a manually maintained clean list or another compatible approach rather than assuming those functions exist.

A fixed range is reasonable for a short list that rarely changes. For example, a small set of permanent statuses may not need a table. But if people regularly add or remove choices, a table-backed name reduces the risk of forgetting to update the validation source. A named fixed range can also make a source easier to read, but by itself it does not make the range dynamic.

Frequently Asked Questions

Can I use a table column directly in Excel data validation?
Usually, use a defined name instead. Define a name that refers to the table column, such as =tblRequests[Category], and put that name, prefixed with an equals sign, in the list’s Source box.

Will the dropdown update when I add a row to the source table?
Yes, when the name refers to the table column and the new choice is actually part of the table. A table usually expands as you type immediately below it, but confirm that it did expand if the new item is missing.

Can I use a table column on another worksheet?
Yes. Create a workbook-level name referring to the source table column, then use that name in the validation rule on the other sheet. This also avoids relying on a direct cross-sheet reference in the Source box.

How do I remove blanks and duplicates from the dropdown?
Use a helper list that filters out empty cells and returns unique values, then define a name for the helper’s spill range. In Microsoft 365 and Excel 2021 or later, FILTER, UNIQUE, and SORT can create that list.

Does data validation stop every invalid value from entering?
No. A Stop alert restricts ordinary invalid typing, but pasting and other editing methods can bypass validation. Treat the dropdown as a useful entry control, and verify data separately when accuracy matters.

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.