You can categorise a bank statement in a spreadsheet by keeping a small table of keywords and categories, then using a formula that searches each transaction description for those keywords. It takes about an hour to set up and an hour a month to maintain. This page teaches the formula properly and says plainly when a sheet stops being enough.
How do we set up the keyword table?
Put your cleaned statement on one sheet, with the date in column A, description in column B, amount in column C and category in column D. On a second area, say columns G and H, list keywords in G and the category for each in H. Use words that appear in your own descriptions, which you can see on your statement. Examples, all made up: grocery in G2 with Groceries in H2; fuel in G3 with Transport in H3; pharmacy in G4 with Medical in H4; rent in G5 with Housing in H5.
Which formula assigns the category?
In D2, enter this formula and fill it down:
- Excel: =IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH($G$2:$G$20,B2)),$H$2:$H$20),"Other")
- Google Sheets: =ARRAYFORMULA(IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH($G$2:$G$20,B2)),$H$2:$H$20),"Other"))
How it works: SEARCH looks for each keyword inside the description in B2 and returns a number if found, or an error if not. ISNUMBER turns that into TRUE or FALSE. Dividing 1 by TRUE gives 1 and by FALSE gives a division error. LOOKUP with 2 finds the last row that returned 1 and returns the matching category from column H. IFERROR supplies Other when nothing matches. If two keywords match, the lower one in the list wins, so place the more specific keyword lower down.
How do we total each category?
Once column D is filled, build a summary with SUMIFS. With categories listed in J2 down, put in K2: =SUMIFS($C$2:$C$500,$D$2:$D$500,J2). If you want one month only, add date limits: =SUMIFS($C$2:$C$500,$D$2:$D$500,J2,$A$2:$A$500,">="&$M$1,$A$2:$A$500,"<="&$M$2), where M1 and M2 hold the first and last dates.
A worked example with made-up numbers
| Description contains | Amount (₹) | Category assigned |
|---|---|---|
| GROCERY MART | -2,340 | Groceries |
| FUEL STATION | -1,500 | Transport |
| PHARMACY | -480 | Medical |
| UNKNOWN STORE 4471 | -910 | Other |
With the keyword table above, three of the four rows are matched and one falls to Other, which is exactly what you want: the unknown row is a prompt to review, not a hidden mistake. SUMIFS for Groceries returns -2,340. If you later add the keyword store to the list, be careful, since it would catch many unrelated merchants.
A tip on ranges: the formulas above stop at row 20 of the keyword table and row 500 of the data. If you add more keywords or more months of transactions, extend those ranges, or the extra rows will silently be ignored. Check the last keyword you added actually works by pasting its text into a spare description cell.
What are the limits of this method?
- Descriptions with the same merchant written several ways need a keyword for each spelling.
- The keyword table grows and needs regular maintenance.
- Generic descriptions, such as a transfer to a person, cannot be categorised by keyword alone; you decide row by row.
- The formulas get slower on thousands of rows.
- Every month you must paste in new data and extend the formulas.
When does a sheet stop being enough?
When the keyword table becomes a chore, a statement importer can take over the routine. SpendixAI accepts bank and card statements as PDF, CSV or Excel, applies your own rules first, then history and similar merchants, and an AI step only if you leave it on, with review before saving; corrections become history. It does not connect to your bank or read SMS. If you stay with the sheet, the formula above is a solid, honest tool.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
