A variance column full of #DIV/0! errors and misleading percentages helps nobody.
Variance is actual minus budget (or this period minus last). Percentage change divides the variance by the absolute value of the base, and returns a blank or a note when the base is zero. Decide whether positive means favourable for each line type, and flag lines over both an amount and a percentage threshold.
With actual in B2 and budget in C2:
=B2-C2
Use the same direction on every line of a report, and say which in the column heading ("Actual less budget").
The simple formula =(B2-C2)/C2 fails in two ways: it returns #DIV/0! when the base is zero, and it gives the wrong sign when the base is negative. A safer version:
=IF(C2=0,"n/a",(B2-C2)/ABS(C2))
Dividing by the absolute value keeps the sign of the percentage the same as the sign of the variance, so an improvement on a loss shows as positive. "n/a" is clearer than 0% or an error when there was no base to compare against.
For income, actual above budget is favourable. For costs, actual above budget is adverse. If your report mixes both, add a column showing the line type ("Income" or "Cost") and calculate:
=IF(D2="Cost",C2-B2,B2-C2)
Positive now always means favourable, and the report can colour every line the same way. Label it clearly, because readers used to "actual less budget" will otherwise misread it.
Thresholds based only on percentage flag tiny accounts; thresholds based only on amount miss big percentage swings in small accounts. Combine them, with the limits in cells named AmtLimit and PctLimit:
=IF(AND(ABS(B2-C2)>=AmtLimit,OR(C2=0,ABS(B2-C2)>=PctLimit*ABS(C2))),"Explain","")
Comparing the variance with the threshold times the base avoids dividing by zero, and lines with a zero base but a large amount are always flagged. Some teams prefer OR between the two tests instead of AND; choose one rule and write it on the report.
Format percentages with one decimal place and amounts without decimals in summary reports, so readers focus on the size of the movement. Conditional formatting can colour the flag column, but keep the word "Explain" in the cell too, so the report still works when printed in black and white.
For year-to-date variances, sum actuals and budget up to the current month first, then apply the same formulas. A month variance that reverses next month is timing; a year-to-date variance that keeps growing is a trend worth explaining.
An explanation should say why the number moved and whether it will continue: "Two months of software licences billed together; March will be lower" is useful. "Costs higher than budget" is not. See budget versus actual variance analysis and flux analysis.
The formulas are the same. UK reports often say "favourable" and "adverse" with F and A markers; US reports more often show variances with brackets for unfavourable amounts. Pick one convention and explain it in the report.
12 questions on how strong your close controls are. Free to use online.
The Month-End Close & Controls System tests the trial balance against the sum of reconciled balances, keeps every open item on one exception dashboard and blocks sign-off while exceptions remain open. Excel, no macros, one-time price.
Because dividing by a negative base flips the sign, so an improvement on a loss would show as a negative percentage.
Show n/a and explain the amount. A percentage of a zero base has no meaning.
Either works if it is consistent and labelled. Many reports use actual minus budget and colour by whether the line is income or cost.
Last updated 04 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.