Yes. To make an Excel data-validation dropdown from a table column, create a workbook-level name that refers to the table column, then use that name as the list source. For example, name the range CategoryChoices, refer it to a table column, and enter =CategoryChoices in the Data Validation Source box; the list can then grow as you add table rows.
Why use a table column for a dropdown?
An Excel table is a useful source for a dropdown when its choices change over time. Unlike a fixed range such as A2:A20, a table column expands when new rows are added to the table. A validation list based on that column can therefore include new choices without requiring you to edit the validation rule each time.
Tables also give the source data a clear structure. You can label the column, sort or filter the table, and refer to the column by its name rather than guessing which worksheet cells contain the list. This is particularly helpful for shared workbooks used to enter products, departments, project statuses, or other controlled choices.
The important detail is that Excel’s Data Validation Source box does not reliably accept a table structured reference typed directly into it. A defined name provides a dependable bridge between the table column and the dropdown rule.
Set up the source table
Start with a single column of choices and a clear header. For example, create a table with a column headed Category and entries such as Hardware, Software, and Services. Select the data and choose Insert > Table, or use the Windows shortcut Ctrl+T. Confirm that the table has headers. On a Mac, use Insert > Table if the shortcut differs in your keyboard setup.
Click inside the table and open the Table Design tab. In the Table Name box, give it a short, descriptive name such as ProductTable. Table names cannot contain spaces, so use a continuous word or underscores. Avoid changing the column header casually after you build formulas or names that depend on it.
- Keep the header row separate from the choices; the table column reference excludes the header.
- Remove unintended blank rows from the table if you do not want blank entries in the dropdown.
- Use one choice per row, rather than separating multiple choices with commas in a cell.
Excel tables may include a Total Row, but the ordinary structured reference to a table column refers to the data items, not the header or total row. That makes it suitable for a list source.
Create a name that points to the table column
With the table called ProductTable and its column headed Category, create a workbook-level defined name. On Windows, choose Formulas > Name Manager > New. Enter CategoryChoices in the Name field, set Scope to Workbook, and enter the reference below in Refers to. The assumed layout is a table named ProductTable with a column whose exact header is Category.
=ProductTable[Category]
Choose OK, then close Name Manager. The name CategoryChoices is a label for that table column; it is not another copy of the list. Workbook scope is usually the most convenient choice because the name can be used by validation rules on different worksheets. If a table or column name contains spaces or punctuation, Excel may adjust how it displays the structured reference; selecting the column through the Name Manager interface can help avoid typing errors.
Do not include the equals sign in the Name field itself. The equals sign belongs in Refers to. Also, a defined name must follow Excel’s naming rules: for example, it cannot contain spaces or look like a cell address.
Apply the data-validation dropdown
Select the cells where people should make a choice. To apply the rule to a column of entry cells, select the intended range rather than the table’s source column. On Windows, go to Data > Data Validation. In the Settings tab, set Allow to List, leave In-cell dropdown selected, and use the name as the source. The assumed setup is that you created the workbook-level name CategoryChoices in the previous section.
=CategoryChoices
Choose OK. The selected cells should now show a dropdown arrow when selected. If users should not type values outside the list, keep the default error alert enabled and choose a suitable alert style, such as Stop. You can edit the alert’s title and message to explain what entries are allowed.
For a table that people will continue to fill in, apply validation to the relevant cells in its entry column. Excel often carries formatting and validation into newly added table rows, but behavior can depend on how rows are added and how the rule was applied. Test a new row in the actual workbook. If the new cell has no dropdown, copy a correctly validated cell or extend the validation rule to the added cells.
How the list updates when the table changes
The defined name refers to the table column rather than to a fixed address. When a new data row is added inside ProductTable, the structured reference includes the new row, so CategoryChoices can expose its value in the dropdown. This is the main advantage over a source such as =$A$2:$A$20, which does not automatically extend beyond row 20.
Adding a value below the last table row does not necessarily add it to the table. The cell must become part of the table, for example by typing in the row immediately below the table or by resizing the table. Check the table boundary if a newly entered choice does not appear.
A table-backed list is not automatically alphabetized or deduplicated. If the source column contains repeated values, repeated choices may appear. If it contains blank cells, the dropdown may include blank entries. Clean the source column or use a separate formula-driven list if you need a distinct, sorted set of choices.
Alternatives for direct and cleaned-up lists
A defined name is usually the clearest method, but another option is to use INDIRECT in the validation Source box. For the same ProductTable and Category column, this source refers to the structured reference by text:
=INDIRECT("ProductTable[Category]")
This can be convenient for a one-off rule, but INDIRECT is volatile: Excel recalculates it more often than many ordinary references. It also stores the table and column names as text, so renaming either can leave the text reference broken. A defined name is easier to inspect and maintain in a workbook with several validation rules.
For a dropdown containing unique, sorted, nonblank values, Microsoft 365 can generate a helper list with a dynamic-array formula. Put it in a normal worksheet cell outside any table; assume ProductTable[Category] is the source and the helper formula goes in Lists!G2:
=SORT(UNIQUE(FILTER(ProductTable[Category],ProductTable[Category]<>"")))
This returns distinct, alphabetized entries and excludes empty cells, provided the source has at least one nonblank value. Create a workbook-level name such as SortedCategories that refers to the spill range =Lists!$G$2#, then use =SortedCategories as the validation source. Keep the cells below G2 clear so the results can spill. SORT, UNIQUE, and FILTER are available in Microsoft 365 and Excel 2021 or later; earlier versions need a different method. A helper range is also useful if you want the dropdown values transformed without changing the original table.
Common problems and fixes
- The source is rejected: Check that the Source field contains the defined name with a leading equals sign, such as
=CategoryChoices. Confirm that the name exists and has workbook scope. - The dropdown is empty or incomplete: Verify the table name and column header in Name Manager, and check that the choice cells are actually inside the table.
- The header appears as a choice: The name may refer to a worksheet range that includes the header instead of the table data column. Check the Refers to field.
- New choices do not show: Confirm that the new row expanded the table, then reopen the dropdown. If needed, check that the name still refers to the intended table column.
- Some cells lack dropdowns: The validation rule may not have been applied to the whole entry range or to newly created rows. Select the affected cells and apply the same rule.
- Users can enter other values: Check that the error alert is enabled. A warning-style alert can permit users to continue; use Stop when invalid entries must be blocked.
When diagnosing a problem, select one affected cell and open Data > Data Validation to inspect its current rule. Then check Formulas > Name Manager separately. This distinguishes a missing validation rule from a name that points to the wrong source.
Choosing the right approach
Use a named reference to the table column for a straightforward dropdown that should grow as the source table grows. It is readable, avoids a fixed row limit, and works well for ordinary lists that may contain repeated values. Use INDIRECT only when its convenience outweighs the extra fragility of a text-based reference.
Choose a helper formula when you need a sorted, unique list or want to exclude blanks. That approach adds a spill range and a second name to maintain, but it keeps the original table intact. For older Excel versions without dynamic-array functions, consider maintaining a separate cleaned list or using a fixed range large enough for expected entries; be explicit about the maintenance required if that range is not dynamic.
Excel for Mac has the same basic concepts: a defined name can refer to a table column, and validation can use that name. Ribbon labels and dialog layouts can vary slightly by version. Excel for the web also has data-validation controls, but some workbook setup and name-management tasks may be easier in the desktop app. A practical workflow is to create and verify the name and validation rule in desktop Excel, then check the dropdown in the version where people will enter data.
Frequently Asked Questions
Can I put a table column reference directly in Data Validation Source?
Excel commonly rejects a structured reference typed directly into the Source box. Create a defined name that refers to the table column and use that name as the source instead.
Will the dropdown update when I add a row to the table?
Yes, if the new row is part of the source table. A row typed outside the table boundary is not included until it is added to or the table is resized to include it.
Can I use a table on another worksheet?
Yes. A workbook-level defined name can refer to a table column on another sheet, and the validation rule can use that name on the destination sheet.
How do I remove duplicate choices?
Clean duplicates from the source column, or use a helper formula with UNIQUE in Microsoft 365 or Excel 2021 and later. The ordinary table-column method does not remove duplicates by itself.
Why does the list show blank choices?
The source column may contain empty table rows. Remove those blanks or use a FILTER-based helper list that excludes empty values.
Does this work with Excel for Mac?
Yes, the named-range method is supported in Excel for Mac. The ribbon or dialog may look different from Windows; use the Formulas and Data tabs to find name management and validation settings.
Related
- Excel Conditional Formatting for Date Ranges Explained
- Excel Conditional Formatting for Upcoming Dates
- Conditional Formatting Excel Dates Before a Date Rule
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Merge Two Word Documents to Compare ChangesTo compare two Word documents, open Word for Microsoft 365 on Windows and choose Review > Compare >…
- How to Change Email View in Outlook 365: Layouts and PanesTo change the email view in Outlook 365, use View > Change View in classic Outlook for Windows…
- Excel Keyboard Shortcut to Hide a Row (and Unhide It)In Excel for Microsoft 365 on Windows, select a cell in the row—or select the whole row—and press…
- Word Styles: How They Work and When to Use ThemMicrosoft Word styles are saved sets of formatting that you can apply to text or paragraphs, such as…
- Excel Workbook Is Slow? Common Causes and Practical FixesAn Excel workbook is often slow because it contains too many calculations, oversized ranges, excessive formatting, or data…
- Google Sheets Dropdown: Show Only Selected ValuesYes. In Google Sheets, a dropdown cell normally displays only the value you selected after the menu closes;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.