Budget vs Actual Variance Analysis in Excel

Step 1: variance amount

Next to your Budget grid, build an identical Actual grid using the same SUMIFS-by-period pattern, pulling the actual figures instead of budgeted ones. Variance is simply Actual minus Budget:

=ActualCell-BudgetCell

For an expense line, a positive result means overspending. For a revenue line, the same positive result means good news — exceeding budget. That sign-meaning flip is worth stating explicitly, because applying one red/green color convention to both revenue and expense lines without adjusting for it is one of the most common mistakes building this report.

Step 2: variance %, not just variance amount

Variance percentage matters more than the raw amount, because it weighs a miss relative to the size of its budget. A ₹5,000 overspend on a ₹10,000 marketing budget is a serious 50% miss. The same ₹5,000 overspend on a ₹5,00,000 payroll budget is a rounding error.

=(ActualCell-BudgetCell)/BudgetCell

Step 3: a conditional-formatting rule that actually saves time

Instead of scanning every cell in a 60-cell grid manually, a single formula-based conditional formatting rule highlights only the exceptions worth a manager's attention:

=ABS(VariancePctCell)>0.15

ABS() strips the sign, so the rule catches large overspends and large underspends symmetrically with one condition.

A worked example

One department came in 22% over budget one month — a clear exception worth explaining at the review meeting. Another came in 3% over, immaterial and expected given a planned cost change. Variance %, not variance amount, is what correctly distinguishes “needs an explanation” from “within normal range.”

Mistakes that undermine the report

• —One color convention for both revenue and expense. Over-budget is bad for an expense line and good for a revenue line — the same red highlight means opposite things.
• —Reporting variance amount without variance %. A raw rupee figure misrepresents materiality without context of the base it’s measured against.
• —Treating 15% as a universal rule. It’s a starting threshold — set too low, it flags everything and becomes meaningless; too high, it misses real issues.