Yes. In Excel for Microsoft 365 on Windows, you can use a table column to supply choices for a data-validation drop-down. The reliable setup is to create a workbook-level defined name that refers to the table column, then use that name as the list source in Data Validation; the source expands as you add table rows.
What “data validation from a table” means
Data validation controls what users can enter in a cell. A common example is a drop-down for a Status field with choices such as New, In Progress, and Complete. Keeping those choices in an Excel table makes them easier to maintain than typing a fixed, comma-separated list into the validation rule.
For example, imagine a table named Table1 with a column headed Category. You want cells in a separate entry area to offer the values in that column. Excel’s Data Validation dialog does not reliably accept a table’s structured reference typed directly into its Source box. A defined name acts as an intermediary: it points to the table column, and the validation rule points to the name.
This is useful when list items change over time, when several sheets need the same choices, or when someone else maintains the source list. It also keeps the allowed values visible in the workbook rather than buried in a dialog box.
Set up a drop-down from a table column
First, make sure the source values are in an Excel table. Select a cell in the source range and choose Insert > Table (or press Ctrl+T on Windows). Confirm that the table has headers. Select a cell in the table, open Table Design, and check its Table Name box to see or change the table name. In this example, use Table1 for the table and Category for its column.
- Choose Formulas > Name Manager > New.
- Enter
CategoryListas the name. Use a simple name without spaces, and set Scope to Workbook if the validation rule will be used on multiple sheets. - In Refers to, enter the table column reference shown here. The assumed layout is an Excel table named Table1 with a column whose header is Category:
=Table1[Category]. - Choose OK, then close Name Manager.
- Select the cell or range that should have the drop-down. Choose Data > Data Validation.
- On the Settings tab, set Allow to List. In Source, enter
=CategoryList. Make sure In-cell dropdown is selected, then choose OK.
Click one of the validated cells to check that its drop-down appears and contains the expected values. If you want to guide users, set an Input Message in the dialog. On the Error Alert tab, keep the alert enabled and use the Stop style if entries outside the list must be rejected. A warning-style alert can allow users to proceed, so it is not the same as a strict restriction.
Why use a defined name instead of a direct reference?
A table column uses a structured reference, such as Table1[Category], rather than a conventional cell address such as $A$2:$A$20. Structured references are convenient in worksheet formulas, but the Data Validation Source box has restrictions and may reject a direct table reference. The defined name bridges that gap: it gives the list source a name that the validation dialog can use.
The name also makes the rule easier to read. =CategoryList is more understandable to a workbook maintainer than a long range address or an indirect formula. A workbook-level name can be used by validation rules on different sheets, while a sheet-level name is limited to its own worksheet and can create confusion if similarly named ranges exist.
When creating the name, use the table’s actual name and exact column header, including punctuation or spaces where applicable. For instance, if the header is Order Status, the structured reference uses that header inside the brackets. Do not include the header row itself as a choice: a normal table column reference points to the data values, not the heading.
What happens when the table changes?
The key benefit is that the defined name refers to the table column, not to a fixed block such as A2:A20. Add a new item in the row directly beneath the table, and Excel normally expands the table to include that row. The structured reference then includes the new item, so the named validation list can offer it without editing the validation rule.
Check that the new row actually became part of the table. Excel may not expand a table in every situation—for example, a blank row or a separated entry can prevent an adjacent entry from being included as expected. You can also add a row from within the table using its last cell and Tab, or use Table Design > Resize Table to adjust the table range.
Deleting a source item removes it from the list of choices going forward, but it does not automatically erase that value from cells where it was selected previously. Likewise, changing a source label does not necessarily update old entries. If consistency matters, review existing data after changing the list, or use a separate checking process to identify values that no longer match.
Excel tables can help carry formatting and formulas into new rows, but do not assume every way of adding or pasting rows will preserve the validation rule on the destination cells. After adding rows to the entry area, check that the new cells still have the intended validation. Pasting content can replace validation settings, depending on what is copied and how it is pasted.
Blanks, duplicates, and list order
A table column can contain blank cells and repeated values. Data Validation does not automatically remove duplicates or sort the choices. Depending on the list and how it is displayed, blanks may appear as empty choices. For a clean user-facing menu, keep the source column free of accidental blanks and decide whether repeated entries are meaningful. If the values are categories, duplicates usually add clutter; if they are names or records, repeated text might be legitimate.
For a short list, the simplest approach is to clean the source table itself: remove unwanted blank rows, standardize spelling, and sort the source column if you want the drop-down to appear in that order. Sorting the table changes its row order, so consider whether that order matters to other users or formulas.
In Microsoft 365, another option is to create a separate, distinct and sorted helper list with a dynamic-array formula. With the source table named Table1 and its Category column containing the values, place this formula in an unused cell such as H2: =SORT(UNIQUE(FILTER(Table1[Category],Table1[Category]"<>"""))). It returns a sorted list of unique, nonblank categories and spills into cells below H2. The formula assumes that the source is exactly Table1[Category]; make sure the spill area is empty. To use the result in validation, create a workbook-level name referring to the spill range, such as =Sheet1!$H$2# (substitute the worksheet’s actual name), then set the validation Source to that name with a leading equals sign. Dynamic-array functions such as FILTER, UNIQUE, and SORT are available in Microsoft 365 and Excel 2021 or later, but not in every older Excel release.
Common problems and how to fix them
- The Source box rejects the reference: Use the defined-name method rather than typing
=Table1[Category]directly into Source. Confirm that the name exists and that its scope is appropriate. - The drop-down is empty or incomplete: Check the spelling of the table name and column header in Name Manager. Also confirm that the values are in the table’s data rows, not outside its boundaries.
- A new item does not appear: Verify that the new source entry is inside the table. Then reopen the list and check the defined name in Formulas > Name Manager.
- The list shows blanks or repeated choices: Clean the source data or use a helper list that filters blanks and returns unique values. Validation itself does not deduplicate a source.
- Users can type values that are not listed: In Data Validation, inspect the Error Alert tab. A disabled alert or a non-blocking alert may not prevent an invalid entry. Also remember that pasting data can bypass or replace validation in some workflows.
- The rule works on one sheet but not another: Check the defined name’s Scope. A sheet-level name may not be available to a validation rule elsewhere; a workbook-level name is often the simpler choice for shared lists.
Alternatives and trade-offs
For a list that will never change, typing options directly into the Source box can be quick. Separate each choice with the list separator Excel expects in your regional settings. This approach is less convenient for long lists, and editing the choices later means changing the validation rule itself.
A fixed cell range can also be used as a list source in some layouts, especially when the range is on the same sheet. But a fixed range will not grow simply because you add a value below it. You must update the source range or use another mechanism. A table plus a defined name is a better fit when additions are expected.
A helper range with a dynamic-array formula is useful when you need unique, sorted, or filtered choices rather than every raw table entry. It adds a formula and requires a clear spill area, so it may be unnecessary if the source table is already clean. Choose the simplest method that matches the way the list is maintained.
Excel for Mac and Excel for the web
The instructions above use Excel for Microsoft 365 on Windows. In current desktop versions for Mac, the same basic approach applies: create a defined name in Name Manager, then choose Data > Data Validation and use the name as the list source. Ribbon placement and dialog appearance can differ slightly by release.
Excel for the web supports data validation, but its available controls and name-management options can differ from the Windows desktop app and may change over time. If you cannot create or edit the needed defined name in the browser, open the workbook in desktop Excel to set up the named table reference. A workbook created with the rule can still be opened in the web app, but check the drop-down and editing behavior in the environment where users will enter data.
Frequently Asked Questions
Can I use a table column directly in Data Validation?
Excel may reject a structured reference typed directly into the Source box. Create a defined name that refers to the table column, then use the name, such as =CategoryList, as the validation source.
Will the drop-down update when I add a row to the table?
Yes, when the new value is included in the source table, a name referring to its column follows the expanded table. If the value does not appear, first confirm that the table itself expanded to include the new row.
Can a table-based drop-down use a column on another sheet?
Yes. A workbook-level defined name can refer to a table column on another worksheet. Use that name in the validation Source rather than trying to enter a cross-sheet cell reference directly.
How do I remove duplicates from the drop-down?
Data Validation does not remove duplicates automatically. Clean the source table, or in Microsoft 365 or Excel 2021 and later, create a helper list with UNIQUE and use that list as the validation source through a defined name.
Why can someone paste an unlisted value into a validated cell?
Validation is not a complete security control. The alert settings may allow an override, and copying or pasting can bypass or replace validation. Use a Stop error alert, review pasted data, and apply other controls if entries must be checked rigorously.
Related
- Compress Pictures in PowerPoint: Steps and Best Settings
- Can You Record a Presentation in PowerPoint and Export It
- Visual Budget Spreadsheet Template: Build a Monthly Plan
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Text Filters: How to Find Matching Rows EasilyIn Excel for Microsoft 365 on Windows, select a cell in your data and choose Data > Filter,…
- Disable Strikethrough in Excel: Quick Steps and FixesTo disable strikethrough in Excel for Microsoft 365 on Windows, select the affected cell or range and press…
- How to Make a Flowchart in Word With Arrows: Step-by-StepTo make a flowchart in Word with arrows, add flowchart shapes from Insert > Shapes, type into each…
- Add a Custom Number Format in Excel: Steps & ExamplesTo add a custom number format in Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1,…
- Excel Keyboard Shortcut to Remove Decimal PlacesIn Excel for Microsoft 365 on Windows, select the cells and press Alt+H, 9 to show one fewer…
- Highlight a Full Column in Excel: Quick StepsTo highlight a full column in Excel, click its lettered header at the top of the worksheet, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.