How to calculate variance percentage in Excel (budget vs actual)

Variance percentage tells you how far each line came in above or below plan, in a form you can compare across a whole P&L. This guide gives you the exact Excel formula, the favourable-vs-unfavourable rule that trips up first-time analysts, the divide-by-zero guard, and a conditional-formatting setup that makes the exceptions jump off the page. Worked example throughout.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

1.What variance percentage tells you

Variance is simply the difference between what you planned and what happened. The absolute variance is in pounds; the percentage variance scales that gap to the size of the budget line, which is what makes lines comparable. A £5,000 overspend means very different things on a £10,000 line and a £500,000 line — 50% versus 1% — and only the percentage makes that obvious at a glance.

That is why every month-end variance report leads with the percentage column: it is the fastest way to spot which lines actually need a conversation. A finance review that scans down a tidy percentage column finds the two or three problems in seconds instead of squinting at raw pounds.

This page is the percentage-mechanics companion to the broader variance analysis in Excel walkthrough — here we focus on getting the formula, the sign, and the formatting exactly right.

2.Step 1 — Lay out budget and actual

Open Excel. Put the line item in column A, the budget in column B and the actual in column C. One row per line. Leave columns D and E for the variance and the percentage variance.

3.Step 2 — The absolute variance

First, the plain difference. In D2:

=C2-B2

Always do actual minus budget, not the other way round, and keep it consistent down the whole column. A positive result means actual landed above budget; a negative result means below. Whether “above” is good news comes in Step 4 — for now it is just the raw gap in pounds.

4.Step 3 — The percentage variance

Now scale that gap to the budget. In E2:

=(C2-B2)/B2

Format the cell as a percentage. For the revenue row above, =(540000-500000)/500000 returns 0.08 — actual came in 8% over budget. The denominator is always the budget, because the budget is the baseline you are measuring against; dividing by the actual instead is a classic and silent error.

Guard the divide-by-zero. If a budget line can be zero, wrap it:

=IF(B2=0, "n/a", (C2-B2)/B2)

Percentage variance from a zero budget is undefined — show the absolute variance in column D and mark the percentage as not applicable rather than letting #DIV/0! spread through your totals.

5.Step 4 — Favourable vs unfavourable

This is the part that catches everyone. A positive variance is not automatically good. It depends on the type of line:

So in the table above, revenue at +8% is good news, but marketing at +20% is an overspend — an unfavourable variance even though the percentage is positive. If you want Excel to label this for you, drive it off a column that marks each line as revenue or cost:

=IF(type="Revenue", IF(E2>=0,"Favourable","Unfavourable"),
                    IF(E2<=0,"Favourable","Unfavourable"))

Many teams sidestep the confusion by storing costs with a convention where underspend is always positive. Whatever you choose, document it — an undocumented sign convention is how variance reports get misread in board meetings.

6.Step 5 — Conditional formatting

A variance report is only useful if the exceptions are obvious. Select the percentage variance column (E2:E100), then Home → Conditional Formatting → New Rule.

Add a rule for material overruns: Format only cells that contain → Cell Value → greater than → 0.1, with a red fill. Add a second rule for the other direction: less than → -0.1, with an amber fill. Now any line more than 10% off budget — in either direction — lights up, and the reviewer’s eye goes straight to it. Colour scales and icon sets (the traffic-light arrows) work too if you prefer a heat-map feel; the same idea powers a full budget vs actual model in Excel.

Worked example

A monthly departmental report (illustrative figures), with the percentage variance and the favourable/unfavourable read already applied:

The percentage column does the triage instantly. Marketing at +20% is the one line that breaches a 10% threshold and needs an explanation; software is over budget too, but at +2% it is noise. Revenue beating plan by 8% is the headline good news. Without the percentage column you would be comparing a £12,000 marketing overrun against a £40,000 revenue beat as if they were the same scale — they are not.

Common mistakes

1. Dividing by the actual instead of the budget. The budget is the baseline. Dividing (C2-B2) by C2 gives a different, wrong percentage. The denominator is always column B.

2. Reading the sign without the account type. +15% is great on revenue and alarming on costs. Never present a variance percentage without making clear whether the line is income or expense.

3. Unguarded zero budgets. A single #DIV/0! can poison a SUM or an average further down. Wrap divisions in IF from the start.

4. Mixing signed conventions in one report. If some cost lines treat overspend as positive and others as negative, the totals are meaningless. Pick one convention and apply it everywhere.

The 30-second shortcut

Building variance columns by hand every month gets old fast. DataHub Pro computes budget-vs-actual variances — absolute and percentage — across every line as soon as you upload the two columns, and flags the exceptions automatically. No formula, no conditional-formatting setup.

Frequently asked questions