Build a small rules table, with a keyword on the left and a category on the right, to categorise a statement. A single lookup formula then labels every row whose description contains the keyword, and anything unmatched is flagged for you to decide by hand. After one good pass, the table does most of the work on every later statement.
How do I build the rules table?
On a second sheet, put keywords in column A and categories in column B. Use short, distinctive fragments, such as the first word of a merchant name, since statement descriptions often add store numbers and city names. Start with the ten merchants you see most. A starter table might look like this.
| Keyword (column A) | Category (column B) |
|---|---|
| GROCER | Groceries |
| FUEL | Transport |
| PHARM | Health |
| STREAM | Subscriptions |
| AIRLINE | Travel |
| PAYMENT THANK YOU | Card payment |
Which formula applies the table?
With descriptions in column B of the transactions sheet and the keyword list at Rules!$A$2:$A$60 and the categories at Rules!$B$2:$B$60, put this in C2 and fill down: =IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH(Rules!$A$2:$A$60,B2)),Rules!$B$2:$B$60),"Review"). SEARCH ignores case. If two keywords match, it returns the one lowest in the list. Blank keyword rows will match everything, so keep the range to filled rows only. This works in Excel and in Google Sheets, but test it on three rows you can check by eye first.
What do I do with the Review rows?
- Filter column C for Review.
- Look at each description and decide a category.
- Add a new keyword and category to the rules table, so the same merchant is caught next time.
- Recalculate and filter again until Review is nearly empty.
- Leave one-off oddities as Review or Other and move on.
How do I check the categories add up?
Total each category with =SUMIF($C$2:$C$300,"Groceries",$D$2:$D$300) where D holds amounts. Then add the category totals together and compare them with the sum of the whole amount column. Say the statement spends 5,200 across 2,100 groceries, 900 transport, 640 health, 450 subscriptions, 1,050 travel and 60 uncategorised. Adding gives 2,100 plus 900 plus 640 plus 450 plus 1,050 plus 60, which is 5,200. If the category totals come to less, a row has fallen out of every category.
When does a sheet stop being enough?
A rules table is excellent for a few hundred rows. It gets tedious when you add new merchants every week, split a purchase across categories or keep several accounts. SpendixAI works in the same order you just built: your own rules first, then your history and similar merchants, and an AI model only for what is left, and only if you turn AI on. It sends a normalised merchant name and an amount band, not your name, account numbers or balances. Your corrections become history, and unmatched rows can be held for review. Custom categories can carry a colour and tax settings.
Do you need to know what the bank itself does?
No. Whatever the issuer, the method is the same. Your statement is the list of lines, your table is the list of rules, and the check is that categories add up to the statement. If you have a question about a fee or interest line, ask the bank through its own channel.
How do I handle a purchase that fits two categories?
A single card swipe at a large store can include groceries, a gift and a household item. For a sheet, pick the category that holds most of the amount, or split the row into two lines whose amounts add up to the original. Say a 3,000 bill is 2,200 of groceries and 800 of household goods: make two rows of 2,200 and 800, and check they still total 3,000. Splitting only matters if you care about the difference, so do it for large mixed bills and leave small ones alone.
Next step
- Build the starter table today.
- Run it on one statement.
- Add keywords for every Review row.
- Save the table, since it improves every month.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


