How to categorise bank transactions in a spreadsheet

Start from the right shape

Export with the accounting profile so each row has a date, a description and separate debit and credit columns, then add three columns of your own.

Keep the exported columns untouched. Adding your own columns to the right means you can re-export next month, paste the new rows underneath, and drag the formulas down.

A rules table beats tagging by hand

On a second sheet named Rules, keep two columns: a keyword and the category it implies. Twenty rules usually cover most of a personal account, because spending is repetitive.

Because the rules live in the sheet rather than in the rows, correcting a mis-categorised merchant fixes every month at once.

The formula that applies them

This looks up the first keyword contained in the description and returns its category, falling back to Uncategorised:

=IFERROR(
   INDEX(Rules!B:B,
     MATCH(TRUE, ISNUMBER(SEARCH(Rules!A$2:A$100, $B2)), 0)),
   "Uncategorised")

SEARCH is case-insensitive and matches anywhere in the description, which suits bank descriptions where the merchant name is buried among reference numbers and locations.

For the month column, normalise every date to the first of its month so grouping is stable:

=EOMONTH($A2, -1) + 1

Review only what did not match

The discipline that makes this quick: never scroll the whole sheet. Filter Category to Uncategorised and work only through those.

1. Filter to Uncategorised and sort by amount, largest first.
2. For each merchant you see, add one rule rather than editing the row.
3. Refresh and repeat. The list shrinks quickly, because the largest rows are usually the most repetitive.
4. Stop when what remains is small and genuinely one-off. Leaving a residual Uncategorised bucket is fine and honest.

A useful check on your rules: the sum of all categories must equal the sum of the debit and credit columns. If it does not, a row is matching two rules or none.

Summarising by month and category

With Category and Month populated, a pivot table does the rest: Month as columns, Category as rows, sum of Debit as values. That single table answers most of what people convert statements for.

• Spot the trend, not the month. One expensive month means little; a category rising for four months means something.
• Separate committed from discretionary spending. Rent, loans and insurance behave differently from restaurants and shopping.
• Watch subscriptions specifically. They are individually small, easy to forget and the easiest thing to cancel.
• Keep income out of the spending totals, or it will flatter every month it lands in.

If several months are involved, merge the statements into one export first so the pivot covers a continuous period.

Pitfalls

• Transfers between your own accounts are not spending. Give them their own category and exclude it from totals, or you will double-count.
• Refunds appear as credits against a spending category. Netting them off gives a truer figure than counting them as income.
• A card payment from a current account is not spending either — the spending already happened on the card statement.
• Annual payments distort a monthly view. Consider spreading them across twelve months for comparison.
• Descriptions change. Banks reformat merchant strings occasionally, so a rule that worked last year may quietly stop matching; the Uncategorised filter is what catches that.