Variance analysis in Excel for budget reviews

Budget vs Actual variance table with dollar delta, percentage variance, and favorable/unfavorable conditional formatting flags.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.

Financial Planning and Analysis: Budgeting and Forecasting in Excel
The Gen Z Analyst
Open on YouTube (opens in a new tab)

Financial Modeling and Valuation Analysis FREE COURSE
The Gen Z Analyst
Open on YouTube (opens in a new tab)Variance analysis explains the difference between what you expected and what actually happened. Most budget-vs-actual workbooks get the arithmetic right and the structure wrong: the numbers are typed in by hand each month, so the file has to be rebuilt every cycle, and nobody can trace a surprising number back to the transactions behind it.
This walks through a layout that survives the monthly rebuild — two flat data
tables, one summary driven by SUMIFS, and a small set of flags that stay
meaningful because they fire rarely.
Start with two flat tables, not a grid
The instinct is to build the report first: departments down the side, months across the top, budget and actual side by side. That grid is the output. If you also make it the input, every new month means inserting columns and repairing formulas.
Instead keep two tables, each one row per record, each formatted as a real Excel Table (Ctrl+T):
tblBudget— Period, Department, Account, AmounttblActual— Period, Department, Account, Amount
Both use the same Period, Department, and Account values. That matters more than
anything else in this article: if budget says Marketing and the ledger export
says MARKETING with a trailing space, every lookup silently returns zero. If
your actuals come out of an accounting system, clean them on the way in — the
bank statement cleanup article
covers the import side of that, and the CSV cleaner will
strip stray whitespace in the browser.
Why Tables rather than ranges? Because next month’s rows land inside the Table
automatically and every SUMIFS pointed at it picks them up without edits. That
is the whole argument in Excel Table vs normal range.
Pull both sides with SUMIFS
Now the summary sheet lists each Department/Account combination once, with the
period in a single input cell (say D2, named SelectedPeriod). Budget and
actual both come from the same pattern:
=SUMIFS(tblBudget[Amount],tblBudget[Period],$D$2,tblBudget[Department],$A5,tblBudget[Account],$B5)
=SUMIFS(tblActual[Amount],tblActual[Period],$D$2,tblActual[Department],$A5,tblActual[Account],$B5)
SUMIFS returns 0 rather than an error when nothing matches, which is exactly
what you want here — a department with no spend this month is genuinely zero,
not an error. (If you need to tell “zero” apart from “no rows at all”, add a
COUNTIFS with the same criteria and check it separately.)
Change D2 and the whole report moves to another month. Nothing is retyped.
Use both amount and percent
An amount variance shows scale. A percentage variance shows proportion. You need both, and they disagree often enough that reporting only one is misleading.
=Actual-Budget
=IFERROR(Variance/ABS(Budget),"")
Two details in that second formula earn their place:
IFERRORrather thanIF(Budget=0,...). It catches the divide-by-zero and any text that sneaks into the budget column from a bad import.ABS(Budget)in the denominator. Without it, a cost line budgeted at −10,000 that comes in at −12,000 reports a positive 20% variance, which reads as good news on a line that just overspent. Dividing by the absolute value keeps the sign of the variance meaning what it says.
Decide once, in writing, whether a positive variance means favourable or unfavourable, and put that sentence on the sheet. Revenue and cost lines pull in opposite directions and this is the single most common source of argument in a budget review.
Flag only what needs a decision
A flag is a request for someone’s attention, so it should be expensive. The useful rule is two-sided: a variance has to be large in percent and material in amount before it gets flagged.
=IF(AND(ABS(VariancePct)>0.1,ABS(Variance)>5000),"Review","")
A 40% overspend on a 200-unit line is noise. A 2% overspend on a two-million line is a real number. Requiring both conditions filters out both kinds of false alarm, and it means the Review column is usually mostly empty — which is what makes it worth scanning.
If you want the flag visible rather than textual, drive conditional formatting from that same column rather than writing a second set of thresholds into the formatting rules. The reasoning is in conditional formatting for KPI flags: one rule, one place to change it.
Add comments for material variances
Numbers show where to look; comments explain what happened. Add a Comment
column to the summary and require it for any row showing Review. A quick check
catches the ones people skipped:
=IF(AND(Flag="Review",Comment=""),"Explanation needed","")
Keep comments in a cell, not in a Note or a threaded comment. Cell text can be filtered, copied into the board pack, and carried forward to next month; floating notes cannot.
Separate price and volume when you can
A single total variance often hides its own cause. If revenue moved, the real question is whether it was price, volume, mix, or timing — and those have completely different responses.
Where you have both a unit count and a rate, split it:
Volume variance = (ActualUnits - BudgetUnits) * BudgetPrice
Price variance = (ActualPrice - BudgetPrice) * ActualUnits
Those two add back to the total variance, so you can show the split and still tie to the headline number. Even an approximate split changes the conversation from “revenue is down 8%” to “we sold the volume we planned at a lower price”, which points at a decision instead of a discussion.
Close with the checks that catch structural errors
Before the report goes out, three control totals catch nearly everything:
- Sum of the summary’s Budget column = sum of
tblBudget[Amount]filtered to the selected period. If they differ, a Department or Account value exists in the data but not on the summary sheet, and its money is invisible. - Same check on the Actual side. Same failure, usually a new account code that appeared this month.
- Sum of all variances = total actual − total budget. If this breaks, a formula in the variance column has been overwritten by a typed number.
That third check catches the single most common real-world failure in a budget file: someone pasted a value over a formula to “fix” a number and nobody noticed. For a broader pass before sharing, run the formula audit checklist.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
Go deeper with this skill
Build a model where assumptions, calculations, outputs, and checks are easy to trace. For this article, the goal is to practice: Compare actuals to budget with variance amount, variance percent, comments, and simple management flags — plus the SUMIFS layout that keeps the whole thing refreshable.
Practice workbook setup
Create a small model with assumptions, historical values, forecast rows, and one summary output.
Practice workflow
- Put assumptions in one clearly marked area.
- Use consistent formulas across time periods.
- Add checks for totals, signs, and balance relationships.
- Create a scenario area before changing base-case assumptions.
Quality checks
- Hard-coded assumptions are visually different from formulas.
- Formulas are consistent across rows and columns.
- Outputs reconcile to the supporting schedules.
Common mistakes
- Hiding assumptions inside formulas.
- Copying formulas across periods without checking references.
- Using formatting to imply precision the model does not really have.
Next actions
- Add sensitivity cases for the two assumptions that matter most.
- Create a model review checklist before sharing the workbook.
Formula focus: test SUMIFS(), IF(), IFERROR(), ABS() with normal rows, blank inputs, and at least one edge case.






