How to Compare Actual vs Budget in Excel

Calculate dollar and percentage variances against budget, then total the overspends that a net variance hides.

You will learn to

• Calculate a dollar variance (actual − budget)
• Calculate a percentage variance
• Total only the line items that went over budget

How it works

Budget variance is actual minus budget. With costs, a positive variance means you spent more than planned (unfavourable) and a negative one means you came in under. The percentage variance, =(C4-B4)/B4, puts lines of very different sizes on the same scale: a $2,000 overspend matters more on a $9,500 travel budget than on an $84,000 payroll line.

A department’s net variance can look fine while individual lines are badly over, because underspends cancel out overspends. Total the overspends separately with =SUMPRODUCT((C2:C8>B2:B8)*(C2:C8-B2:B8)) to see the gross overrun.

=(C4-B4)/B4