How to make a budget spreadsheet that you'll actually keep using

Most budget spreadsheets are not abandoned because the maths was wrong. They are abandoned in week three, when logging a coffee takes ninety seconds, the category list has thirty-eight entries, and the file no longer answers any question you actually have.

So this guide is in four parts: the decisions you make before you open a spreadsheet, the build itself with every formula typed out, the five specific failures that kill budgets in week three, and an honest note on when building your own stops being worth the time.

Part 1: Four decisions before you touch a cell

Decision 1 — How many categories, and what each one is for

The instinct is to be thorough. Thoroughness is what kills it.

Every category you add is a decision you have to make at the till, every time. Twelve categories is fast. Thirty-eight means you stand in the supermarket wondering whether nappies are "Groceries", "Kids" or "Household", and the answer is that it does not matter, and having to decide is exactly what makes you stop.

A workable default is fifteen to twenty spending categories, grouped into six or seven headings:

Group	Categories
Housing	Rent/mortgage, utilities, home
Transport	Fuel/transit, car costs, insurance
Food	Groceries, eating out
Personal	Health, clothing, subscriptions
Fun	Entertainment, travel, gifts
Obligations	Debt payments, childcare, insurance
Saving	Savings transfers, sinking funds

The test for whether a category earns its place: would you change your behaviour based on that number alone? "Coffee" is only worth splitting out of "Eating out" if you would genuinely act on seeing it. Otherwise it is data collection dressed up as budgeting.

Decision 2 — Cadence

Two workable rhythms, and one that does not work.

Log as you go (30 seconds, on your phone, at the moment of spending). Highest accuracy, highest friction. Suits people who already have a phone habit to attach it to.

Weekly import (10 minutes, Sunday, from your bank's CSV export). Lower accuracy on cash, near-zero friction, and the numbers are still fresh enough to act on. This is what most people should do.

Monthly catch-up does not work. By day 24 you are reconstructing a month you cannot remember, the session takes ninety minutes, and you will not do it twice.

Pick one. Put it in your calendar as a repeating event with a specific day and time. A budget without a scheduled moment is a file, not a habit.

Decision 3 — Who is in it

If you share money with someone, decide this now, because it changes the structure:

One pot. One spreadsheet, one set of categories, and both of you need read access. Add a "who spent it" column only if you will use it for planning rather than scorekeeping.
Separate pots plus shared bills. The spreadsheet tracks the joint account only. Personal spending stays out of it entirely. This is the lowest-conflict option and it is usually the right one.
Proportional split. Each person contributes a percentage of their income to the joint pot. Put the two incomes and the percentages on a Settings sheet so the contribution updates when pay changes.

The mistake is building for one person and retrofitting a partner in month four.

Decision 4 — The one number that matters

A dashboard with fourteen KPIs tells you nothing, because you cannot act on fourteen things. Pick one headline number and design the sheet to make it obvious.

Candidates, depending on where you are:

Situation	The number
Living paycheck to paycheck	Lowest projected balance before next payday
Paying off debt	Total balance remaining
Stable, building wealth	Savings rate
Irregular income	Months of expenses in the buffer
Overspending on variables	Left to spend this month

One number. Big font. Top left. Everything else on the sheet exists to feed it.

Part 2: Build it

This works identically in Google Sheets and Excel 2016 or newer. Everything below uses functions both support. Nothing here needs macros.

Step 1 — Three sheets, not fifteen

Create exactly three tabs: Settings, Transactions, Summary. Add more later only when you have a question the existing three cannot answer.

Step 2 — Settings

On Settings:

B1: the budget year, e.g. 2026
A4:A40: your category list, one per row
B4:B40: the group each category belongs to
C4:C40: the type — Need, Want or Saving
D4:D40: your monthly budget for that category

That type column in C is what makes a needs/wants/savings split possible later without re-tagging anything.

Step 3 — Transactions

On Transactions, headers in row 1:

A	B	C	D	E	F
Date	Description	Category	Amount	Type	Month

Two rules that prevent most spreadsheet bugs:

All amounts are positive. Do not use negative numbers for spending. The Type column is what tells income and spending apart. Mixed signs are the single most common reason a budget total comes out wrong.
One transaction per row, never a summary row. No blank spacer rows inside the data.

Column C gets a dropdown. In Google Sheets: Data → Data validation → Dropdown (from a range) → Settings!A4:A40. In Excel: Data → Data Validation → List → =Settings!$A$4:$A$40.

Column E pulls the type from your settings table so you never tag it by hand:

=IF($C2="","",IFERROR(INDEX(Settings!$C$4:$C$40, MATCH($C2, Settings!$A$4:$A$40, 0)), "?"))

Column F turns the date into a month key. Use the last day of the month — it sorts correctly, it is a real date, and it never breaks on locale:

=IF($A2="","",EOMONTH($A2,0))

Format column F as mmm yyyy. Then select F2:F2000 and fill the formula down. Yes, all the way to row 2000 — blank rows return "" and cost nothing.

Step 4 — The month headers on Summary

On Summary, put your month columns across row 4 starting in B4:

B4:  =EOMONTH(DATE(Settings!$B$1,1,1),0)
C4:  =EOMONTH(B4,1)

Fill C4 right to M4. Format the row as mmm. You now have twelve real month-end dates that exactly match column F on Transactions.

Put your categories down column A from A5:

A5:  =Settings!A4

Fill down. Now the two sheets can never drift apart.

Step 5 — The formula that does the actual work

In B5:

=SUMIFS(Transactions!$D$2:$D$2000,
        Transactions!$C$2:$C$2000, $A5,
        Transactions!$F$2:$F$2000, B$4)

Fill it right to M5, then down for every category. That is your entire budget engine: total the Amount column where the category matches this row and the month matches this column.

Note the mixed anchoring — $A5 locks the column, B$4 locks the row. Get that wrong and the fill produces convincing nonsense.

Add an annual column in N5:

=SUM(B5:M5)

Why row 2000 and not whole columns? Whole-column references (D:D) work in both apps, but the moment you add a total row at the bottom of Transactions, it gets swept into the sum. Fixed generous ranges do not have that failure mode. If you outgrow 2000 rows, change the number once with find-and-replace.

Step 6 — The needs / wants / savings split

Below your category rows, leave a gap and add four summary rows. If income and the three types sit in rows 28 to 31:

B28 (Income)   =SUMIFS(Transactions!$D$2:$D$2000, Transactions!$E$2:$E$2000, "Income",  Transactions!$F$2:$F$2000, B$4)
B29 (Needs)    =SUMIFS(Transactions!$D$2:$D$2000, Transactions!$E$2:$E$2000, "Need",    Transactions!$F$2:$F$2000, B$4)
B30 (Wants)    =SUMIFS(Transactions!$D$2:$D$2000, Transactions!$E$2:$E$2000, "Want",    Transactions!$F$2:$F$2000, B$4)
B31 (Savings)  =SUMIFS(Transactions!$D$2:$D$2000, Transactions!$E$2:$E$2000, "Saving",  Transactions!$F$2:$F$2000, B$4)

And the percentages, which are what you actually read:

B33  =IF(B$28=0,"",B29/B$28)
B34  =IF(B$28=0,"",B30/B$28)
B35  =IF(B$28=0,"",B31/B$28)

Format as percentages. Note B$28 again — the row is locked so the three percentages all divide by that month's income when you fill right. If you want to compare against the 50/30/20 targets, that rule and its edge cases are covered here — and there is a free 50/30/20 calculator if you just want the three numbers for your income without building anything.

Step 7 — The savings-rate cell

There are two honest definitions and they answer different questions. Build both; they take one row each.

Cash-flow savings rate — what was left over, whether or not you moved it anywhere:

=IF(B28=0,"",(B28-B29-B30)/B28)

Contribution rate — what actually left for savings and investments:

=IF(B28=0,"",B31/B28)

If the first is comfortably higher than the second, you have money leaking into a current account and quietly getting spent. That gap is usually the most useful single fact in the whole spreadsheet.

For the year, do not average the twelve monthly rates — average of ratios is not the ratio of totals. Use:

=IF(SUM(B28:M28)=0,"",(SUM(B28:M28)-SUM(B29:M29)-SUM(B30:M30))/SUM(B28:M28))

Step 8 — Two cells that make it useful on a Tuesday

Left to spend this month. Put your total monthly variable budget in Settings!B2, then:

=Settings!$B$2 - SUMIFS(Transactions!$D$2:$D$2000,
                        Transactions!$E$2:$E$2000, "Want",
                        Transactions!$A$2:$A$2000, ">="&EOMONTH(TODAY(),-1)+1,
                        Transactions!$A$2:$A$2000, "<="&TODAY())

EOMONTH(TODAY(),-1)+1 is the first day of the current month. The ">="& pattern — comparison operator as text, joined to a value with & — is how you get date and number conditions into SUMIFS. It is worth learning; it unlocks most of the interesting formulas.

Rolling 90-day spend, which is far more stable than any single month:

=SUMIFS(Transactions!$D$2:$D$2000,
        Transactions!$A$2:$A$2000, ">="&EDATE(TODAY(),-3),
        Transactions!$A$2:$A$2000, "<="&TODAY(),
        Transactions!$E$2:$E$2000, "<>Income")

Finally, add conditional formatting on your category grid so overspending is visible without reading. Select B5:M24, add a rule with the custom formula =AND(B5<>"", B5>Settings!$D4), and give it a red fill.

Part 3: The five mistakes that kill spreadsheets by week three

1. Too many categories. Covered above, and it is genuinely the biggest one. If you catch yourself hesitating over where something goes, merge the categories.

2. No scheduled moment. The spreadsheet is not the habit. The Sunday ten minutes is the habit. Without a repeating calendar entry and a fixed trigger, entry becomes something you do when you feel guilty, which is to say rarely and unpleasantly.

3. Budgeting for a person who does not exist. A budget with $0 for fun, $0 for eating out and $0 for impulse purchases is not a strict budget, it is a fictional one. It will be broken in week two, and breaking a budget once is what makes people stop opening it. Budget for the person you are — generously, if anything — and tighten later from real data.

4. No line for irregular expenses. This is the one that looks like bad luck and is actually bad structure. Car insurance, the annual renewal, Christmas, the vet, the tyres: none of them are unexpected, they are just not monthly. If they hit an otherwise-fine budget from outside it, every quarter looks like a failure. The fix is sinking funds — a line per irregular expense, funded monthly.

5. Precision theatre. Reconciling to the cent, chasing a $0.40 discrepancy, re-categorising last March. None of this changes a decision. A budget that is 97% accurate and gets used beats one that is perfect and gets abandoned. If a transaction is genuinely ambiguous, put it anywhere and move on.

Part 4: When to stop building your own

Building your own is genuinely worth it if you want to understand the mechanics, if your situation is unusual enough that no template fits, or if you enjoy it. Those are all good reasons and the guide above is enough to get you there.

Stop when one of these is true:

You are spending more time maintaining the tool than reading it. Rebuilding the layout for the third time is a sign.
You need things that are tedious to build correctly and easy to build wrongly: an amortising debt payoff schedule with rollover, a cash-flow calendar, a net-worth history, a paycheck-to-bill mapping.
You have broken it twice and cannot find the formula that is wrong. Debugging your own spreadsheet is a genuinely bad use of an evening.

For what it is worth, our Ultimate Annual Budget Spreadsheet is the version of this build with the tedious parts already done — the same SUMIFS engine, plus the payoff schedules and the cash-flow calendar, tested formula by formula. But if the eight steps above got you a working file, you do not need it, and you should keep the one you built. The sheet you understand is the sheet you will still be using in March.

If you would rather work on paper than on a screen, there are free sample pages from our printable binder — a zero-based monthly budget, an expense log and a bill tracker — that do the same job with a pen.