How to categorise bank transactions in Excel

Start with the converter’s CSV (Date, Description, Paid in, Paid out, Balance). Turn it into a table with Ctrl+T and call it Txns (Table Design, Table Name).

Step by step

1. Make a keyword tableOn a new sheet, make two columns: Keyword and Category. For example: TESCO → Groceries, SHELL → Fuel, HMRC → Tax, RENT → Rent. Make it a table called Rules.
2. Add a Category columnIn Txns, add a column called Category.
3. Look up the first matching keywordIn Excel 365, enter =IFERROR(INDEX(Rules[Category],MATCH(TRUE,ISNUMBER(SEARCH(Rules[Keyword],[@Description])),0)),""). It finds the first keyword that appears anywhere in the description.
4. Fill the gapsFilter Category for blanks. For anything recurring, add a keyword to Rules; for one-offs, type the category directly.
5. Total each categoryInsert a PivotTable from Txns with Category in Rows and Paid in and Paid out in Values.

Categories for tax

If you’re self-employed, use the expense categories on the Self Assessment self-employment pages (such as office costs, travel, and accountancy fees) so the pivot table gives you the figures to enter. See bank statements for Self Assessment.

Keep personal and business apart

If one account mixes both, add a Personal category for anything that isn’t business, so it is easy to exclude.

Accounting software does this too

Xero, QuickBooks and FreeAgent have bank rules that categorise imported transactions automatically. If you’re going to use one, import the statements (see accounting imports) and set rules there instead.

Reuse your rules every year

Keep the Rules table in its own workbook. Next year, paste the new year’s CSV into the Txns table and most transactions categorise themselves, because the same shops, suppliers and employers come round again. Over time, the share you have to type by hand keeps shrinking.

Check the categories add up

The pivot table’s grand total of Paid out should equal =SUM(Txns[Paid out]). If it doesn’t, some rows have been filtered out of the pivot table or have a blank category.