Build a Budget from Your Bank Statements in Excel

Most budgets fail because they start from guesses — what you think you spend on food, what you hope you spend on subscriptions. Your bank statements contain the actual answer. Building a budget from three months of real transactions takes about an hour in Excel and produces something no template can: a budget based on evidence about your own behavior.

Step 1: Get three months of transactions into Excel

Download your last three monthly statements as PDFs from online banking, then convert them with our free statement converter — drop all three in at once and you'll get one workbook, one sheet per month, with the files never leaving your device. Combine the months into a single sheet with columns for date, description, and amount. Three months matters: one month is always unrepresentative — someone's birthday, an annual insurance bill, an unusually quiet month.

Step 2: Categorize with a lookup column

Add a "Category" column. You can type categories manually (fast enough for 200 rows), but the smarter route is a keyword table: list recurring merchants on a second sheet next to their categories ("UBER" → Transport, "WOOLWORTHS" → Groceries, "NETFLIX" → Subscriptions), then use a formula like =IFERROR(INDEX(Keywords!B:B, MATCH(TRUE, ISNUMBER(SEARCH(Keywords!A$2:A$50, B2)), 0)), "Other") entered as an array formula. Ten minutes of keyword setup categorizes 80% of rows automatically; hand-fill the rest. Keep categories broad — eight to twelve is plenty; forty invites abandonment.

Step 3: The pivot table that shows the truth

Select your data, Insert → PivotTable, drag Category to Rows, Amount to Values, and the month to Columns. You now see spending per category per month — and this is where budgets get real. The number people expect to surprise them (restaurants) usually does, but the quieter ones matter more: subscriptions that sum to a car payment, fees, small daily purchases that compound. Sort categories by total, descending; the top five are where your money actually goes and where any budget adjustment has to happen.

Step 4: Set targets against evidence

Next to each category's three-month average, set a target. The discipline that makes this work: targets must be justified by the data, not by optimism. Cutting a $700/month grocery average to $400 because it sounds nice fails by February; cutting it to $600 with a specific change behind it (meal planning, different store) holds. Income minus the sum of targets is your planned surplus — give it a destination (savings transfer, debt payment) or spending will absorb it.

Step 5: The monthly refresh

A budget you never revisit is a document, not a system. Each month: download the new statement, convert, paste rows into your data sheet, refresh the pivot (right-click → Refresh), and compare actuals to targets. Fifteen minutes. After a few cycles you'll know which targets were honest — and your categories column doubles as tax-time preparation, since deductible expenses are already sorted.