Transaction categories
How to Categorise Bank Transactions in Excel
Reviewed 12 September 2026
Short answer
Short answer
Add a Category column and keep a separate rules table containing a merchant or keyword and its category. Use XLOOKUP for exact normalised merchant names, or Power Query and careful keyword rules for description matching. Return Uncategorised when no rule is reliable, then review those rows manually rather than forcing a guess.
The goal is not to make every row look complete. The goal is to make repeat transactions consistent while leaving uncertain ones visible.
A category system you can explain
Start with a short list that matches the decision you are making: groceries, dining, utilities, subscriptions, transfers, income and uncategorised, for example. Accounting and tax coding may require a different chart set by your accountant.
Exact merchant lookups are easy to audit. Keyword matching reaches more descriptions but needs ordering so a specific rule wins before a broad word. AussieBankStatements uses fixed rules rather than an AI model and lets you edit the result before download.
Step by step
- 01
Choose the category list
Use a controlled list rather than typing slightly different labels into every row.
- 02
Create a rules sheet
Store the normalised merchant or keyword beside the category it should return.
- 03
Apply exact matches first
Use XLOOKUP where descriptions can be normalised to a stable merchant value.
- 04
Handle unmatched rows
Use careful keyword rules or review them manually, leaving uncertain rows uncategorised.
- 05
Check transfers and refunds
These often resemble income or expenses but should not be classified from the amount sign alone.
Checks before you rely on the spreadsheet
- Category labels come from one controlled list.
- Specific rules run before general keywords.
- Uncertain transactions remain visible.
- An accountant reviews final tax coding where required.
Excel documentation used in this guide
Microsoft documentation checked 12 September 2026.
Questions people ask
Can XLOOKUP categorise bank transactions?
Yes, when you have a stable lookup value such as a normalised merchant name. XLOOKUP returns the category from the matching row and uses exact matching by default.
Should every transaction get a category automatically?
No. A visible Uncategorised result is safer than a confident but incorrect guess.
Does AussieBankStatements use AI for categories?
No. It uses fixed keyword and pattern rules, and the categories remain editable before download.