How to Build Your First Monthly Budget
Don't have a budget structure yet? Start here — this spreadsheet guide assumes you already know what your categories should be.
Read the guide →
This guide assumes you already know what your budget categories are — rent, groceries, transport, the usual list. If you don't have that list yet, start with How to Build Your First Monthly Budget first, then come back. What's covered here is purely the spreadsheet mechanics: how to lay out a workbook so it doesn't fall apart the first time you add a transaction, how to write formulas that total themselves instead of you re-adding rows by hand, how to make Excel flag overspending visually, and how to carry the whole thing forward every month without rebuilding it from scratch.
What you'll be able to do: Build a working Excel budget spreadsheet — with self-totaling formulas and automatic overspending alerts — that you can duplicate every month in under a minute.
Outcome: "I have a Transactions sheet and a Summary sheet set up with the right columns and a dropdown-controlled category list."
Open a blank workbook. Rename Sheet1 to "Transactions." In row 1, type four headers across columns A to D: Date, Category, Description, Amount. That's the entire micro-start — you now have the skeleton every formula in this guide will hang off.
A budget spreadsheet that works long-term needs two sheets, not one: a Transactions sheet, which is a flat, chronological log of every entry you make, and a Summary sheet, which pulls totals out of that log by category. Keep the column order fixed as Date, Category, Description, Amount — formulas later in this guide reference these columns by letter, so changing the order means rewriting them. The part that actually prevents the sheet from breaking is the Category column: instead of letting yourself type category names freely, restrict it to a fixed list using Data > Data Validation > List. Free-typed text means "Groceries," "groceries," and "Grocery" are three different strings to Excel — a formula that totals "Groceries" will silently skip the other two, and you won't see an error, just a wrong number.
Here's what a working Transactions sheet looks like after a few days of entries. Row 2: 03/01/2026, Housing, Rent payment, -900. Row 3: 03/02/2026, Groceries, Weekly shop, -62.40. Row 4: 03/03/2026, Income, Salary deposit, 2400. Row 5: 03/05/2026, Transport, Fuel, -48. Row 6: 03/06/2026, Groceries, Top-up shop, -19.80. Notice Amount is a single signed column — expenses are negative, income is positive — rather than two separate Income and Expense columns. This matters because it lets you sum the entire column for a net cash-flow figure, and it lets a single SUMIF formula handle a category whether it's a cost or a deposit, without branching logic.
Outcome: "I have a Summary sheet where every category total updates itself the moment I add a new transaction."
On the Summary sheet, in cell B2, type one formula:
=SUMIF(Transactions!C:C,"Groceries",Transactions!D:D). Press Enter. If you entered
the rows from Chapter 1, it should return -82.20. That single cell is the pattern you'll repeat
for every other category.
SUMIF adds up a range, but only the rows where another range matches a condition you give it.
The syntax is =SUMIF(range to check, condition, range to sum). In the formula above,
Excel scans the whole Category column (C:C), finds every row where it says exactly "Groceries,"
and adds up the matching rows in the Amount column (D:D) — automatically, including rows you
haven't typed yet. If you ever need a second condition — say, groceries in March only — SUMIFS
lets you stack conditions: =SUMIFS(Transactions!D:D,Transactions!C:C,"Groceries",
Transactions!A:A,">="&DATE(2026,3,1)). The reason this matters more than it sounds: the
single most common way a budget spreadsheet quietly stops working is someone typing a hardcoded
total instead of a formula. It looks correct the day you type it and becomes wrong the moment you
add one more transaction, because nothing recalculates it for you.
Build out the Summary sheet with one row per category: A2 "Housing," B2
=SUMIF(Transactions!C:C,"Housing",Transactions!D:D) → -900. A3 "Groceries," B3
=SUMIF(Transactions!C:C,"Groceries",Transactions!D:D) → -82.20. A4 "Transport," B4
=SUMIF(Transactions!C:C,"Transport",Transactions!D:D) → -48. A5 "Income," B5
=SUMIF(Transactions!C:C,"Income",Transactions!D:D) → 2400. Add a final row that sums
column B with a plain =SUM(B2:B5) to get your net position: 1369.80. Add one more
transaction to the Transactions sheet right now and watch every one of these numbers update
without you touching them.
Outcome: "I have conditional formatting that turns a category red the moment it goes over budget, plus a chart showing where my money goes."
Add a Budgeted column next to your Summary totals — type a planned number for one category, say 300 for Groceries. Select the Actual cell next to it, go to Home > Conditional Formatting > Highlight Cell Rules > Greater Than, and reference the Budgeted cell. That's one working overspending alert.
Conditional formatting changes a cell's appearance based on a rule you set, and it recalculates every time the underlying number changes — same self-updating principle as the SUMIF formulas from Chapter 2. For a budget sheet, the rule that matters most is a simple "greater than" comparison: if Actual spend exceeds Budgeted amount, turn the cell red. You set this through Format > Conditional Formatting > New Rule > "Format cells that contain" > "cell value greater than," then point it at the Budgeted cell rather than typing a fixed number, so the rule still works if you change your budget next month. Pair this with a chart — select your category names and their Actual totals, then Insert > a bar chart or pie chart. A bar chart is more useful here than a pie chart because it's easier to compare category heights against each other at a glance, and you can add a second data series for Budgeted amounts on the same chart to see both numbers side by side.
Groceries has a Budgeted figure of 300 in cell C3 and an Actual figure of 340 in cell B3
(as a positive number, or use =ABS(SUMIF(...)) if you're keeping Amount signed
negative for expenses). Select B3, open Conditional Formatting > Greater Than, and type
=$C3 as the comparison value instead of a fixed number. Because 340 is greater than
300, B3 turns red immediately. Drop the Budgeted figure to 350 instead, and the same cell turns
back to its default color without you touching the formatting rule — the rule is comparing two
cells, not two hardcoded numbers.
Outcome: "I have a repeatable monthly routine: duplicate the tab, clear the data, and the formulas keep working without rewriting."
Right-click your current month's tab, choose Move or Copy, tick "Create a copy," and confirm. Do this now, even with the practice data still in it from earlier chapters — the point is to see, in under a minute, that the duplicate still works exactly like the original.
The formulas you built in Chapter 2 reference whole columns — Transactions!C:C,
not a fixed range like C2:C50 — which is exactly why they survive being copied into a new month
without breaking: a new tab still has a column C, so the formula still has something to point at.
This is the reusable habit the whole guide builds toward: duplicate the current month's tab, clear
out last month's transaction rows (keep the headers and the category list), and the Summary
formulas, conditional formatting rules, and chart all keep working unchanged. Keep one tab per
month rather than one giant running log — it keeps each month's chart and totals isolated, and it
means last month's numbers are never at risk of being accidentally edited while you're working on
the current one.
Your tab is named "Jan-2026." Right-click it, Move or Copy, Create a copy, and rename the result "Feb-2026" (double-click the tab name to rename). Delete the transaction rows from Feb-2026's Transactions area but leave the header row and the Data Validation list untouched. The Summary sheet's SUMIF formulas — still referencing Transactions!C:C and Transactions!D:D within that same tab's structure — now return zero for every category, which is correct: no transactions have been entered yet for February. As you log February's spending, every total, every conditional formatting color, and the chart populate themselves exactly as they did in January, with zero formula rewriting.
The complete guide — all 4 chapters, every checklist and exercise — as a clean, printable PDF. No email required.
Every guide on this site is produced with the Self-Directed Learning Framework: instead of an article that explains a topic at you, the framework turns it into a structure you work through — each chapter with one outcome, a starting action under twelve minutes, the minimum you actually need to know, a worked example, a checklist, and exercises. It starts from three questions:
Topic: Building a budget spreadsheet in Excel.
Outcome wanted: Have a working Excel budget spreadsheet with formulas that total themselves — within four days.
Starting point: Comfortable with basic Excel (typing, simple formulas) but has never built a structured, reusable workbook.
The prompt framework behind it is public: see the C.R.A.F.T. prompt framework, including the exact prompt that generated this library.
Don't have a budget structure yet? Start here — this spreadsheet guide assumes you already know what your categories should be.
Read the guide →Free, self-paced guides across everyday, professional and academic topics.
See all guides