Calculate Budget Variance in Dollars and Percent

Excel & Google Sheets

How it works

With budget in column A and actual in column B, actual minus budget gives the variance in dollars. Divide that by the budget — =(B2-A2)/A2 — and format as a percentage to see how big the miss is relative to what you planned. The sign tells a different story depending on the line: for an expense line, a positive variance means you overspent; for a revenue line, positive means you beat plan. That's why finance teams often label the column "Over/(Under)" rather than "Good/Bad" — the number is neutral, the interpretation depends on the line.

B2

The actual amount — what really happened.

-A2

Minus the budgeted amount. Positive = actual came in above budget.

(B2-A2)/A2

The same variance divided by budget — format as a percentage.

When to use it

Use this on any budget-vs-actual report: monthly department spend, project budgets, revenue plans, event costs. Pair the dollar variance with the percent so a $500 miss on a $1,000 line doesn't hide next to a $500 miss on a $100,000 line.

Common mistakes

• ✕Subtracting in the wrong order.

  ✓=A2-B2 flips every sign, so overspending looks like savings. Stick to actual minus budget (=B2-A2) and keep it consistent across the sheet.

• ✕Reading positive as good on every line.

  ✓Positive variance on an expense line means you spent MORE than planned. Only on revenue lines does positive mean you're ahead. Label the report so readers know which is which.

• ✕Dividing by actual instead of budget for the percent.

  ✓=(B2-A2)/B2 measures against what happened, not what you planned. Variance percent should use budget as the base: =(B2-A2)/A2.

• ✕A budget of zero breaks the percentage.

  ✓Dividing by a zero budget returns #DIV/0!. Guard it: =IF(A2=0,"",(B2-A2)/A2).