For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Variance analysis in Excel for budget reviews

FIN.E6 =(Actual - Budget) / ABS(Budget)
Variance analysis in Excel for budget reviews — Excel example preview

Budget vs Actual variance table with dollar delta, percentage variance, and favorable/unfavorable conditional formatting flags.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Intermediate

Financial Planning and Analysis: Budgeting and Forecasting in Excel

The Gen Z Analyst

Open on YouTube (opens in a new tab)
The Gen Z Analyst

Loading preview…

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, Amount
  • tblActual — 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:

  • IFERROR rather than IF(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:

  1. 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.
  2. Same check on the Actual side. Same failure, usually a new account code that appeared this month.
  3. 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.

=PRACTICE

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

  1. Put assumptions in one clearly marked area.
  2. Use consistent formulas across time periods.
  3. Add checks for totals, signs, and balance relationships.
  4. 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.