Excel Budget Template
Build an Excel budget template from scratch: structure your sheets, total spending by category with SUMIF, flag overspending with IF, and track averages.
Introduction
Downloaded budget templates almost always break the moment you add your own categories, because the formulas are hard-wired to a fixed layout. Building your own takes about twenty minutes and gives you a file you can actually extend. This guide constructs a two-sheet monthly budget driven by four functions — SUMIF, SUM, AVERAGE and IF — that works identically in Excel and Google Sheets.
Prerequisites
- Comfort entering formulas and filling them down
- Basic understanding of cell references and the $ symbol
- Excel 2010+ or any version of Google Sheets
1Set Up the Two-Sheet Structure
The single biggest mistake in home-made budgets is mixing raw data with summaries. Keep them apart. Sheet 1, named Transactions, is a flat log you only ever append to: A = Date, B = Category, C = Description, D = Amount. Sheet 2, named Budget, is the dashboard: A = Category, B = Monthly Budget, C = Actual, D = Variance, E = Status. Every figure on the Budget sheet is a formula pointing at Transactions — you never type a total by hand.
Create the category list
List each spending category once in column A of the Budget sheet, starting in A2 — Rent, Groceries, Utilities, Transport, Dining, Savings.
Budget!A2:A20Add a drop-down to the Transactions sheet
Select Transactions column B, then Data > Data Validation > List, and point the source at the Budget category list. This kills typos, which are the number one cause of a category totalling zero.
=Budget!$A$2:$A$20Format the log as a table
Select the Transactions headers and press Ctrl+T so new rows are picked up automatically. In Google Sheets, skip this and use whole-column references instead.
Ctrl+TExample
=COUNTA(Transactions!B2:B1000)A quick sanity check that counts how many transactions have been logged. Put it somewhere on the dashboard so you notice immediately if an import silently failed.
Enter expenses as positive numbers and income as a separate category — mixing signs makes every later formula harder to read.
Never insert blank rows inside the Transactions log; blank rows break table auto-expansion.
2Total Each Category with SUMIF
SUMIF is the engine of the whole template. It scans the category column of the log, finds every row matching the category on the current dashboard row, and adds up the matching amounts. Because the criteria is a relative reference to column A, one formula filled down handles every category.
Enter the actual-spend formula
Put this in C2 of the Budget sheet and fill it down alongside your category list.
=SUMIF(Transactions!$B:$B, $A2, Transactions!$D:$D)Restrict it to one month
Switch to SUMIFS when you want a specific month. B$1 holds the first day of that month, and EDATE finds the first day of the next one.
=SUMIFS(Transactions!$D:$D, Transactions!$B:$B, $A2, Transactions!$A:$A, ">="&B$1, Transactions!$A:$A, "<"&EDATE(B$1,1))Total everything
Add a grand-total row beneath the categories so budget and actual can be compared at a glance.
=SUM(C2:C20)Example
=SUMIF(Transactions!$B:$B, $A2, Transactions!$D:$D)Transactions!$B:$B is the range searched, $A2 is the category on this dashboard row, and Transactions!$D:$D holds the amounts actually added. Lock the two Transactions ranges with $ so they don't drift when you fill down.
SUMIF is identical in Excel and Google Sheets, including the optional third argument.
If a category returns 0 when you know spending exists, the culprit is almost always a trailing space in the logged category name — TRIM the column.
3Flag Overspending with IF
A number on its own doesn't prompt action; a label does. Calculate the variance, then use a nested IF to turn it into a three-state traffic light. Ordering the tests from strictest to loosest matters — IF stops at the first TRUE it meets.
Calculate the variance
Budget minus actual. A positive number means money left; a negative one means you are over.
=B2-C2Add the status flag
Place this in E2 and fill down.
=IF(C2>B2, "Over", IF(C2>B2*0.9, "Watch", "OK"))Guard against an empty budget
Wrap the test so categories with no budget set don't read as OK.
=IF(B2=0, "No budget set", IF(C2>B2, "Over", IF(C2>B2*0.9, "Watch", "OK")))Example
=IF(C2>B2, "Over", IF(C2>B2*0.9, "Watch", "OK"))With a 400 budget and 372 spent, the first test (372 > 400) is FALSE, but the second (372 > 360) is TRUE, so the row is flagged Watch before it tips over.
On Microsoft 365 and Google Sheets, IFS reads more cleanly than nested IFs: =IFS(C2>B2,"Over", C2>B2*0.9,"Watch", TRUE,"OK").
Apply conditional formatting to column E using the rule "Text is exactly: Over" so the flag colours itself.
4Track Averages and Spot Trends with AVERAGE
A single month tells you very little. Once you have three or more months side by side, AVERAGE turns the template from a record into a forecasting tool — and it is the fastest way to set realistic budgets for next quarter.
Find the typical transaction size
AVERAGEIF answers a different question: not what you spend per month, but what a single purchase in that category usually costs.
=AVERAGEIF(Transactions!$B:$B, $A2, Transactions!$D:$D)Set next month's budget from history
Round the three-month average up to the nearest 10 for a realistic target.
=CEILING(AVERAGE(C2:E2), 10)Calculate your savings rate
Income minus total spending, divided by income. Format the cell as a percentage.
=IF(B$25=0, "", (B$25-SUM($C$2:$C$20))/B$25)Example
=AVERAGE(C2:N2)Averages the twelve monthly actuals on one category row. AVERAGE ignores empty cells, so months you haven't filled in yet do not drag the number down — unlike dividing by 12 manually.
AVERAGE skips blanks but counts zeros — a logged 0 will pull the average down, so leave unused months empty.
In Google Sheets, SPARKLINE(C2:N2) drops a mini trend chart straight into the cell next to each category.
5Add a Running Balance and Keep It Maintainable
The final piece is a rolling balance on the Transactions sheet so you can see where the account stood after any given row, plus a few habits that stop the template rotting after three months.
Show the remaining budget for the month
Total budget minus total actual, in one cell on the dashboard.
=SUM(B2:B20)-SUM(C2:C20)Catch uncategorised rows
Any transaction whose category is not on the dashboard list is invisible to SUMIF. This flags them before they cost you.
=SUM(Transactions!$D:$D)-SUM($C$2:$C$20)Example
=$H$1-SUM($D$2:$D2)H1 holds the opening balance. The mixed reference $D$2:$D2 expands as the formula is filled down, so each row subtracts everything spent up to and including that row.
The uncategorised check should read 0. Anything else means a transaction is being silently excluded from every category total.
Keep one year per file. Twelve months of daily transactions stays fast; five years of whole-column SUMIFs will not.
In Google Sheets you can replace the entire dashboard with a single QUERY formula that groups by category — useful once your category list grows past twenty.
Functions Used
SUMIF
SUMIF adds up all numbers in a range that meet a specified condition. It's the go-to function for conditional summing — like totaling sales for a specific region or summing expenses above a threshold.
SUM
SUM adds all the numbers in a range of cells and returns the total.
AVERAGE
AVERAGE returns the arithmetic mean of a set of numbers.
IF
IF performs a logical test and returns one value if true and another if false.
Related Guides
SUMIF with a Date Range
SUMIF only takes one condition, so summing between two dates needs a trick. Learn three clean ways to SUMIF (or SUMIFS) over a date range in Excel and Google Sheets.
AVERAGEIF with Criteria
Learn how to use AVERAGEIF with criteria in Excel and Google Sheets: syntax, text, number and date conditions, wildcards, and when to switch to AVERAGEIFS.
Excel N/A Error Fix
The complete Excel N/A error fix guide: why #N/A appears in VLOOKUP and XLOOKUP, how to repair the root cause, and when to wrap it with IFNA or IFERROR.
Summary
A budget template that survives contact with real life keeps raw transactions separate from summaries. Log every expense on a flat Transactions sheet, then let the dashboard calculate itself: SUMIF totals each category, SUM produces grand totals and a running balance, IF converts variance into Over / Watch / OK flags, and AVERAGE turns several months of history into realistic targets for the next one. Add a drop-down for categories and an uncategorised-total check, and the file will still be accurate a year from now.
Next Steps
- Add a second month column and switch the actual-spend formula from SUMIF to SUMIFS with date bounds
- Build the uncategorised-total check so no transaction can slip past your category list
- Layer conditional formatting on the status column so Over rows turn red automatically