TEMPLATES & BUSINESS WORKFLOWS

Data Validation Drop-Down List from a Table (Auto-Expanding)

A drop-down list that points at fixed cells like $A$4:$A$10 works until someone adds an eighth item. Then the new item is missing from the list, nobody notices, and entries start drifting. The fix is to build the drop-down from an Excel Table, so it grows by itself. This guide does that in five minutes for an expense category list, and adds a check for the one thing data validation cannot stop: pasted values.

Excel drop-down list from a table: the data validation source =CategoryList points at tblCategories, a 7-category list with October totals such as Rent 2,400.00 and Meals 140.95, out of 4,629.05 in expenses. — The drop-down reads the whole Table column, however long it gets.

What you will build

Sheet	Cells	What it holds
Lists	A3:B10	Table tblCategories: 7 expense categories and their October totals (SUMIFS)
Expenses	A3:E15	Table tblExpenses: 12 October 2026 expenses with a Category drop-down in column C
Expenses	G3:H7	Checks: invalid categories, total vs category totals, and a Ties check

Free download: Get the drop-down list workbook (.xlsx). The Table, the name and the validation are all set up. Works in Excel 2010 and later, Microsoft 365 and Excel for the web.

Step 1: Put the list in an Excel Table

Type the items in one column under a header (Category in A3), click any cell in the list and press Ctrl+T, then OK (My table has headers stays ticked). On the Table Design tab, change the Table Name from Table1 to tblCategories. A clear name pays off in every formula that uses it.

Excel Table named tblCategories on the Lists sheet with 7 expense categories from Rent to Professional Fees. — One column, one header, converted to a Table.

Checkpoint: The Table holds 7 categories, Rent to Professional Fees. Keep the list on its own sheet so nobody sorts or deletes it by accident.

Step 2: Give the Table column a name

Data validation does not accept a structured reference such as tblCategories[Category] directly in its Source box, so wrap it in a defined name. Go to Formulas > Name Manager > New (or Define Name), and enter:

Field	Value
Name	CategoryList
Scope	Workbook
Refers to	`=tblCategories[Category]`

The quickest way to fill in Refers to is to click in the box and select the Table column without its header: Excel writes the structured reference for you.

Excel New Name dialog creating CategoryList that refers to =tblCategories[Category]. — The name points at the Table column, not at fixed cells.

Step 3: Create the drop-down with Data Validation

On the Expenses sheet, select the Category cells (C4:C15). Go to Data > Data Validation, and on the Settings tab set:

Allow: List
Source: =CategoryList
In-cell dropdown: ticked

Prefer not to create a name? =INDIRECT("tblCategories[Category]") works as the Source too, and it is the same trick the dependent drop-down list uses. The name is easier to read and to audit.

Excel Data Validation dialog with Allow set to List and Source set to =CategoryList for the Category column C4:C15. — Allow: List, Source: =CategoryList.

Tip: Because the expenses are also a Table (tblExpenses), new expense rows inherit the drop-down automatically. On the Input Message and Error Alert tabs you can add a hint and a clear message for wrong entries.

Step 4: Use the drop-down

Click a Category cell and use the arrow, or keep your hands on the keyboard and press Alt+Down Arrow (the drop-down shortcut), then the arrow keys and Enter. If you type a category that is not on the list, the Stop alert rejects it.

Excel drop-down list open in the Category column, showing all 7 categories from the table. — All seven categories, in the order of the Table.

Checkpoint: The list shows all 7 categories. To change their order, sort the Table on the Lists sheet; the drop-down follows.

Step 5: Add an item and watch the list grow

On the Lists sheet, type a new category directly under the last row of the Table and press Enter. The Table expands to include it, CategoryList covers the new row, and the drop-down shows it straight away. Nothing needs editing. In the practice file, Professional Fees was added this way as the seventh category.

=SUMIFS(tblExpenses[Amount],tblExpenses[Category],A4)

Column B of the Table totals October spending per category with SUMIFS (the same pattern as summing monthly values with SUMIFS). New categories get the formula automatically because Tables fill calculated columns down.

Excel categories table with Professional Fees added as the last row and SUMIFS totals per category, such as Rent 2,400.00. — Type below the Table and it expands: list, name and totals.

Checkpoint: Rent 2,400.00, Utilities 372.55, Office Supplies 209.95, Software 83.00, Travel 486.20, Meals 140.95, Professional Fees 750.00.

Step 6: Catch entries that slipped past the drop-down

Data validation only checks what is typed. Paste a value into the cell, or add the rule after data is already there, and anything goes. Row 12 was pasted from another file and says Travel & Meals, which is not a category. Two formulas catch it. In H3:

=SUMPRODUCT(--(COUNTIF(tblCategories[Category],tblExpenses[Category])=0))

COUNTIF looks up every expense category in the list; those it cannot find return 0 and are counted. Then compare the total spending with the sum of the category totals, in H6:

=H4-H5

Excel check cells showing 1 invalid category, Travel & Meals, that was pasted past the drop-down, leaving 186.40 of 4,629.05 unassigned. — One pasted category hides 186.40 from the summary.

Why this matters: Total expenses are 4,629.05, but the category totals add up to only 4,442.65. The missing 186.40 would simply vanish from a report by category. Data > Data Validation > Circle Invalid Data also draws a red circle around every cell that breaks the rule.

Ready-to-share test: Pick Travel in C12 from the drop-down. H3 drops to 0, Travel on the Lists sheet rises to 672.60, Unassigned becomes 0.00 and H7 shows Ties.

To make a bad entry impossible to miss, add a conditional formatting rule with =COUNTIF(CategoryList,$C4)=0 on the Category column.

Troubleshooting

Problem	Cause	Fix
Excel rejects the Source or shows an error message	Structured reference typed in the Source box, or a typo in the name	Use =CategoryList or =INDIRECT("tblCategories[Category]").
New items do not appear	The source points at fixed cells, or the item was typed two rows below the Table	Point at the Table column; type directly under the last row.
The list shows blanks	The source range includes empty cells	A Table column has no trailing blanks; delete empty Table rows.
The arrow does not show	In-cell dropdown is unticked	Tick it in Data Validation.
Duplicates or messy order	The list itself is unsorted or repeats items	Sort the Table, or in Microsoft 365 build a clean list with SORT(UNIQUE()) and point a name at it with the # spill reference. See dynamic lists with FILTER.

Where to use it

Category lists are the backbone of an expense tracker and the petty cash log: consistent categories are what make the totals by category trustworthy. The same drop-down can also drive a dynamic chart.

Frequently asked questions

Can I use a table column directly in data validation?

Not by typing the structured reference into the Source box. Create a name that refers to the Table column (=tblCategories[Category]) and use =CategoryList, or use =INDIRECT(“tblCategories[Category]”).

Does the drop-down update when I add items to the table?

Yes. Add the item in the row directly below the Table, the Table expands and the name and drop-down include it immediately.

Can the list be on another sheet?

Yes. A name or INDIRECT works across sheets, which is why the practice file keeps the list on a separate Lists sheet.

Can I select multiple items from one drop-down?

Not with data validation alone. It allows one value per cell; multi-select needs VBA.

Why does pasted data ignore the drop-down?

Pasting replaces the cell, including its validation rule. Use Paste Special > Values to keep the rule, then run Circle Invalid Data or a COUNTIF check.

Final checklist

The list is an Excel Table with a clear name (tblCategories).
A defined name points at the Table column, not at fixed cells.
Data Validation uses List with Source =CategoryList and an error alert.
The data entry range is a Table too, so new rows get the drop-down.
A COUNTIF check counts entries that are not on the list.
Total spending ties to the category totals.

Practice file: Download the drop-down list workbook. The “How It Works” sheet explains the Table, the name and each check.

Build input sheets nobody can break

Browse the OneXcel Tutorial Hub for more workflow tips, explore ready-to-use Excel templates with validated input forms, or turn your data into a dashboard with the OneXcel Dashboard Builder.