A normal dropdown points at a fixed range, so a category typed one row below the range never appears. To make it grow, base the source on an Excel table, on a spilled formula with a hash reference, or on an OFFSET formula that counts the filled cells. The table method works in every modern Excel and is the easiest to maintain.
How do you make the dropdown follow an Excel table?
- On a Lists sheet type Category in A1 and your categories in A2 downwards.
- Select any cell in that list and press Ctrl and T. Tick My table has headers.
- On the Table Design tab, rename the table to tblCats.
- Choose Formulas, Name Manager, New. Name it CatList and in Refers to type =tblCats[Category].
- On your expense sheet, select the category cells, then Data, Data Validation, Allow List, Source =CatList.
Now type a new category in the first empty row under the table. The table expands to take it in and the dropdown includes it at once. Data Validation cannot use a structured reference directly, which is why the named range step is there.
What does the spill range method look like?
In Excel 365 or 2021, put this in D2 on the Lists sheet:
=SORT(UNIQUE(FILTER(Lists!$A$2:$A$200,Lists!$A$2:$A$200<>"")))
It lists each category once, alphabetically, ignoring blanks, and spills downward. Then use =Lists!$D$2# as the dropdown source. The hash means the whole spill, so the list grows and shrinks with the formula. Make sure nothing sits under D2, or the formula shows a #SPILL! error.
Is there a method for older Excel?
Yes. Define a name, say CatOld, that refers to:
=OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A$2:$A$200),1)
COUNTA counts the filled cells, and OFFSET returns a range that tall, starting at A2. Use =CatOld as the source. It breaks if there are gaps in the list, because COUNTA counts filled cells and not the position of the last one.
| Method | Needs | Grows when | Watch out for |
|---|---|---|---|
| Excel table and name | Any recent Excel | You type under the table | Name must refer to the table column |
| Spill range | Excel 365 or 2021 | Source rows change | Blocked spill cells give #SPILL! |
| OFFSET with COUNTA | Any Excel | You add to the end | Gaps in the list throw it off |
Does removing a category change old entries?
Which method should you pick? Choose the table if the list is typed by hand and you want the simplest set-up that survives for years. Choose the spill range if categories arrive from other data, for instance pulled from an expense log, and you want duplicates removed and the list sorted without effort. Keep the OFFSET version for files that must open in older Excel. Whichever you choose, finish by testing a new category, a renamed one and a deleted one.
No. Rows already filled keep their old text, so a category you delete from the list can still sit in the expense log. Before you delete or rename, filter the category column for the old name, fix those rows and then change the list. If a SUMIFS report goes quiet after a rename, this is the first thing to check.
Housekeeping tips
- Keep the list on its own sheet and do not put notes beneath it.
- No blank rows in the middle of the list.
- Trim stray spaces, since Dining and Dining followed by a space look identical in a dropdown but count as different categories in a report.
- Keep the number of categories small enough to scan.
Keep a dated note on the Lists sheet saying which method you chose. In a year you will not remember whether the source is a table, a spill or a formula, and the answer decides where you click to add a category.
Where categories change often, SpendixAI lets you create custom categories and sub-categories in the app, so imported statement rows can be filed under them without editing a validation rule.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


