How to Do Variance Analysis in Excel (Budget vs Actual That Explains Itself)

How-to · Updated July 2026 · ~11 min read

Your budget-vs-actual tab has a variance column, and every month somebody asks the same question in the review: "why is marketing up 18%?" Nobody knows, because the workbook computes differences and stops there. Half the percentage column reads #DIV/0! on the new accounts, and one cost centre that overspent badly shows a cheerful +20%. This post fixes all three with built-in Excel, and ends with a commentary column that answers the review question before it is asked.

One disambiguation first. People searching "excel analysis of variance" land on two unrelated things: the ANOVA hypothesis test, and budget-vs-actual variance analysis. ANOVA lives in the Analysis ToolPak (enable it under File > Options > Add-Ins, then run it from Data > Data Analysis), and Microsoft describes those tools as testing "the hypothesis that each sample is drawn from the same underlying probability distribution." VAR.S is statistical too — it "estimates variance based on a sample." Neither computes a budget variance. This article is the finance one, what Zoho Books calls "comparing your projected budget versus the actual performance of your business."

For a starting point, the P&L template has the shape already: a full statement down to net profit, with This period / Prior period / Variance / Var % columns where every total and variance is a live formula. Relabel the prior-period column as Budget.

Step 1: lay it out so the formulas can reach it

One row per account per period:

Type holds Revenue or Expense. It looks redundant; Step 3 explains why it is the most important column here. Build the header row out to J now — Steps 3 and 4 fill Verdict and Flag. Press Ctrl+T to make it a real Table, and use a Section column rather than blank separator rows, because gaps break Tables, PivotTables and Power Query at once.

Step 2: the two formulas, and the two ways the percentage breaks

Budget in C, Actual in D, row 2. The variance amount is additive, never errors, and aggregates correctly at every level:

=D2-C2

The percentage everyone writes first ruins the report, in two separate ways:

=(D2-C2)/C2

Failure one: a zero or blank budget. Microsoft states that a "#DIV/0! error occurs when a number is divided by zero (0)", and that it also occurs when the formula references a blank cell or one containing zero. Blanks are what bite: an account nobody budgeted for arrives from an export empty, not as a zero.

Failure two: a negative budget. Worse, because it doesn't error — it lies. Reproduce it in a cell:

Budget  -10,000
Actual  -12,000
Variance = -12,000 - (-10,000) = -2,000
-2,000 / -10,000 = +0.20

That reads +20%, an apparent outperformance, when spending got 2,000 worse — the two negatives cancelled. Any workbook carrying contra-revenue, credits or a negatively entered cost centre has this bug somewhere. Divide by the magnitude: ABS(number) returns "the number without its sign", so -2,000 / ABS(-10,000) = -0.20.

So the version to use puts the denominator itself in as the logical test, catching zero and blank at once because both evaluate FALSE:

=IF(C2,(D2-C2)/ABS(C2),"n/a")

Microsoft documents that shape verbatim — =IF(A3,A2/A3,0), =IF(A3,A2/A3,""), =IF(A3,A2/A3,"Input Needed").

Do not use =IFERROR((D2-C2)/C2,0) here. Returning 0 is a factual misstatement: 0% means "on budget", whereas the truth is "there is no budget, so a percentage is undefined". Microsoft also warns that IFERROR and IF(ISERROR()) suppress all errors, not just #DIV/0!, and that a formula should be verified as working before error handling is applied. Wrap the column in it and a #REF! from a deleted column, or a #VALUE! from text-stored actuals, becomes a clean-looking blank (fix those at source).

Step 3: favourable or unfavourable, and why the sign has to flip

Revenue 50,000 over budget is good news. Rent 50,000 over budget is very bad news. Both produce Actual - Budget = +50,000, so sort that column descending and your worst overspend sits next to your best sales month.

No vendor documents a rule here; this is accounting convention and you choose it. The usual one, worth adopting because it makes the column sortable: positive = favourable, negative = unfavourable, so expense lines get multiplied by -1. Build it off Type in F (Effect), compute G (Effect %) from the signed effect so the convention and the negative-denominator fix don't fight each other, then H (Verdict) for readers who don't read minus signs:

=IF([@Type]="Expense",-([@Actual]-[@Budget]),[@Actual]-[@Budget])

=IF([@Budget]=0,"n/a",[@Effect]/ABS([@Budget]))

=IF([@Budget]=0,"no budget",IF([@Effect]>0,"Favourable",IF([@Effect]<0,"Unfavourable","On budget")))

Keep the raw Variance in column E too, so you can tie back to the ledger, and document the convention in the workbook.

Step 4: flag only the variances that matter

A report where everything is highlighted is a report where nothing is. Use two tests, and set both numbers yourself — no standard exists. A percentage floor, so 3% wobbles aren't discussed, and an absolute floor, so 40% on a 200-unit stationery line doesn't outrank 4% on 900,000 of payroll. A row qualifies if it clears either. In I (Flag), with 1,000 and 5% as examples:

=IF(NOT(ISNUMBER([@[Effect %]])),"check budget",
   IF(OR(ABS([@Effect])>=1000,ABS([@[Effect %]])>=0.05),"Review",""))

Mind the shape: the ISNUMBER test has to sit outside the OR. Excel evaluates every argument rather than stopping early, so if Effect % holds the text "n/a", an ABS() on it inside the OR returns #VALUE! and takes the whole cell down. Type =OR(TRUE,ABS("x")) into a spare cell and watch it error.

Conditional formatting, the exact path

Select the Effect % column body, e.g. $G$2:$G$200. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, then New Rule. Under Select a Rule Type choose Use a formula to determine which cells to format, type the rule into the Format values where this formula is true box, then Format..., pick a fill, OK. Microsoft's constraint, verbatim: "You have to start the formula with an equal sign (=), and the formula must return a logical value of TRUE (1) or FALSE (0)."

Write each rule against the selection's top-left cell — column locked, row relative:

Unfavourable:  =AND(ISNUMBER($G2),$G2<-0.05)
Favourable:    =AND(ISNUMBER($G2),$G2>0.05)

ISNUMBER is not decoration: Excel ranks text above every number, so ="n/a">0.05 returns TRUE, and without the guard every unbudgeted account gets painted green as a star performer. Manage Rules on the same menu holds the Applies to range, the rule order and Stop If True.

Step 5: the PivotTable version

For subtotals by department and period, a PivotTable beats a formula grid. Then the decision most posts get backwards: calculated field or helper column? They fail in opposite places. For the variance amount both are fine, because it is additive — summing rows equals subtracting sums. For the percentage the helper column breaks: in Values it can only be Summed (adding percentages) or Averaged (an unweighted average of ratios, which is not the ratio of the totals).

A calculated field gets it right, for one documented reason: "Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula." That gives (SUM(Actual)-SUM(Budget))/SUM(Budget) at every subtotal and at the grand total, correctly weighted. Keep helper columns for row-level attributes you slice on.

It needs Budget and Actual as separate fields, so a wide source. Click inside the PivotTable, then the PivotTable Analyze tab (labelled Analyze in some versions) > Calculations group > Fields, Items, & Sets > Calculated Field. Type a Name, enter the Formula — click Insert Field rather than typing field names — then Add:

=Actual-Budget
=(Actual-Budget)/Budget

The documented limits bite: it cannot refer to totals, or use cell references, defined names or array functions, and it cannot be wrapped in IFERROR, because the expression is evaluated inside the PivotTable engine. So a zero budget at any level shows an error — filter those accounts out instead.

The no-formula alternative suits a tall source — Account, Period, Scenario, Amount — where Budget and Actual are two values in one field, so no calculated field can reach them. Microsoft's path: "In the PivotTable, right-click the value field, and then click Show Values As", then choose Difference From or % Difference From, with Base field set to Scenario and Base item to Budget. Drop Amount into Values twice, one copy set to each.

Step 6: budget in one file, actuals in another — merge with Power Query

FP&A owns the budget workbook, actuals come out of the accounting system, and pasting them together every month is the biggest time sink here. You want a Merge, which "creates a new query from two queries in a join operation" — not an Append, which stacks one query's rows after another's.

Load each source as its own query with a key column (Power Query for beginners if this is new); to reopen one, select a cell in the data, then Query > Edit. On the Home tab in the Combine group, click the arrow next to Merge Queries and choose Merge Queries as New, which creates a third query and leaves both sources intact. In the Merge dialog, pick the budget table and click its key column header, then the actuals table and its key column; Power Query shows the number of matches from a top set of rows, so read that before clicking OK — it is your reconciliation check. Column names need not be identical, but "matching columns must be the same data type, such as Text or Number": a code stored as text one side and a number the other returns zero matches with no warning.

The default Join Kind is an Inner join, which for variance reporting is wrong — it silently drops every account appearing on only one side, precisely the population you're hunting. Use a Left outer join with budget as the left table — the one you picked first — which "keeps all the rows from the primary table and brings in any matching rows from the related table", or a Full outer join, which "brings in all the rows from both the primary and related tables" and also surfaces actuals hitting unbudgeted accounts.

Then click the Expand icon on the new column named after your right table and tick the columns to bring through. Expect nulls: after an outer join, missing actuals land as 0 or blank, re-creating the divide-by-zero case, so Step 2's guard still earns its keep. (If the two sides spell account names differently rather than sharing a code, see fuzzy matching in Excel — the merge dialog's fuzzy option covers text columns only.)

Step 7: the commentary column that makes it explain itself

A variance report without commentary just relocates the question. In column J, write one sentence per flagged row covering what moved, by how much, why, and what happens next:

"Marketing 42,000 over budget (18%). Q3 trade show moved from September into August, so the spend landed a month early. September will come in under by roughly the same amount; full-year on plan."

That closes the item; "Marketing overspend, under investigation" does not. Keep it in the workbook rather than the covering email, and name a driver rather than a direction — price, volume, timing, one-off, mix, FX, error. Seed the numeric half:

=IF([@Flag]<>"Review","",
   "Actual "&TEXT([@Actual],"#,##0")&" vs budget "&TEXT([@Budget],"#,##0")&
   " ("&TEXT([@[Effect %]],"0.0%")&" "&LOWER([@Verdict])&") - ")

Because it runs only on flagged rows — the numeric ones — the TEXT calls are safe. Paste as values, finish each sentence by hand, and make the job somebody's: the close checklist template covers 20 tasks from cash through to reporting and close, with a status dropdown and a progress percentage (the post behind it covers sequencing).

Where HISAB 360 fits

Everything above is free and built in, and the workbook needs no add-in to keep working. What never gets easier is the monthly rebuild: re-exporting actuals, re-pointing the merge, repairing the grid after a bad paste.

HISAB 360 is a paid Excel add-in with an AI chat panel docked in the workbook, aimed at that loop. Ask it in plain English to write the guarded variance formulas down your table or build the PivotTable with its calculated field, and it applies the change to the live sheet rather than handing you text to copy. It also connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage with full read and write, so the actuals come from the ledger rather than a manual export.

The honest limits: Windows desktop Excel only, not Mac and not Excel on the web. Paid after a 15-day free trial of every feature plus 50 HISAB AI credits, no credit card, once per machine — there is no permanently free tier. Connecting an accounting system means authorising it yourself through OAuth, and any write back to a ledger should be reviewed before you approve it and reconciled afterwards. It does not email your variance pack on a schedule. Pricing is $7/month or $60/year to bring your own AI key, $10/month and $20/month for the hosted AI tiers, or $199 perpetual — see pricing and features.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.