Cellwise Start free

Excel guide · 4 min read

How to calculate budget vs actual variance in Excel

Quick answerVariance is =D2-C2 (actual minus budget) and variance % is =(D2-C2)/ABS(C2). Using ABS on the budget keeps the sign right when a budget is negative. Wrap the percentage in IF(C2=0,...) for lines with no budget, and remember that a positive variance is good for income but bad for costs.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1LineTypeBudgetActual
2Product salesIncome4800051250
3ServicesIncome1250010900
4SalariesExpense2100021480
5RentExpense45004500
6SoftwareExpense18002340
7MarketingExpense35002860
8TravelExpense0640
9FX gain/lossIncome-500-1150

Download this sheet as an Excel practice file (.xlsx)

Variance: actual minus budget

Put the budget in C and the actual in D, then subtract. Actual minus budget is the most common convention: a positive number means you got or spent more than planned, a negative number means less. Product sales came in 3,250 above budget.

Pick one convention and say so in the column header ("Actual - Budget"). The opposite order, budget minus actual, is also used, and mixing the two in one workbook is how people end up reporting an overspend as a saving.

=D2-C2

Result: 3250

3,250: product sales beat the 48,000 budget by 3,250.

Variance %: divide by the budget

Variance % tells you how big the miss was relative to the plan. Divide the variance by the budget and format the cell as a percentage. A 3,250 beat on a 48,000 budget is 6.8%; the same 3,250 on a 4,500 rent line would be 72%.

Use ABS(C2) as the divisor rather than C2. For normal positive budgets it changes nothing, but it protects you from the negative-budget trap further down. Fill the formula down the whole column.

=(D2-C2)/ABS(C2)

Result: 0.067708333333 shows as 6.8% with Percent format

0.0677, which shows as 6.8% above budget.

Favorable or unfavorable

A positive variance is good news on an income line and bad news on a cost line. Software spend of 2,340 against 1,800 is +540, and that is an overspend. The Type column lets one formula label both kinds correctly.

The test (D6>C6)=(B6="Income") is TRUE when actual is higher on an income line or lower on an expense line, which is the favorable case. Rent, exactly on budget, shows "On budget" instead of being forced into one side. Add conditional formatting on the label for a quick red/green view.

=IF(D6=C6,"On budget",IF((D6>C6)=(B6="Income"),"Favorable","Unfavorable"))

Result: Unfavorable

Unfavorable: software cost 540 more than planned. The same formula gives Favorable for marketing (2,860 against 3,500).

A zero budget gives #DIV/0!

Travel had no budget but 640 was spent. Variance % divides by zero, so Excel returns #DIV/0!. The plain variance (640) is still correct; it is only the percentage that has no meaning, because any spend is an infinite percentage of nothing.

Test for zero explicitly and show a label. =IF(C8=0,"No budget",(D8-C8)/ABS(C8)) shows "No budget" here and the normal percentage everywhere else. You can use IFERROR((D8-C8)/ABS(C8),"No budget") instead, but IFERROR also hides genuine problems such as a #N/A pulled in by a broken lookup, so the IF test is safer in a report people rely on.

=(D8-C8)/ABS(C8)

Result: #DIV/0!

#DIV/0!, because the travel budget is 0. Wrap it in IF(C8=0,"No budget",...).

Negative budgets: why ABS matters

The FX line was budgeted at a 500 loss (-500) and came in at a 1,150 loss. That is 650 worse than planned. Dividing by the raw budget, (D9-C9)/C9, gives +130%, which looks like a strong beat because two negatives cancel out.

Dividing by ABS(C9) gives -130%, so the sign of the percentage always matches the sign of the variance. The same applies to any line that can be budgeted below zero: losses, refunds, write-offs or net transfers.

=(D9-C9)/ABS(C9)

Result: -1.3 shows as -130% with Percent format

-1.3, which shows as -130%: 650 below a -500 budget. Without ABS it shows +130%.

Totals, and showing favorable as positive

For a summary, total each type separately. Income and costs should not be netted into one variance % because a revenue beat would hide a cost overrun. SUMIFS on the Type column gives the actual and budget for expenses; subtract to get the variance.

Some finance teams prefer every favorable variance to be positive, whatever the line type. Use =IF(B2="Income",D2-C2,C2-D2): income keeps actual minus budget and costs flip to budget minus actual. Marketing then shows +640 (under budget) and software -540. Label the column "Favorable / (unfavorable)" so nobody reads it the other way.

=SUMIFS(D2:D9,B2:B9,"Expense")-SUMIFS(C2:C9,B2:B9,"Expense")

Result: 1020

1,020: total costs were 1,020 over budget (31,820 actual against 30,800), even though marketing came in under.

Common mistakes

Dividing by the actual instead of the budget

Variance % compares against the plan, so the budget is the divisor: (actual - budget) / ABS(budget).

Treating every positive variance as good

Positive is favorable for income and unfavorable for costs. Use a Type column and the Favorable/Unfavorable formula, or flip the sign for cost lines.

#DIV/0! across lines with no budget

Test for it with IF(C2=0,"No budget",...). Keep the plain variance column, which still works.

Netting income and cost variances in one total

A revenue beat can hide a cost overrun. Total income and expenses separately with SUMIFS on the Type column.

FAQ

What is the formula for variance % in Excel?

=(Actual-Budget)/ABS(Budget), for example =(D2-C2)/ABS(C2), formatted as a percentage. ABS keeps the sign correct when a budget is negative.

Is a negative variance good or bad?

It depends on the line. With actual minus budget, a negative variance on income means you earned less than planned (bad), while on a cost line it means you spent less (good). Label lines Favorable or Unfavorable so readers do not have to work it out.

How do I show variance as a percentage when the budget is zero?

There is no meaningful percentage of zero. Show a label instead with =IF(C2=0,"No budget",(D2-C2)/ABS(C2)), and report the variance in currency.

How do I do budget vs actual by month?

Keep one row per line and month (or a column per month), then total each month with SUMIFS on the month and type columns. The variance and variance % formulas are the same; only the ranges change.

Functions used

Related guides

Updated