Budget vs Actual Variance

All versionsPercent

Every budget review asks the same two questions: how far off were we, and by what percent? Variance and variance % answer both — with a sign convention that makes “good” and “bad” obvious at a glance.

Quick formula: for budget in B2 and actual in C2:

=C2 - B2 // variance ($) =IFERROR((C2-B2)/B2, "") // variance (%)

For expenses, a positive variance means over budget; for revenue, positive means above plan. Decide your sign convention up front.

Functions used (tap for the full reference guide):

IFERROR TEXT

The example

An expense line: budget $5,000, actual $5,600.

	A	B	C
1	Line	Budget	Actual
2	Marketing	$5,000	$5,600
3	Variance	$600	+12%

The formula

Dollar and percent variance:

=C2 - B2 → 600 (over) =IFERROR((C2-B2)/B2, "") → 0.12 (12% over)

How it works

Two simple differences, with one guard:

Dollar variance is actual − budget. The sign tells the story once you fix a convention.
Percent variance divides that by the budget: (actual−budget)/budget. Format as a percentage.
Wrap the percent in IFERROR so a zero or blank budget doesn’t throw #DIV/0!.
Add a label for readability: =IF(C2>B2, "Over", "Under"), or use conditional formatting to color over/under.

Make “good” consistent. Over budget is bad for expenses but over plan is good for revenue. Many models store variance as “favorable/unfavorable” instead of raw sign — e.g. =IF(isExpense, B2-C2, C2-B2) so a positive number is always favorable.

Try it: interactive demo

Live demo

Enter budget and actual.

Budget

Actual

Variance ·

Variations

Favorable / unfavorable

Flip sign for expenses:

=IF($A$1="Expense", B2-C2, C2-B2)

Over / under label

Readable status:

=IF(C2>B2, "Over", "Under")

% of budget used

Run-rate view:

=IFERROR(C2/B2, "")

Pitfalls & errors

Guard the division. A zero or blank budget makes the percent #DIV/0!. Wrap with IFERROR.

Pick a sign convention and stick to it. Decide whether positive means over or favorable, and apply it consistently across the whole report.

Percent of a small base looks huge. A $50 miss on a $100 budget is 50%; the same miss on $50,000 is 0.1%. Show both dollars and percent.

Practice workbook

📊

Download the free Budget vs Actual Variance practice workbook

A budget-variance sheet with dollar/percent variance, favorable-sign and run-rate variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate budget variance in Excel?

Dollar variance is =actual-budget; percent variance is =(actual-budget)/budget, wrapped in IFERROR to avoid divide-by-zero. Format the percent as a percentage.

How do I make positive always mean 'good'?

Flip the sign for expense lines: =IF(isExpense, budget-actual, actual-budget) so a favorable result is always positive.

Why does a small line show a huge variance percent?

Percent variance divides by the budget, so a small base magnifies the percentage. Always report dollars alongside the percent.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: IFERROR · TEXT