How to Build a Budget from Your Bank Statements in Excel

By Syed Hassan, creator of SafeStatement · Last updated September 29, 2026

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 already contain the real answer. Building a budget from three months of actual transactions takes about an hour in Excel, and it gives you something no template can: a budget based on how you really spend.

This guide goes from PDF statements to a working monthly budget, with the formulas and a PivotTable you can reuse every month.

Step 1: Get three months of transactions into Excel

Download your last three monthly statements as PDFs from online banking. Include every account you spend from: current or checking accounts and credit cards. Convert them with our free statement converter. Drop all the files in at once and you'll get a single workbook with one sheet per statement. The files never leave your device.

Combine everything into one sheet named Data with these columns:

Use one sign convention throughout, with spending negative and income positive. Credit card statements often show purchases as positive, so flip those with =-C2. For the Month column, use =TEXT(A2,"yyyy-mm").

Why three months? One month is never typical. There's always a birthday, an annual insurance payment or an unusually quiet few weeks. Three months smooths that out. Six is even better if you have them.

Step 2: Remove transfers so nothing is counted twice

Paying your credit card from your current account shows up twice: once as a payment out of the current account and once as a payment in on the card. The spending itself is already counted in the individual card purchases. Filter the descriptions for words like PAYMENT, TRANSFER, "TO SAVINGS" and your card provider's name, and label those rows Transfer so they can be left out of the totals. Do the same for moves into savings, although you may want to track savings separately as a "category" of its own.

Step 3: Categorise automatically with a keyword table

Typing a category next to 300 transactions is tedious and error-prone. Instead, create a sheet named Keywords with two columns: a word that appears in the merchant description, and the category it belongs to.

Then in the Category column of your Data sheet (F2), enter:

=IFERROR(INDEX(Keywords!$B$2:$B$100, MATCH(TRUE, ISNUMBER(SEARCH(Keywords!$A$2:$A$100, B2)), 0)), "Other")

In Excel 365 this works as typed. In older versions, confirm with Ctrl+Shift+Enter. The formula finds the first keyword that appears anywhere in the description and returns its category. Anything it can't match becomes "Other".

Filter to "Other", add keywords for the most common merchants, and watch the list shrink. Ten minutes of setup usually categorises 80–90% of transactions automatically, and the table keeps working every month after that.

Keep categories broad. Eight to twelve is plenty: Housing, Utilities, Groceries, Eating out, Transport, Subscriptions, Shopping, Health, Kids, Fun, Savings and Other. Budgets with 40 categories get abandoned.

Step 4: The PivotTable that shows the truth

1. Click inside your data and choose Insert → PivotTable.
2. Drag Category to Rows, Month to Columns and Amount to Values.
3. Add a filter on Category to exclude Transfer.
4. Sort categories by grand total, largest spending first.

You now see spending per category per month, and this is where budgets get real. The category people expect to be a surprise (eating out) usually is. The quieter ones often matter more: subscriptions that add up to the price of a car payment, bank fees, and small daily purchases that add up. Your top five categories are where your money actually goes, and where any real change has to happen.

Step 5: Set targets based on the evidence

Next to the PivotTable, build a small budget table: Category, 3-month average (=AVERAGE() of the three month columns), Target and Difference.

The rule that makes this work is that every target must be justified by the data and by a specific change, not by optimism. Cutting a £700/month grocery average to £400 because it sounds good fails within weeks. Cutting it to £600 with a concrete plan behind it (a weekly meal plan, one shop instead of four) holds. Separate your categories into:

• Fixed: rent, loan payments, insurance. Set the target equal to the actual amount.
• Flexible essentials: groceries, fuel, utilities. Aim for modest cuts.
• Discretionary: eating out, shopping, subscriptions. This is where real changes happen.

Income minus total targets is your planned surplus. Give it a job straight away, such as an automatic transfer to savings or an extra debt payment on payday, or your spending will quietly absorb it.

Step 6: Refresh every month in 15 minutes

A budget you never revisit is just a document, not a system. Each month:

1. Download the new statement and convert it.
2. Paste the rows at the bottom of the Data sheet. If Data is formatted as a table (Ctrl+T), formulas and the PivotTable range extend automatically.
3. Check "Other" and add keywords for any new merchants.
4. Right-click the PivotTable → Refresh, and compare actual spending against targets.

After a few cycles you'll know which targets were realistic, and you can adjust. The categorised data also helps at tax time, because charitable donations, business expenses and medical costs are already sorted.

Useful extras once the basics work

• Find subscriptions automatically: in a helper column, =COUNTIFS(B:B, B2) shows how often each description appears. Anything that appears in every month at the same amount is a recurring charge worth reviewing.
• Chart the trend: select the PivotTable and choose Insert → PivotChart → Line to see whether each category is trending up or down.
• Sinking funds: for annual costs (car insurance, holidays, gifts), divide the yearly amount by 12 and budget that monthly, so the big bill doesn't wreck the month it lands in.

Common mistakes to avoid

• Counting card payments as spending. This double-counts. Label them as transfers (see Step 2).
• Forgetting cash. ATM withdrawals hide what the cash was actually spent on. If cash is a big share of your spending, keep a rough note for one month so you know what it goes on.
• Leaving refunds in the wrong category. A returned purchase should reduce the category it came from, not count as income.
• Setting targets once and never looking again. Review targets every quarter. Life changes and the budget should change with it.