Getting started

Categorise UK bank transactions in a spreadsheet, row by row

Use a keyword lookup table to label current-account transactions in pounds, with a Review bucket for anything the table does not recognise.

Editorial illustration for Categorise UK bank transactions in a spreadsheet, row by row

A keyword table does the job for a UK current-account statement in a spreadsheet: each keyword, such as a supermarket or phone provider name, points to a category. A formula searches every description against the table and writes the category, and anything unmatched goes to a Review bucket. Build the table once and it keeps working.

Why raw descriptions are hard to read

The description column mixes merchant names with card references, payment method text, dates and location fragments. The same shop can appear in three different forms. That is why rules based on a short, stable fragment of the name work better than typing categories by hand. we are not describing any bank's text format; look at your own rows and choose the stable part.

Step 1: make the keyword table

On a second sheet, put keywords in column A and categories in column B. Keep keywords short but specific.

KeywordCategory
SUPERMARKETGroceries
RAILWAYTransport
MOBILEPhone and internet
RENTHousing
SAVINGSTransfer to own account

Step 2: write the lookup formula

Assume descriptions sit in B2:B200 and the keyword table in Keywords!$A$2:$A$30 with categories in Keywords!$B$2:$B$30. In C2, enter:

  • =IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH(Keywords!$A$2:$A$30,B2)),Keywords!$B$2:$B$30),"Review")
  • Fill it down the column. SEARCH is not case sensitive, so 'Supermarket' and 'SUPERMARKET' both match.
  • If more than one keyword matches, this formula returns the last one in the table, so put the broadest keywords at the top.
  • In Google Sheets, the same idea may need ARRAYFORMULA around the ISNUMBER part; test it on a few rows before filling down.

A worked example in pounds

Say five rows come out like this:

DescriptionAmountCategory
Card payment SUPERMARKET 0231£61.40Groceries
Direct debit MOBILE provider£32.99Phone and internet
Standing order RENT£850.00Housing
Transfer to SAVINGS pot£300.00Transfer to own account
Card payment BOOKSHOP£14.50Review

Rows marked Review are your to-do list: add 'BOOKSHOP' to the table with the category Shopping, and next month it files itself. Add the totals with =SUMIFS($B$2:$B$200,$C$2:$C$200,"Groceries"). Here £61.40 + £32.99 + £850.00 + £14.50 is £958.89 of spending, since the £300.00 transfer is not spending.

How often should you update the table?

  1. After each month's import, filter the Review rows.
  2. Add a keyword for each new merchant that repeats.
  3. Once a quarter, scan for keywords that match too much, such as 'SHOP'.
  4. Keep the table with the workbook so a copy travels with it.

How do you deal with one-off and shared payments?

A bookshop or a gift will not appear in your table, and that is fine. Leave it as Review for the month, then decide whether it will repeat. If it will not, categorise it by hand and move on without adding a keyword. For payments to friends, such as splitting a dinner, use a category named Shared and keep a note, so you can see later how much you have been paid back and how much you have covered for others.

When a sheet stops being enough

A keyword table is fine for one account. If you handle several accounts or want less upkeep, an expense tracker does similar work with rules and corrections. SpendixAI is built around India and the rupee, so it may not suit a pounds account; we would not rely on it for UK statements without checking.

A last check: add up all categories and compare the total with the statement's paid-out figure. If they match, nothing was lost on the way.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports