How to build a monthly budget in Excel that adds itself up

You need two tables and about eight formulas. Here they are, in the order you'd type them, with the reason for each — and the mistakes that make budgets quietly wrong.

Contents

What a budget sheet has to do

Three things, and most templates only do the first: hold a plan per category, show what actually happened next to it, and tell you the difference in a way you notice. The fourth — filling the “actual” column from a list of transactions instead of by hand — is what makes the sheet survive past the first month.

Step 1: the plan

Two columns: category and planned amount. Put income categories in one block and expense categories in another, because you’ll total them separately.

the layout, rows 5 onward

A            B          C          D
Income       Planned    Actual     Difference
Salary       3,900
Freelance      400
Total income =SUM(B5:B6)

Expenses     Planned    Actual     Difference   % of income
Housing      1,400
Groceries      520
…
Total        =SUM(B10:B19)

Colour the Planned cells so it’s obvious they’re yours to type. Everything else on the sheet should be a formula — the moment one Actual is typed by hand and the next is calculated, the budget stops being trustworthy and nobody can tell why.

Step 2: differences that read the right way round

For income, actual minus planned: positive is good. For expenses, planned minus actual, so that a negative number always means “overspent”. Then one conditional-format rule on the whole Difference column: less than zero → red fill, greater than zero → green.

income vs expense differences

Income row:   =C5-B5        (earned more than planned → positive)
Expense row:  =B10-C10      (spent more than planned → negative → red)

This is the detail people skip. If both blocks use actual − planned, an overspend shows green.

what each category took

=IF($C$7=0, 0, C10/$C$7)

Divide by total actual income, guarded by an IF so an empty month doesn’t fill the column with #DIV/0!. Format as a percentage.

Step 4: the transactions list

A second sheet with four columns: date, description, category, amount. Make the category a dropdown (Data → Validation → List) pointing at your category list, so “Groceries” is always spelled the same. This one decision is worth more than any formula: SUMIFS matches text exactly, and a stray space in “Groceries ” loses every row that has it.

Step 5: fill Actual from the list

the formula that does the work

=SUMIFS(Transactions!$D$5:$D$400,
      Transactions!$C$5:$C$400, $A10,
      Transactions!$A$5:$A$400, ">="&$B$2,
      Transactions!$A$5:$A$400, "<="&EOMONTH($B$2,0))

Three conditions: the category matches this row, the date is on or after the first of the month (a cell you type, $B$2), and on or before the last day, which EOMONTH(date, 0) gives you. Copy it down both blocks; only $A10 changes.

don't reference whole columns

=SUMIFS(Transactions!D:D, Transactions!C:C, A10, …)

It works, but it’s a million rows per condition. Google Sheets slows visibly with a dozen of these. Stop at a row — 400, 1000 — and say so on the sheet.

Step 6: the bottom line

left over, and savings rate

Left over:     =C7-C20                 (total actual income − total actual expenses)
Savings rate:  =IF(C7=0, 0, C16/C7)    (the Savings row as a share of income)

Point the savings rate at the row, not at the word “Savings”, so renaming the category doesn’t break it.

Step 7: twelve months in one table

A Year sheet with twelve rows, one per month, each computing income and expenses from the same transactions list for its own date range.

one row of the year table

A6 (month):    =EOMONTH($B$2,-12)+1       ← first day of the month 11 months back; +1 month per row
B6 (income):   =SUMPRODUCT((dates>=A6)*(dates<=EOMONTH(A6,0))*ISNUMBER(MATCH(cats,IncomeCats,0))*amounts)
C6 (expenses): same with ExpenseCats
D6 (left):     =B6-C6

ISNUMBER(MATCH(cats, IncomeCats, 0)) is the test “is this row’s category in the income list” — one formula instead of a SUMIFS per category. IncomeCats and ExpenseCats are named ranges on your category list. Add a column chart of B and C; Excel and Sheets both draw it.

The mistakes that make budgets wrong

Typing an actual by hand in a column that’s otherwise formulas. Never; if a transaction is missing, add it to the list.
Categories typed free-hand instead of from a dropdown. SUMIFS misses the typo silently.
Signs: expense differences as actual − planned, so overspending looks like a win.
#DIV/0! in the percentage column before the first income of the month. The IF guard fixes it.
A macro to “refresh” anything. Nothing above needs one, and the sheet then opens in Google Sheets and Numbers without a warning.

If you’d rather not type it: Tally is exactly this sheet, with the transactions log, the year view and a page that explains each of these formulas in its own words.