Budget Planner Excel: Formulas That Do the Math for You

A budget planner in Excel does one thing paper can't: the math updates itself. Change one number in the Food row and the total, the amount remaining, and the percentage-of-income column all recalculate without you touching a calculator.

The basic layout

Set up four columns: Category, Budgeted, Actual, and Difference. Rows for Income, Housing, Bills, Food, Transportation, Debt, Savings, and Fun Money. At the bottom, a Total row that sums the Budgeted column and compares it to Income.

The formulas that make it work:

• Total Budgeted: =SUM(B2:B9) — adds every category in the Budgeted column.
• Remaining: =B1-B10 — Income minus Total Budgeted, so you see instantly if you've assigned every dollar or if some is unaccounted for.
• Difference per category: =C2-B2 — Actual minus Budgeted, so overspending in Food shows as a negative number the moment you type it in.

On a $2,700 take-home month, a filled sheet might show Income $2,700, Total Budgeted $2,700, Remaining $0 — every dollar assigned before the month starts, with the spreadsheet confirming the math instead of you checking it by hand.

Formatting that catches problems fast

Use conditional formatting on the Difference column — red for negative numbers, green for positive. A quick glance at the sheet shows which categories ran over without reading every row. This single formatting rule does more to keep a spreadsheet budget useful than any other feature in it.

The free Clear Desk Money Reset gives you a weekly plan, spending check, and next-step list to put this into practice.

Adding a yearly summary tab

A second tab that pulls each month's totals into one row per month turns twelve separate budgets into a year-long view. Use =January!B10 style formulas to pull the total from each month's tab automatically, rather than retyping numbers by hand — one typo in a manually copied number throws off the whole yearly total.

Protecting the formulas from getting overwritten

Lock the formula cells and leave only the Actual column open for editing. In Excel, select the formula cells, right-click, choose Format Cells, check "Locked," then protect the sheet. This stops a stray keystroke from deleting a SUM formula halfway through the month — a common way spreadsheet budgets quietly break without anyone noticing until the totals stop adding up.

Excel versus Google Sheets

The formulas above work identically in Google Sheets, with the advantage of syncing across a phone and computer without saving or emailing a file back and forth. Excel edges ahead if you want offline access or more advanced formatting. Neither is more "correct" — pick whichever one you'll actually open on payday.

When a spreadsheet isn't the right fit

If typing into cells feels like a chore rather than a shortcut, paper does the same math slower but with less friction to start. A spreadsheet earns its keep once you're tracking more than five or six categories, or once you want the yearly-tab view — before that point, a printed page might get filled in more consistently.

Common mistakes that throw off the totals

Typing over a formula cell is the biggest one. Click into the Remaining cell to check the number and accidentally type a 0 instead of just looking — the SUM formula is gone, and nothing warns you. Locking the sheet, covered above, prevents this, but if that step gets skipped, get in the habit of clicking a formula cell once to glance at the formula bar before typing anything into it.

Leaving a category blank instead of entering $0 causes the second most common error. If Transportation has no spending one month, put a 0 in the Actual column. An empty cell usually still sums correctly, but conditional formatting rules built around "greater than" or "less than" a blank cell sometimes skip it entirely, so that category quietly stops showing red or green when it should.

A third one: typing "n/a" instead of leaving the cell empty. Excel treats anything with a letter in it as text, and SUM skips text cells without an error message — the total looks right at a glance but is actually short by whatever got mistyped.

When the totals stop adding up

Start by clicking the Total Budgeted cell and reading the formula bar. Confirm the range still covers every category row and hasn't shrunk because a row got deleted instead of cleared — deleting shifts the SUM range down by one; clearing contents with the Delete key does not. Next, scan the Budgeted column for any cell aligned left instead of right — Excel right-aligns numbers automatically, so a left-aligned entry is almost always text hiding as a number. Fix that one cell and the total usually corrects itself, no rebuilding required.

Skipping the formula-building entirely

A budgeting app that builds this same category breakdown automatically from your bank account does the SUM formulas and conditional formatting for you, pulling real transactions instead of manual entry — worth it once you've confirmed your categories and just want the totals without maintaining a formula sheet.