Project: Build a monthly budget planner
15 minutes · 7 steps · free, no sign-up
Time to put everything together. You will turn a table of planned and actual spending into a real budget planner: it works out what you overspent, flags problem categories, totals everything and shows each cost as a share of your income. Change your income and the whole thing updates.
- Subtract with a formula and fill it down
- Flag overspending with IF
- Total a column with SUM
- Use an absolute reference for the income cell
- Format money, percentages and headers
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceFormulas you will use
=B2-C2 planned minus actual
=IF(D2<0,"Over","OK") flag overspending
=SUM(B2:B7) column total
=C2/$B$10 share of income (income cell locked)
=B10-C8 money left overThe key ideasSummary: Project: Build a monthly budget planner
You have built a complete budget planner using nothing but the basics: a few formulas, a fill down, an IF, one SUM per column and a single locked reference to your income. The structure is what makes it reusable. Inputs (planned, actual, income) are plain numbers, and everything else is a formula, so updating a figure updates every result.
Make it your own. Replace the categories with your own, add rows for the ones you have, and extend the SUM ranges to include them. Next ideas: add a column for last month and compare, or total only the categories marked "Over" using SUMIF from the Intermediate level.
Watch out forCommon mistakes
- Mixing up the subtraction direction.
=B2-C2(planned minus actual) makes overspending negative.=C2-B2flips the signs and then the IF test gives the opposite answers. - Forgetting the dollar signs on income. Without
$B$10the reference slides down the column and divides by blank cells, giving#DIV/0!errors. - Typing totals by hand. A total that is a typed number never updates. Keep inputs as numbers and everything else as formulas.
- Stopping the SUM range short. When you add categories, extend the range or add the new rows inside it. A total that skips a row looks right but is wrong.
Next stepTry it in the real Google Sheets
Recreate this in a real Google Sheet and then make it yours. Use Format → Conditional formatting to colour the Status column red when a cell says "Over", add a pie chart of actual spending with Insert → Chart, and freeze the header with View → Freeze → 1 row. For a full year, copy the sheet into one tab per month and add a summary tab that totals them.