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

To calculate variance percentage, subtract the budget (or original) figure from the actual (or new) figure, divide by the budget, and multiply by 100: (actual − budget) ÷ budget × 100. A budget of 500,000 and an actual of 540,000 gives 40,000 ÷ 500,000 = 8%. In Excel: =(C2-B2)/B2, formatted as a percentage. If the budget is zero the percentage is undefined, so return “n/a” instead.

Try it on your own file → · Updated 10 October 2026

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.

The DataHub Pro Team · Analytics for SMEs
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.

How to calculate variance percentage by hand

You do not need Excel to calculate variance percentage — three steps on a calculator give the same answer:

1. Find the variance: actual minus budget. 72,000 − 60,000 = 12,000.
2. Divide by the budget: 12,000 ÷ 60,000 = 0.20.
3. Multiply by 100: 0.20 × 100 = 20% over budget.

The same calculation answers “percentage variance between two numbers” in general: use the figure you are measuring against (the budget, the forecast, last year) as the denominator. That is why variance percentage and percentage change are the same maths — percentage change simply uses the earlier period as the base.

Copy-ready variance percentage formulas

Budget in column B, actual in column C, one line per row. Each formula below is exactly what the Copy button puts on your clipboard.

1. The standard formula

Format as a percentage. Use this when every budget line is a positive number.

2. When the budget is zero

A percentage of zero is undefined, so there is no “right” number to show. This version returns 0% when both budget and actual are zero (nothing planned, nothing spent), and the label No budget when money was spent against a zero line — which is exactly the line a reviewer needs to see. Report the absolute variance from column D alongside it.

3. When the budget is negative

Budgeted losses, refunds and contra-revenue lines are negative, and the standard formula flips their sign. A budgeted loss of −10,000 that comes in at −5,000 is an improvement, but =(C2-B2)/B2 returns −50%. Dividing by ABS(B2) returns +50%, so a positive result still means “actual came in above budget” on every line.

4. The total row

Never average the percentage column. Using the three cost lines from the worked example (marketing, salaries, software): total budget 255,000, total actual 265,300, so costs are 4.0% over budget. The average of the three line percentages is 7.0%, because the small marketing line gets the same weight as the much larger salaries line. Sum first, then divide.

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