Create a Budget from Bank Statements in Excel

TL;DR The most realistic budget starts from what you've actually spent, not what you think you spend. Convert three to twelve months of bank and credit card statements into one Excel table, remove transfers between your own accounts, and categorize every row. Then calculate a monthly average for each category, split costs into fixed, variable and irregular, and turn irregular costs such as insurance, car repairs and gifts into a monthly amount you set aside. That gives you a starting budget based on evidence. Each month after that, compare actual spending from the new statement with the budget and adjust.

Most budgets fail for the same reason: they're built from memory. People remember the rent and the phone bill, underestimate groceries, and forget entirely about the annual car insurance, the dentist and the three birthdays in May. A month later, the budget is "broken" and gets abandoned.

Your bank statements fix that. They hold a complete, unflattering record of where money actually went. Turn a few months of them into a spreadsheet and you can build a budget that reflects your real life, then use each new statement to keep it honest.

This guide walks through the whole process in Excel (Google Sheets works too): collecting the right statements, converting them, cleaning out transfers, categorizing, building the budget, planning for irregular costs, and tracking actual against budget each month.

How many months of statements do you need?

More history gives a better picture, but there's a trade-off with effort.

• Three months is the minimum to get useful averages for regular spending like groceries, fuel and eating out. It will miss most irregular costs.
• Six months catches more variation and some irregular items.
• Twelve months is ideal. It captures seasonal costs (heating in winter, holidays in summer, back-to-school spending), annual bills and one-off events. If you're going to do this once properly, do twelve.

You need statements for every account money is spent from: current accounts, joint accounts, credit cards, and any digital wallets or store cards. Missing a credit card means missing a big chunk of spending.

If some months are no longer visible in online banking, our guide to getting old bank statements covers how to request them.

Step 1: convert the statements into one table

You need every transaction as a row with at least date, description and a signed amount (money in positive, money out negative), plus an Account column so you know where each row came from.

Options:

• Bank downloads. Many banks let you download recent transactions as CSV. Check how far back they go.
• Copy and paste from PDFs. Workable for a few short statements, slow for a year.
• A converter. SheetStatement reads PDF statements, puts each transaction on one row with a signed amount, and checks each statement's balances before you export. The free plan includes 3 pages a month; Starter ($9), Pro ($19) and Business ($39) monthly plans cover larger volumes. Google Sheets users can follow our guide to bank statements in Google Sheets.

Then combine everything into a single table. Our guide to combining multiple bank statements in Excel covers the mechanics. Format the result as an Excel table (Ctrl+T) and call it Tx.

Your table should look something like this:

The figures are illustrative.

A note on credit card signs

On a credit card statement, purchases increase what you owe. For budgeting, flip that so purchases are negative and payments positive, matching your current account. Then everything can be summed the same way.

Step 2: remove transfers

This is the step most people skip, and it ruins the numbers. When you pay your credit card from your current account, the money appears twice: once leaving the current account, once arriving on the card. If you count both, or count the card payment as "spending", you double-count everything you spent on the card.

The same applies to:

• transfers to and from savings;
• moving money between a joint account and personal accounts;
• topping up a digital wallet from your bank.

Tag all of these as Transfer and exclude them from spending totals. The actual spending is the purchases on the card, not the payment.

A quick sanity check: the transfers should roughly cancel out across your accounts over the period. If your Transfer total is a large positive or negative number, something's tagged wrong, or you're missing one of the accounts.

Step 3: categorize every row

Keep the category list short. Ten to fifteen categories is plenty for a household budget. A starter list:

• Housing (rent or mortgage, council tax or property tax)
• Utilities (energy, water, internet, phone)
• Groceries
• Transport (fuel, public transport, parking)
• Car (insurance, servicing, repairs, tax)
• Insurance (other than car)
• Eating out and takeaways
• Subscriptions
• Shopping (clothes, household items)
• Health (prescriptions, dentist, optician)
• Children
• Gifts and celebrations
• Holidays and travel
• Fun (hobbies, entertainment)
• Savings and debt repayments

To speed it up, build a lookup table of keywords and categories, then use a formula to apply them. For example, with a Rules table that has Keyword and Category columns:

=IFERROR(INDEX(Rules[Category], MATCH(TRUE, ISNUMBER(SEARCH(Rules[Keyword], [@Description])), 0)), "Uncategorized")

That returns the category for the first keyword found in the description. Our guide to Excel formulas to categorize bank transactions explains this and some alternatives. Expect to handle the leftovers by hand; the aim is to get most rows automatically, not all.

Find your subscriptions while you're there

Recurring payments are easy to lose track of. Sort by description and look for the same merchant appearing every month at the same amount. Our guide to finding recurring payments in a bank statement has formulas that flag them automatically. Cancelling one or two forgotten subscriptions is often the quickest budget win.

Step 4: add a Type column

Alongside the category, label each row as one of:

• Fixed: the same, or nearly the same, every month. Rent, loan repayments, phone contract, most subscriptions.
• Variable: happens every month but the amount changes. Groceries, fuel, eating out.
• Irregular: doesn't happen every month. Annual insurance, car servicing, gifts, holidays, school trips.
• Income and Transfer, as before.

This split matters because each type is budgeted differently.

Step 5: calculate monthly averages

Add a Month column so you can group by month:

=TEXT([@Date], "yyyy-mm")

Then build a summary with a pivot table: categories as rows, months as columns, sum of Amount as values, filtered to exclude transfers. Our bank statement pivot table guide explains how.

Or use SUMIFS on a summary sheet. For the total spent on groceries over the whole period:

=-SUMIFS(Tx[Amount], Tx[Category], "Groceries")

The minus sign turns spending into a positive number. Divide by the number of months to get a monthly average:

=-SUMIFS(Tx[Amount], Tx[Category], A2) / MonthsCovered

where A2 holds the category name and MonthsCovered is a named cell with the number of months in your data.

Look at the monthly figures as well as the average. If groceries were steady at around the same amount but one month was much higher, find out why. It might be a one-off, like hosting a family event, and you might want to treat it as irregular instead.

Step 6: build the budget

Now you have evidence. Build a budget sheet with one row per category and these columns:

The figures are illustrative.

How to set each type:

• Fixed: budget the actual amount. If a contract renews soon, use the new price.
• Variable: start from the average, then decide whether to reduce it. Be realistic; cutting groceries by half on paper won't make it happen in the shop.
• Irregular: budget the monthly average, and actually set that amount aside every month (see next step).

Then add up the budget and compare it with your average monthly income. If spending is higher than income, the variable categories are usually where change is realistic.

Step 7: plan for irregular costs

Irregular costs are what sink most budgets. The car insurance renewal, a boiler repair, the dentist, gifts in December. They're predictable in total even when the timing isn't.

The approach: take the annual total for each irregular category from your twelve months of statements, divide by twelve, and move that amount into a separate savings pot every month. When the bill arrives, it comes out of the pot, not out of your monthly budget.

For example, if twelve months of statements show car-related irregular costs totalling 1,140, that's 95 a month into the car pot. The numbers come straight from the SUMIFS above.

If you only have three or six months of data, you'll need to add irregular items from memory or from other paperwork. Look for annual renewal letters and last year's receipts.

Step 8: track actual against budget each month

A budget is only useful if you check it. Each month:

1. Convert the new month's statements and append them to Tx.
2. Categorize the new rows. Your keyword rules will handle most of them.
3. Calculate actual spending by category for that month:

=-SUMIFS(Tx[Amount], Tx[Category], A2, Tx[Month], CurrentMonth)

4. Compare with the budget:

=Budget - Actual

A positive number means under budget; negative means over.

Use conditional formatting to highlight categories that are over budget. Keep it simple: one colour for over, nothing for under. Our guide to tracking expenses from bank statements covers a monthly tracking layout in more detail.

Every three months or so, revisit the budget itself. If a category is consistently over, either the budget was unrealistic or something has changed. Adjust the number rather than carrying on with a budget you never meet.

Common mistakes to avoid

Budgeting from net income you don't actually receive. Use the amounts that land in your account, not your gross salary. If your income varies, as it does for many freelancers, budget from your lowest typical month rather than the average, and treat anything above that as a bonus for savings or irregular costs.

Counting cash withdrawals as a category. A cash machine withdrawal tells you money left the account, not what it was spent on. If you use a lot of cash, either keep a rough note of what it goes on, or give it its own "Cash" category and set a budget for it.

Too many categories. Forty categories feel precise, but they make categorizing slow and tracking tedious. If a category is under a few percent of spending, merge it into something broader.

Ignoring refunds. A refund on a returned item should reduce spending in the same category, not be counted as income. Give it the category of the original purchase.

Forgetting the timing of income. If you're paid on the 28th, a calendar-month budget can make one month look terrible and the next look great. Some people budget from payday to payday instead. Pick one approach and stick to it.

Treating the first budget as final. Your first version is a draft. Two or three months of tracking will show which numbers were wishful thinking.

A worked example: a couple combining finances

A couple want a shared budget. Between them they have two current accounts, a joint account for bills, and two credit cards.

1. Statements. They collect twelve months of statements for all five accounts.
2. Convert and combine. Sixty statements become one table with an Account column. They check that each statement balances, which flags one month where a card statement was missing.
3. Transfers. Each of them transfers money into the joint account monthly, and both pay their cards from their current accounts. All of these are tagged as transfers. The Transfer total comes out near zero, which confirms they've got them all.
4. Categorize. A rules table with about sixty keywords handles most rows. They do the rest by hand in an evening.
5. Subscriptions. They find two streaming services they both pay for separately and a gym membership one of them forgot about.
6. Averages. A pivot table shows monthly spending by category. Eating out is higher than either of them guessed.
7. Budget. They set fixed costs at actual, reduce eating out and groceries modestly, and work out the monthly set-aside for irregular costs: car, gifts, holidays, home repairs.
8. Track. Each month, one of them converts the new statements and updates the actuals. They review the budget together every quarter.

Neither of them had to guess. Every number in their budget traces back to a real transaction.

Using Google Sheets instead

Everything above works in Google Sheets with small changes. SUMIFS, TEXT, pivot tables and conditional formatting all exist. For the categorization step, REGEXMATCH can be handy. If you want to summarize by month and category in a single formula, the QUERY function is very effective; see our guide to the Google Sheets QUERY function for bank transactions.

Keep the original statements

Keep the PDFs alongside your spreadsheet. If a number looks wrong, you can go back to the source. And if you ever need statements for something else, like a mortgage or rental application, they're already organized. See bank statements for a mortgage application.

Start your budget from real numbers

Upload your statement PDFs to SheetStatement, check each statement balances, and download Excel or CSV ready for categorizing. The free plan includes 3 pages a month, enough to try it on a real statement.

FAQ

How many months of bank statements should I use for a budget?

Twelve months is ideal because it captures annual and seasonal costs. Three months is the minimum for useful averages on regular spending.

Do I need credit card statements too?

Yes. If you spend on a credit card, its statement shows what you bought. The payment from your bank account is a transfer, not spending.

How do I stop transfers being counted as spending?

Tag every transfer between your own accounts, including credit card payments and savings transfers, as Transfer, and exclude that category from spending totals.

How do I budget for irregular costs?

Total each irregular category over twelve months, divide by twelve, and set that amount aside every month in a separate pot. Pay those bills from the pot when they arrive.

What's the fastest way to categorize transactions?

Use a keyword lookup table with a formula that assigns a category based on the description. It handles most rows automatically; review the rest by hand.

Can I do this in Google Sheets?

Yes. The same formulas and pivot tables work, and the QUERY function is useful for monthly summaries.

Is it safe to upload my statements?

See the security page for how SheetStatement processes and stores files, and review any service's policy before uploading financial documents.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39.