Put one ARRAYFORMULA in the header cell and it fills the whole column, including rows you add later. For a category column, a pattern like =ARRAYFORMULA({"Category";IF(B2:B="","",IFNA(VLOOKUP(TRIM(B2:B),$F$2:$G$200,2,FALSE),"Uncategorised"))}) in C1 does the heading and every result at once, with no dragging down.
Why use ARRAYFORMULA at all?
A normal formula works on one row. You copy it down, and each month you paste new rows, you copy it again, sometimes forgetting. An ARRAYFORMULA applies the same logic to a whole range from one cell. There is a second reason: IF, VLOOKUP and similar functions look only at the first row of a range unless you wrap them. That wrapper is what ARRAYFORMULA is for.
| Point | Copy-down formulas | One ARRAYFORMULA |
|---|---|---|
| New rows | Must be copied again | Filled automatically |
| Editing the logic | Change and recopy 500 cells | Change one cell |
| Typing in the result column | Allowed, but silently breaks the pattern | Blocks the whole result |
| Speed on big sheets | Many formulas | One formula, usually lighter |
How do I write it step by step?
Say narrations are in B, your merchant table is in F2:G200 with merchant in F and category in G, and the category column is C. Clear column C, then build the formula in C1:
- Start with the plain formula for row 2: =IFNA(VLOOKUP(TRIM(B2),$F$2:$G$200,2,FALSE),"Uncategorised")
- Change the single cell to an open range, B2:B, which means from row 2 to the end of the column.
- Add the blank-row guard: IF(B2:B="","", ...) so empty rows stay empty and are not labelled Uncategorised.
- Wrap it all in ARRAYFORMULA.
- Put the heading inside it by wrapping the output in curly brackets with the word "Category" and a semicolon: {"Category"; ...}
The finished version is the formula at the top. The semicolon stacks the heading above the results.
Why does it say Array result was not expanded?
The result needs room. If any cell below C1 already holds a value, even a leftover formula or a stray space, Sheets shows #REF! with that message and names the blocked cell. Select the cells under the formula and press Delete. If you want to keep some manual overrides, handle them with an extra column instead of typing over the results.
What happens when I insert a row later?
Three cases:
- Add new rows at the bottom: the formula covers them because B2:B is open-ended
- Insert a row in the middle of the data: the formula result expands by one row too, and the new row is calculated
- Insert a column to the left: the references shift with it, so the formula keeps working
For a closed range such as B2:B500, rows added past 500 would be missed. The open-ended B2:B avoids that, at a small cost to speed on very large sheets.
Can I use it for other columns?
Yes, any row-by-row calculation. A tax-estimate column, say with the rate in K1, would be =ARRAYFORMULA({"Tax estimate";IF(D2:D="","",ROUND(D2:D*$K$1,2))}). A month label would be =ARRAYFORMULA({"Month";IF(A2:A="","",TEXT(A2:A,"mmm yyyy"))}). Functions that already handle ranges, such as SUMIF or FILTER, do not need the wrapper.
Does it slow the sheet down?
For a household's expenses, no. A few thousand rows of simple lookups calculate in a blink. Trouble starts only when you stack many array formulas over whole columns that also contain heavy functions. If a sheet feels sluggish, give each array formula a bounded range, such as B2:B3000, and delete unused rows at the bottom. Calculation speed is rarely the reason to avoid ARRAYFORMULA; a blocked spill area is far more likely to cause trouble.
Before you switch over
- Save a copy of the sheet first
- Delete the old column formulas fully, then paste the single one
- Check one row at the top, one in the middle and the last row against the old results
- Keep the merchant table on its own tab so no one types in the spill area
If you ever want the categorising done without any formulas, SpendixAI assigns categories during import, and you review the rows before saving.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

