How to Categorize Bank Transactions for a Monthly Budget

A converted bank statement gets you a clean list of transactions — but a list of transactions isn't a budget yet. A budget needs those transactions grouped into categories you can actually total up. Here's a straightforward way to get there in a spreadsheet, without needing dedicated budgeting software.

Start with 8-10 broad categories rather than 30 narrow ones — you can always split a category later once you see it's genuinely large. A reasonable starting set: Housing (rent/EMI), Utilities, Groceries, Dining Out, Transport, Subscriptions, Shopping, Health, Savings/Investments, and Other. The goal is coverage, not precision — every transaction should fit somewhere without much thought.

Add one new column next to your transaction data and manually assign a category to each row. Once every row has a category, a formula like =SUMIF(CategoryColumn, "Groceries", AmountColumn) gives you the monthly total for that category instantly, and updates automatically if you add more transactions later. This is the whole mechanism — everything else below is about making the manual assignment step faster.

Most recurring spending has a recognizable pattern in the narration text — a specific merchant name, a recurring subscription service, a utility provider. A formula like =IF(ISNUMBER(SEARCH("grocery-keyword",NarrationColumn)),"Groceries","") chained with a few more conditions (or a lookup table of keyword-to-category pairs and XLOOKUP/VLOOKUP) can pre-fill the obvious majority of transactions, leaving only the ambiguous ones for manual review.

Some transactions genuinely don't map cleanly to one category — a single large purchase that covers groceries and household items, a transfer to a family member, an ATM withdrawal you later spent in cash across several categories. Rather than guessing, an "Uncategorized" bucket is more honest than a wrong category, and keeps that total visible so it doesn't silently distort the categories it got merged into.

Once every row has a category, a pivot table (Insert → PivotTable) with Category in rows and Amount summed in values gives you a one-screen monthly breakdown, and adding the transaction month as a second field turns it into a month-over-month view without any extra formulas. This is usually the point where a budget stops being a spreadsheet exercise and starts being genuinely useful — you can see, not just estimate, where a given month's money actually went.