A monthly budget in Excel works best as three sheets: Inputs for planned amounts, Transactions for actual spend, and Summary for totals and what is left. That layout makes income, fixed costs, variable costs and remaining cash easy to understand at a glance, and keeps the sheet simple enough to update honestly.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowThat is why building from scratch can be better than copying a complicated template you never fully understand. In this tutorial you will build a three-sheet monthly budget (Inputs, Transactions, Summary) in about 45 minutes, using Excel Tables, SUMIFS, and a small amount of data validation. By the end you will have a workbook you fully understand, can maintain in 10 minutes per month, and can adapt to either personal or small-business finances.
This tutorial assumes you know how to enter data, insert a sheet, and type a formula. You do not need VBA, Power Query, or any paid add-ins. It works in every Excel version from 2016 onwards (including Excel for the web and Excel for Mac).
For the wider Excel skill stack that makes budgets scale — pivot tables, dashboards, data cleanup — start at the Excel formulas and analysis hub.
What is the quickest way to build a monthly budget in Excel?
Build three sheets: Inputs (list of categories with Planned amounts), Transactions (an Excel Table where you log actual spend per row), and Summary (SUMIFS formulas that pull actuals by category and show remaining cushion). Inputs and Transactions are editable. Summary is read-only. This three-zone separation is what makes budgets survive past week two.
Use this approach when:
- You want a budget you can genuinely maintain — no elaborate macros, no copy-pasting every month.
- Existing templates feel cluttered or opaque — you cannot tell which cells you are allowed to edit.
- You need a base sheet that can later support analysis, forecasting, or a connected dashboard.
Which categories should a monthly budget include?
A personal monthly budget usually groups its categories into Fixed, Variable and Goals, while a small-business budget uses Income, Fixed costs, Variable costs and Cash reserve.
The best category list is not the longest one. It is the one that reflects how you or the team really track money. For most people, 8-12 categories is the sweet spot — fewer than 8 hides useful variance, more than 15 means many categories stay at zero and the budget stops feeling informative.
Personal budget starter categories
- Fixed: Rent/Mortgage, Utilities, Internet/Phone, Insurance, Subscriptions (Netflix/Spotify/gym), Loan repayment
- Variable: Groceries, Dining out, Transport (fuel/public), Household, Personal care, Medical
- Goals: Savings, Investment, Debt principal payoff
Small business starter categories
- Income: Revenue by channel (direct, marketplace, wholesale), Other income
- Fixed costs: Rent, Payroll, Software licences, Insurance, Accounting/Legal retainers
- Variable costs: Marketing, Shipping/Delivery, Packaging, Payment processing fees, Inventory replenishment
- Cash reserve: Tax reserve, Emergency fund top-up
Real-world scenario. A freelance designer tracked 28 expense categories in her first month and gave up by week three — every coffee required a decision ("is this 'Dining', 'Work lunch', or 'Client meeting'?"). Switching to 10 broader categories cut entry time from 90 seconds to 15 seconds per transaction, and she stayed consistent for the full year.
Beginner pitfall. Inventing categories that do not match how money actually leaves the account. "Self-improvement" sounds useful until you realise it could be books (reading), courses (career), therapy (medical), or a retreat (travel) — the category is too abstract and every entry becomes a philosophical debate. Categories should map to how your bank statement groups spend, not to aspirational life buckets.
How should you lay out a monthly budget spreadsheet?
Enter transactions or monthly amounts in one clear area, then let totals and remaining balances sit in a separate summary view. This is the single highest-impact structural choice for a budget spreadsheet. When entry and summary live in the same cells, one bad paste destroys a formula and the budget breaks silently.
Create three sheets in one workbook:
- Inputs — Two columns:
CategoryandPlanned. One row per category. This is the only place you define monthly targets. - Transactions — An Excel Table with columns
Date,Category,Amount,Note. One row per real-world transaction. Convert to a Table withCtrl+Tand name ittblTransactions. Add a data validation dropdown on theCategorycolumn using the Inputs categories as source — this stops typos that would otherwise fragment the SUMIFS. - Summary — Four columns:
Category,Planned,Actual,Remaining. Formulas pull from Inputs and Transactions. Protect this sheet (Review → Protect Sheet) so nobody accidentally overwrites a formula.
How do you calculate the remaining budget for each category?
The remaining budget for a category is its planned amount minus the SUMIFS total of its actual transactions, and a Month cushion row does the same for the whole month.
On the Summary sheet, beside each category row:
Planned: =XLOOKUP(A2, Inputs!A:A, Inputs!B:B, 0)
Actual: =SUMIFS(tblTransactions[Amount], tblTransactions[Category], A2)
Remaining: =B2 - C2
The fourth argument 0 on XLOOKUP is the "if not found" value, so a category that hasn't been entered on Inputs shows £0 planned rather than #N/A. On Excel 2019, swap to =VLOOKUP(A2, Inputs!A:B, 2, FALSE) — same result, less graceful when keys don't match.
At the bottom of the Summary sheet, add a single "Month cushion" row:
Total planned: =SUM(B2:B15)
Total actual: =SUM(C2:C15)
Month cushion: =SUM(B2:B15) - SUM(C2:C15)
How do you highlight overspending and protect a budget spreadsheet?
A budget spreadsheet needs two finishing touches: a conditional formatting rule that turns overspent categories red, and sheet protection that stops anyone overwriting the Summary formulas.
Apply conditional formatting on the Remaining column: green if positive, red if negative. Select the Remaining range, Home → Conditional Formatting → New Rule → "Format only cells that contain" → Cell Value → less than → 0 → red fill, then repeat for greater than 0 → green fill. The whole sheet becomes a traffic-light view of the month.
Then protect the Summary sheet so nobody accidentally overwrites a formula: Review → Protect Sheet → leave the password blank for personal use, or set one for shared workbooks. The Inputs and Transactions sheets stay unprotected — that is where editing happens.
Real-world scenario. A couple tracking joint expenses used to both edit the Summary tab directly; one partner would update "groceries" to reflect a new receipt, then the SUMIFS further down would return wrong totals because the "groceries" cell was no longer a formula. Splitting into Inputs, Transactions, Summary and protecting the Summary sheet ended the silent corruption — they lost track of variance only when they forgot to log a transaction, which was now easy to notice because the Total Actual row lagged visibly.
Beginner pitfall. Typing category names in Transactions instead of using a dropdown. "Groceries", "groceries", "Grocerys", "Grocery" all become separate SUMIFS groups, leaving three categories under-reported and one non-existent category secretly carrying the spend. Always back the Category column with a Data Validation list pointing at Inputs.
Why does a simple budget spreadsheet last longer?
A budget that is easy to update will usually outperform a fancy budget that nobody keeps current after two weeks. Every entry is a small chore, so the time it takes matters: a budget that takes 2 minutes per entry tends to die in week three, while one that takes 10 seconds can last for years.
Optimise for: (a) dropdown-driven category selection so you never type a category name, (b) mobile-friendly entry — save the workbook to OneDrive and use the Excel mobile app to log transactions from your bank-transaction-notification, (c) a single "Month cushion" number at the top of Summary so you can scan the health of the month in 5 seconds without mental arithmetic.
Worked example: small-business monthly budget
A small business tracks sales income, payroll, software, marketing, rent, and variable delivery costs. Assume 8 categories and budget of £10,000 planned spend for the month.
Inputs sheet
Category Planned
Payroll 5000
Rent 1200
Software 400
Marketing 1500
Shipping 600
Packaging 300
Accounting 400
Contingency 600
Transactions sheet (formatted as Excel Table, name: tblTransactions)
Date Category Amount Note
2026-04-01 Rent 1200 Apr lease
2026-04-02 Software 49 Figma
2026-04-02 Shipping 145 Royal Mail
2026-04-05 Payroll 5000 Salaries
2026-04-06 Marketing 300 Meta ads
...
Summary sheet formulas (C2 onwards, one row per category)
=XLOOKUP(A2, Inputs!A:A, Inputs!B:B, 0) // Planned
=SUMIFS(tblTransactions[Amount], tblTransactions[Category], A2) // Actual
=B2 - C2 // Remaining
With this setup, logging a transaction takes about 10 seconds: type date, pick category from dropdown, type amount, hit Enter. The Summary updates live. At the end of the month the Month Cushion row tells you whether you came in under or over.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowWhich formulas does a monthly budget spreadsheet use?
A monthly budget spreadsheet needs XLOOKUP (or VLOOKUP) for planned amounts, SUMIFS for actual spend and SUM for the totals, as listed below.
| Where | Formula | What it does |
|---|---|---|
| Summary B2 (Planned) | =XLOOKUP(A2, Inputs!A:A, Inputs!B:B, 0) | Pulls the planned amount for the row's category; the 0 is the if-not-found value (M365 / 2021) |
| Summary B2 (Excel 2019 fallback) | =VLOOKUP(A2, Inputs!A:B, 2, FALSE) | Pre-XLOOKUP equivalent; returns #N/A on miss instead of 0 |
| Summary C2 (Actual) | =SUMIFS(tblTransactions[Amount], tblTransactions[Category], A2) | Sums every transaction matching the category |
| Summary D2 (Remaining) | =B2 - C2 | Planned minus actual; goes red when negative |
| Summary Total Planned | =SUM(B2:B15) | Total budgeted spend for the month |
| Summary Total Actual | =SUM(C2:C15) | Total spent so far |
| Summary Month Cushion | =SUM(B2:B15) - SUM(C2:C15) | Single-number health check |
| Summary Actual (with Month filter) | =SUMIFS(tblTransactions[Amount], tblTransactions[Category], A2, tblTransactions[Month], $D$1) | For multi-month workbooks driven by a slicer or month dropdown |
| Conditional format (Remaining red) | =B2 < 0 | Relative reference, applied to the Remaining range |
| Audit (mistyped categories) | =COUNTIF(tblTransactions[Category], "Groceries") | Confirms a category name actually matches transaction rows |
Variations: customise for your situation
The three-sheet skeleton (Inputs + Transactions + Summary) stays the same. What changes is the categories on Inputs and the extra metric on Summary.
| Use case | Inputs categories | Extra Summary metric |
|---|---|---|
| Personal monthly budget | 8–12 across Fixed / Variable / Goals | Savings rate = Goals ÷ Income |
| Couple / household | Shared categories + a "Whose" column on Transactions | Split percentage per person |
| Freelancer cash flow | Income streams + Tax reserve + Business costs | Take-home after tax reserve |
| Small-business monthly | Revenue + Fixed + Variable + Cash reserve | Operating margin % |
| Student / fixed allowance | Course fees, Rent, Food, Transport, Fun money | Days until next allowance vs cushion |
| Multi-month yearly budget | Same categories, add a Month column on Transactions | Slicer-driven Month total in Summary |
For couples, replace the dropdown's source with a small "Categories" sheet so both partners share one list and can add categories without editing Inputs directly. For multi-month budgets, add a Month calculated column on Transactions (=TEXT([@Date], "yyyy-mm")) and let a Slicer or single-cell month picker drive the Summary's SUMIFS.
If you want to build these formulas with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.
Common mistakes
- Using categories nobody can remember to update — 28 categories becomes 3 categories in practice, with 25 stale rows.
- Mixing calculations directly into raw entry rows — one paste destroys the formula and you will not notice for weeks.
- Chasing polish (sparklines, icons, thematic colours) before the structure is stable — always make it work, then make it pretty.
- Starting a new workbook every month — keep one workbook per year with a Month column on the Transactions Table, and the Summary can filter by month via Slicer.
- Not backing up — save to OneDrive or Google Drive, not a local drive, so version history catches accidental overwrites.
Troubleshooting common budget spreadsheet errors
These are the five most common problems and exact fixes.
1. Summary shows zero for a category that clearly has transactions
Cause: category name mismatch between Transactions and Summary — "Groceries" vs "groceries" vs "Grocerys". SUMIFS is case-insensitive but space-and-spelling sensitive. Fix: add a Data Validation dropdown on the Category column in Transactions, sourced from Inputs. Any existing mistyped rows: select the column, Find & Replace ("Grocerys" → "Groceries"), then verify with =COUNTIF(tblTransactions[Category], "Groceries").
2. Summary formulas show #N/A after adding a new category
Cause: VLOOKUP cannot find the new category on the Inputs sheet. Fix: confirm the category exists on Inputs with exact spelling, then check the lookup range includes the new row (use Inputs!A:B for full column, not Inputs!A1:B10 for a fixed range). Prefer XLOOKUP if you have Excel 365 — it has clearer error handling.
3. Actual totals appear too high — you see a £0 transaction showing up
Cause: accidental blank row inside the Table, or a text-typed number that SUMIFS ignored. Fix: check the Table's Row Count in the status bar matches your visible rows; select the Amount column and convert any text-number with Paste Special → Multiply by 1.
4. Conditional formatting colours do not update when I change a number
Cause: formula-based conditional formatting was applied with an absolute reference (=$B$2 < 0) instead of a relative reference (=B2 < 0). Fix: select the Remaining column, Home → Conditional Formatting → Manage Rules → edit the rule to use a relative cell reference.
5. Slicer to filter by month is not updating the Summary
Cause: Summary formulas use plain SUMIFS on the whole Table — Slicers only filter pivot tables, not SUMIFS. Fix: either wrap SUMIFS with a Month criterion (=SUMIFS(tblTransactions[Amount], tblTransactions[Category], A2, tblTransactions[Month], $D$1)), or rebuild Summary as a pivot table off the Transactions Table so the Slicer filters natively.
Frequently Asked Questions
How do I build a monthly budget spreadsheet in Excel from scratch?
Create three areas in the workbook: an Inputs sheet for income and planned spend per category, a Transactions sheet (formatted as an Excel Table) for actual spend, and a Summary sheet that uses SUMIFS to roll actuals up by category and subtract from planned. Keep the Summary read-only — all edits happen on Inputs or Transactions. This pattern is maintainable for years because the three zones have separate purposes and can be updated independently.
What categories should I include in a monthly budget?
For personal budgets, start with 8-12 categories grouped into Fixed (rent, insurance, subscriptions, loan repayment), Variable (groceries, fuel, dining, transport), and Goals (savings, investment, debt payoff). For small business, use Income (revenue streams), Fixed Costs (rent, payroll, software, insurance), Variable Costs (delivery, supplies, marketing, transaction fees), and Cash Reserve. Fewer than 8 categories hides useful variance; more than 15 categories means many entries stay zero and the tracker loses signal.
How do I calculate remaining budget in Excel?
Use SUMIFS on the Transactions table to pull actuals by category, then subtract from the planned amount: =Planned - SUMIFS(tblTransactions[Amount], tblTransactions[Category], A2). Place this formula in the Summary sheet beside each category row. For the month-total cushion, use =SUM(PlannedColumn) - SUM(ActualColumn) — the single most important number on the sheet.
Should I use a template or build from scratch?
Build from scratch if you have never built a spreadsheet before — a template you do not understand will break silently and you will not know why. Use a template if you have built at least three spreadsheets and want to save time on layout. The 'from scratch' approach in this tutorial takes about 45 minutes and produces a budget you fully understand and can adapt, which matters more than polish for something you will maintain for years.
Why does my budget spreadsheet stop getting updated after a few weeks?
Three root causes: (1) too many categories so each entry takes decision-time you do not have; (2) entry and summary are mixed, so one bad paste breaks formulas and you abandon the sheet; (3) no mobile or quick-entry path, so transactions pile up unlogged. Fix by cutting to 10 categories maximum, enforcing the Inputs/Transactions/Summary separation with Excel Tables and sheet protection, and saving the workbook to OneDrive so the mobile Excel app can capture spend in 10 seconds.
When to use something else
If you need a more finance-specific planning model with forecasts and scenarios, financial modelling is the better fit. If you want loan planning specifically, an amortisation schedule handles principal-vs-interest splits. For multi-sheet dashboards once the budget is stable, see interactive dashboards.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowRelated tutorials
- Excel formulas and analysis hub — the cluster hub linking every Excel + AI tutorial on this site
- Excel Tables Best Practices — why the Transactions Table is the backbone of a maintainable budget
- How to Create a Dropdown List in Excel — the dropdown pattern that stops category typos
- How to Highlight an Entire Row in Excel Based on a Cell Value — traffic-light formatting for the Remaining column
- 15 Excel Formulas That Save Hours of Manual Work — SUMIFS, VLOOKUP, and related functions used above
- How to Build a Financial Model in Excel (Step by Step) — when you outgrow a simple budget and need forecasting
- Excel Loan Amortization Schedule With Extra Payments — loan-specific companion to the budget sheet
- How to Create a Calendar in Excel That Updates Automatically Every Month — pair with the budget for date-driven planning
