Budget vs Actual Report in Excel With Variance

A budget vs actual report in Excel compares what you planned to spend with what you really spent, line by line. The difference is called the variance, and it shows where money went off plan and by how much. This guide builds a one-month report with variance, variance %, totals and automatic red flags, for a team budget, a shop or a personal plan.

How the budget vs actual report is laid out#

Each row is one budget category. The columns run from the plan to the difference:

You type the budget and actual columns. Variance, variance % and the totals are formulas. If your budget lives in a separate plan, the monthly budget in Excel guide shows how to build it.

Step 1: Calculate the variance#

Variance is the actual amount minus the budget. In D2:

=C2-B2

Fill it down to D7 with Ctrl+D (Mac: ⌘D). Wages show 400: you spent 400 more than planned. Marketing shows −250: you spent 250 less.

Always use the same order, actual minus budget, on every row. Then a positive number always means "more than planned" and a negative number always means "less than planned". Mixing the order is the most common reason a variance report confuses people.

Step 2: Calculate variance %#

Variance % shows how big the difference is compared with the budget. A 400 overspend on 8,500 is small; 240 on 900 is not. In E2:

=IFERROR((C2-B2)/B2,"")

Format column E as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%), then add one decimal place. Supplies show 26.7% over budget and Travel 31.7% under.

This is the same formula as percent change: (new − old) ÷ old, with the budget as the "old" value. The brackets matter. Without them, Excel divides B2 by B2 first and gives the wrong answer. IFERROR handles a budget of 0, which would otherwise show #DIV/0!. The percentages lesson practises this formula step by step.

Step 3: Add totals with SUM#

SUM adds every number in a range. In B8:

=SUM(B2:B7)

Fill it right to D8. The total budget is 13,600, the total actual 13,855 and the total variance 255. The total variance should equal =SUM(C2:C7)-SUM(B2:B7), which is the exercise on this page. If the two don't match, a row's variance formula is missing or overwritten.

Don't SUM the variance % column. Percentages don't add up. Copy the variance % formula down to row 8 instead: =IFERROR((C8-B8)/B8,"") gives 1.9% for the whole budget. The SUM function guide covers adding ranges and whole columns.

Step 4: Label variances as favourable or unfavourable#

A favourable variance helps the result; an unfavourable one hurts it. For costs, spending less than planned is favourable. Add a label column in F2:

=IF(D2>0,"Over",IF(D2<0,"Under","On budget"))

Wages show Over, Marketing Under and Rent On budget. For income lines, the meaning flips: earning more than budget is favourable. If your report mixes income and costs, keep them in separate sections, each with its own total, and say at the top what a positive number means.

To show each category's share of total spending alongside the variance, see the guide to percentage of total in Excel.

Step 5: Highlight problems with conditional formatting#

Conditional formatting colors cells by rule, so readers see the problems before they read a single number.

Color overspent rows red:

1. Select A2:E7.
2. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =$D2>0 and pick a light red fill.

Flag only big overspends: many teams only care about variances over a threshold, such as 10%. Use =$E2>10% instead, and only Supplies and Utilities turn red. Small variances like Wages at 4.7% stay plain.

Icon sets: select E2:E7 and choose Home > Conditional Formatting > Icon Sets for arrows that show direction at a glance.

The $ before the column letter keeps every cell in the row looking at column D (or E). Practise conditional formatting to write rules like these with confidence.

Step 6: Extend the report to year to date#

A single month can be noisy. A year-to-date view (YTD: from the start of the year to now) shows whether a variance is a one-off or a trend.

• Put each month on its own sheet with the same layout, then add a YTD sheet with =SUM(Jan:Mar!C2) for each cell. This 3D reference adds C2 across every sheet from Jan to Mar.
• Or use one sheet with months across the columns and a YTD column that sums them.
• Add a comments column where the budget owner explains any variance over the threshold, such as "one-off equipment repair".

Budget vs actual reports are a regular part of retail and operations work. The article on Excel skills for retail jobs shows where they come up. For stock-heavy budgets, link the Supplies line to an inventory tracker.

Common mistakes in budget vs actual reports#

These slips make a report misleading:

1. Switching the order of actual and budget between rows.
2. Summing percentages in the total row instead of recalculating them.
3. Forgetting brackets in (C2-B2)/B2.
4. Calling every positive variance bad, even on income lines.
5. Typing totals by hand. Typed numbers don't update when an actual changes; SUM does.

Practise it#

Try the exercise on this page: total variance with two SUMs. Then practise percentages and percent change in a live spreadsheet for variance %, and practise conditional formatting so overspent lines color themselves.