Should I use a template or build a budget spreadsheet 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.

Excel

How to Build a Monthly Budget Spreadsheet in Excel From Scratch

Q: 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.

Q: 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.

Q: 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.

Q: 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.

By Sagnik Bhattacharya 2 Mar 2026 4 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for building a monthly budget spreadsheet in Excel, with budget and cashflow visuals.

A budget spreadsheet only helps if it is simple enough to update honestly. The best budget sheet is not the one with the most tabs. It is the one that makes income, fixed costs, variable costs, and remaining cash easy to understand at a glance.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

That 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.

Quick answer

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.

Step 1 — Choose your categories

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.

Step 2 — Build the three-sheet layout

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: Category and Planned. 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 with Ctrl+T and name it tblTransactions. Add a data validation dropdown on the Category column 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.

Step 3 — Add the Summary formulas

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)

Step 4 — Lock in conditional formatting and protection

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 simplicity wins

A budget that is easy to update will usually outperform a fancy budget that nobody keeps current after two weeks. Research on habit formation shows new routines survive at about the 21-day mark only when the per-interaction cost is under 30 seconds. A budget that takes 2 minutes per entry dies in week three; a budget that takes 10 seconds lives 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 full formula reference

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.

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?

Use three sheets: Inputs (planned amounts per category), Transactions (Excel Table for actuals), and Summary (SUMIFS formulas pulling actuals by category). Protect Summary. Add a dropdown on the Transactions Category column. Setup takes about 45 minutes.

What categories should I include in a monthly budget?

For personal: 8-12 categories across Fixed / Variable / Goals. For small business: Income streams + Fixed Costs + Variable Costs + Cash Reserve. Fewer than 8 hides variance; more than 15 and many stay at zero.

How do I calculate remaining budget in Excel?

On the Summary sheet, =Planned - SUMIFS(tblTransactions[Amount], tblTransactions[Category], A2) beside each category, plus a total Month Cushion row at the bottom.

Should I use a template or build from scratch?

Build from scratch if you have never built a spreadsheet — a template you do not understand breaks silently. Use a template only after you have built at least three spreadsheets and understand the formulas inside.

Why does my budget spreadsheet stop getting updated after a few weeks?

Too many categories, mixed entry/summary cells, or no mobile quick-entry path. Cut to 10 categories, enforce three-zone separation with protection, save 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.