Guide

How to categorise a year of bank transactions in Excel without retyping

Sorting a year of bank lines into categories by hand is slow and error prone. Most of it is repeats: the same landlord, the same software bill, the same fuel station. A short list of keyword rules does the bulk, and you only decide the leftovers.

1. Put every transaction in one table

One row per transaction with Date, Description, Account, Category and Amount. Spending as negative, money in as positive. If your files come from several accounts, keep an Account column so nothing is mixed up. See how to combine several bank statements into one Excel sheet.

2. Write keyword rules

A rule is a category and a few words: Rent and lease: rent, lease. Utilities: electric, water, internet. Match whole words, otherwise "fee" lands inside "coffee". Put the more specific rules first, because the first match wins.

3. Review what is left

Anything no rule matched should be visibly marked as uncategorised, not hidden. Go through those once, change the row or add a keyword, then reuse those rules for later files.

4. Summarise with SUMIFS

With the table in Excel, a category total is =SUMIFS(E:E, D:D, "Utilities") where E is Amount and D is Category. For a month, add date limits: =SUMIFS(E:E, D:D, "Utilities", A:A, ">="&DATE(2026,1,1), A:A, "<"&DATE(2026,2,1)). Because these are formulas, changes update existing summary rows. Add a summary row if you introduce a new category in Excel.

5. Check before you rely on it

Keyword matching is only as good as your rules. Scan the totals for a category that looks too large or too small, and compare the grand total with the statements. Transfers between your own accounts are not income or spending, so give them their own category.

The quick way

Statement Merger applies keyword rules you can edit, lets you change categories on the first 400 displayed rows or apply edited rules to all rows, and downloads an Excel workbook with the Transactions, a By category sheet and a By month sheet. The summary sheets use SUMIFS formulas linked to the Transactions sheet. Everything runs in your browser and nothing is uploaded. The free page exports the first 40 rows.

Open the free Statement Merger

No row cap and offline use: the one-time $19 offline pack is here: https://xargaartools.gumroad.com/l/statement-merger-offline-pack

This sorts and totals rows. It is not accounting or tax advice, and which category a cost belongs in for tax is a question for your accountant.