Budget vs Actual in Excel — Variance Analysis with Favourable/Unfavourable Flags (2026)

A budget is a plan; actuals are reality. Variance analysis is where you compare the two and learn something. In 35 minutes you’ll build a budget vs actual report in Excel that calculates absolute and percentage variance, correctly labels each line favourable or unfavourable (respecting income vs expense), colour-codes the result, and rolls everything up to category and grand totals. Native formulas only — no add-ins.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is Budget vs Actual Variance Analysis?

Budget vs actual (also called variance analysis) compares what you planned to earn and spend against what actually happened, line by line. The gap between the two — the variance — is where the learning lives. A budget on its own is just a hope; comparing it to actuals turns it into a feedback loop that makes the next budget sharper and surfaces problems while they are still fixable.

Variance comes in two flavours that you always report together. Absolute variance is the raw difference in pounds — it tells you the size of the miss. Percentage variance expresses that miss relative to the budgeted amount — it tells you how significant the miss is. A £1,000 overspend is trivial on a £200,000 line (0.5%) but alarming on a £2,000 line (50%). You need both numbers to prioritise correctly.

The subtlety that trips up most home-made reports is direction. A positive variance is not automatically good. On an income line, beating budget (actual above plan) is favourable; on an expense line, the same positive variance means an overspend, which is unfavourable. A report that just shows the number, without interpreting its sign according to the line type, forces the reader to do that mental translation on every row — and they will get it wrong. This guide builds the type-aware logic that labels each line correctly and automatically.

Done well, a budget vs actual report is the single most useful management document a business produces each month. It answers three questions at a glance: where did we beat the plan, where did we miss, and how big was each gap? With colour-coding and totals, a manager can scan it in seconds and zero in on the two or three lines that actually need a conversation.

Before You Start

Budget and actuals must line up exactly

Every budget line needs a matching actual line for the same item and the same period. If your budget has “Marketing” but your actuals split it into “Ads” and “Events”, the comparison breaks. Agree a single chart of accounts (the list of line items) and use it for both budget and actuals.

Tag each line as income or expense

This single column is what makes the favourable/unfavourable logic work. Without it, Excel cannot know whether a positive variance is good news or bad. Add a Type column with exactly “Income” or “Expense” on every row.

Same period, same basis

Compare like with like: a monthly budget against a month of actuals, a full-year budget against a full year. If you are mid-month, compare against a pro-rated budget (budget × days elapsed / days in month) rather than the full month, or the variance will always look unfavourable simply because the month is not over.

1.Step 1 — Set Up the Budget vs Actual Table

Create these headers in row 1: Line Item, Category, Type, Budget, Actual. Enter one row per line item, fill in your budget and actual figures, and tag each row Income or Expense. Then click inside the data and press Ctrl+T to make it an Excel Table named Budget.

The Table pays off immediately: the variance formulas you add next will auto-fill down every row, structured references keep them readable, and the report grows automatically when you add a line. This is the foundation the rest of the report stands on.

2.Step 2 — Calculate Absolute Variance

Add a Variance column. Using the Actual-minus-Budget convention:

A positive number means actual exceeded budget; a negative means it fell short. Note that at this stage the number carries no judgement — +800 on rent and +5,000 on sales are both “positive” but mean opposite things. We add the judgement in Step 4. For now, this column gives you the raw size of every gap in pounds, which is the foundation of everything that follows.

3.Step 3 — Calculate Percentage Variance

Absolute pounds can mislead, so add a Variance % column that scales the gap against the budget:

Format the column as a percentage. The IFERROR wrapper is essential: if a line was budgeted at zero, the division would throw #DIV/0!, and one ugly error cell undermines the whole report. With the percentage in place you can instantly see that a 50% overspend on a small line may deserve more attention than a 2% overspend on a large one — or vice versa, depending on the absolute pounds. Reading both columns together is the skill.

4.Step 4 — Favourable vs Unfavourable

This is the step that turns numbers into meaning. Add a Verdict column whose logic depends on the line type:

Read it as two rules. For an income line, a variance of zero or above (you earned at least what you planned) is favourable. For an expense line, a variance of zero or below (you spent no more than planned) is favourable. Everything else is unfavourable. This single formula encodes the judgement that an experienced finance manager applies instinctively, and applies it consistently to every row — no manual interpretation, no mistakes.

5.Step 5 — Conditional Formatting

A verdict column is good; a colour is better. Select your data rows, go to Home → Conditional Formatting → New Rule → Use a formula, and add two rules referencing your Verdict column (assume it is column F):

The $F2 reference locks the column but not the row, so the rule colours the entire row based on its verdict. Now the report reads at a glance: green rows are fine, red rows need a conversation. For a subtler look, apply the colour only to the variance cells, or add an icon set (up/down arrows) driven by the variance sign.

6.Step 6 — Total and Subtotal by Category

Individual lines matter, but leaders want the roll-up. Total each column with SUM, and subtotal by category with SUMIF:

Repeat for the Budget column, then compute category variance and variance % on those subtotals exactly as you did per line. A small summary block — each category with its budget, actual, variance, and verdict — is often the part of the report executives actually read, with the line-level detail available beneath for anyone who wants to drill in.

7.Step 7 — Build a Variance Dashboard

Top it with three summary cards: Total Budget, Total Actual, and Net Variance (with its verdict). Then add a clustered column chart — select Line Item, Budget, and Actual and Insert → Clustered Column — so each item shows its budget bar beside its actual bar and the gaps are visible instantly.

If your table is in an Excel Table, drop in a slicer (Table Design → Insert Slicer) on Category or Department. One click filters the whole report to a single area, letting a department head see only their lines. For an executive view, chart the variance column alone — positive and negative bars in different colours — so the eye goes straight to the biggest divergences.

8.Step 8 — Track Variance Across Months

A single month tells you what happened; a trend tells you whether it is getting better or worse. Extend the model with a Budget and Actual column per month, plus a year-to-date block that sums the months elapsed. Add a cumulative YTD variance so a one-off blip is visibly different from a line that drifts further unfavourable every month.

The most actionable view pairs the current-month variance with the YTD variance side by side: a line can be on-budget this month yet badly over for the year (or vice versa), and only seeing both numbers tells you whether to act. Reviewing this every month and tackling the largest persistent unfavourable lines is one of the highest-leverage financial habits a business can build.

Try It: Budget vs Actual Variance Calculator

Edit the budget and actual figures below and watch the report compute each line’s variance, percentage, and verdict instantly — with the favourable/unfavourable logic respecting whether each line is income or expense, exactly as the Excel formulas do. Try pushing an expense above budget and an income below budget to see the colours flip.

Advanced Variance Tips

Pull actuals automatically with SUMIFS

Keep actuals as a transaction list and populate the Actual column with =SUMIFS(Txns[Amount],Txns[Category],[@Category],Txns[Month],$B$1). The variance report then updates itself the instant new transactions land — no monthly copy-paste.

Flag only material variances

Add a “materiality” rule so the report highlights only variances above a threshold — e.g. =AND(ABS([@Variance])>500,ABS([@[Variance %]])>0.1). This stops trivial £20 wobbles from cluttering the picture and focuses attention on the gaps that matter.

Add a forecast / re-forecast column

Mature budgeting adds a re-forecast: given the variance so far, what do you now expect for the full year? A simple version annualises year-to-date actuals; a better one lets you override individual lines. It turns a backward-looking report into a forward-looking one.

Automate the whole report

When monthly variance prep becomes a grind, DataHub Pro reads your budget and actuals from an uploaded file and builds a live variance dashboard with favourable/unfavourable flags, category roll-ups, and trends — shareable by link and refreshed automatically.

A Worked Example

Imagine a small agency’s month. Product Sales were budgeted at £60,000 and came in at £64,500 — a +£4,500 variance (+7.5%), favourable because more income is good. Service Revenue was budgeted at £25,000 but only reached £22,800 — a −£2,200 variance (−8.8%), unfavourable because the income fell short. So far the income lines net to a +£2,300 favourable swing.

On the cost side, Salaries landed exactly on budget (£32,000, zero variance, favourable by the “at or under” rule). Marketing was budgeted at £8,000 but spent £10,400 — a +£2,400 variance that is unfavourable for an expense line, because spending more than planned hurts profit. Software came in under at £2,650 against a £3,000 budget — a −£350 favourable variance. Notice how the same positive sign (+£2,400 on Marketing, +£4,500 on Sales) means opposite things depending on the line type. That is precisely the interpretation the Step 4 formula automates, and exactly what a raw “variance” column without a verdict leaves the reader to puzzle out.

The management story writes itself: revenue mix shifted from services toward products, and the marketing overspend needs a look — was it a deliberate push that drove the extra product sales, or an uncontrolled cost? A good variance report does not just compute the numbers; it frames exactly these questions. The interactive calculator above is loaded with this same example, so you can change any figure and watch the verdicts and totals respond.

Common Errors & Fixes

Every expense shows as favourable (or unfavourable)

The Type column text does not exactly match “Income” in the formula. Check for typos, plurals, or trailing spaces; apply Data Validation and re-select values from the list.

#DIV/0! in the variance percentage

A line was budgeted at zero. The IFERROR(...,"") wrapper from Step 3 handles it — make sure it is actually in the formula.

Totals don’t reconcile to the lines

A SUMIF criterion does not match the category text on some rows, or the SUM range misses a row. Confirm category spellings are consistent and the total range covers every detail line.

Conditional formatting colours the wrong rows

The rule’s reference is mixed up — usually a missing or extra $. For whole-row formatting the column must be locked and the row relative (=$F2="Unfavourable"), and the rule must apply to the full data range.