How to Do a Variance Analysis in Excel: Step by Step

To do a variance analysis in Excel, put budget and actual figures side by side for each line item. Subtract budget from actual to get the dollar variance. Divide that variance by the budget to get the percentage. Then flag each line as favorable or unfavorable and explain the biggest gaps.
This guide walks through the formulas, the worksheet layout, and a full worked example. It also covers how to chart the result and how to split a revenue gap into price and volume. Excel steps come from Microsoft's support pages read on November 10, 2026.
What a variance analysis shows
A variance is the gap between what you planned and what happened. A variance analysis lists those gaps line by line and explains the ones that matter.
Most teams compare actuals with one of three baselines:
- Budget. The plan set at the start of the year.
- Forecast. An updated plan set during the year.
- Prior period. Last month, last quarter, or the same period last year.
Microsoft's own Copilot skill for this task describes the job well. Its variance-analysis skill "Compares actual results with budgets, forecasts, or prior periods and explains differences by price, volume, mix, rate, or timing."
The output is usually a table plus a few written notes. The table shows every gap. The notes explain the three or four that leaders need to act on.
The core formulas
Three formulas do most of the work. Assume budget is in column B and actual is in column C.
| Measure | Formula | What it tells you |
|---|---|---|
| Dollar variance | =C2-B2 | How far actual is from budget, in currency |
| Percentage variance | =IFERROR((C2-B2)/ABS(B2),"") | How large the gap is relative to the plan |
| Status | =IF(D2=0,"On plan",IF(D2>0,"Above plan","Below plan")) | Which direction the line moved |
The ABS function keeps the sign correct when a budget is negative. IFERROR leaves the cell blank when the budget is zero, instead of showing an error.
Direction is not the same as good or bad. Revenue above plan is good news. Costs above plan are bad news. The next sections show how to handle that.
How to set up the worksheet
A clean layout makes the formulas easy to copy. Use one row per line item and these columns:
- Line item. Revenue, cost of goods sold, payroll, marketing, and so on.
- Type. Enter "Revenue" or "Cost" so Excel knows which direction is good.
- Budget. The planned amount for the period.
- Actual. The amount from your accounting export.
- Variance. Actual minus budget.
- Variance %. Variance divided by budget.
- Favorable or unfavorable. A label based on type and direction.
- Explanation. A short note for material lines.
Keep budget and actual for the same period and the same account mapping. A variance analysis is only as good as that match. If payroll is split into two accounts in the budget, split it the same way in actuals.
Format the table as an Excel table with Ctrl+T. New rows then pick up the formulas automatically.
How to calculate variances in Excel
With the layout in place, the steps are short. Assume the columns above start in row 2, with Type in B, Budget in C, and Actual in D.
- In E2, enter =D2-C2 for the dollar variance.
- In F2, enter =IFERROR((D2-C2)/ABS(C2),"") and format it as a percentage.
- In the Status column, enter the favorable test shown below.
- Copy the formulas down to every row.
- Add a total row, and calculate its variance from the totals, not by adding percentages.
Here is the favorable test for row 2 of the Status column:
=IF(E2=0,"On plan",IF(B2="Revenue",IF(E2>0,"Favorable","Unfavorable"),IF(E2<0,"Favorable","Unfavorable")))
It reads the Type column first. For revenue, a positive variance is favorable. For costs, a negative variance is favorable because you spent less than planned.
Percentages need care in the total row. Adding line percentages gives a wrong answer. Divide the total variance by the total budget instead.
How to flag the variances that matter
Not every gap needs a note. Most teams set a materiality rule, such as "over 5% and over $2,000." Lines that pass both tests get an explanation.
A simple formula marks them. Add a Material column and enter =AND(ABS(F2)>0.05,ABS(E2)>2000) in row 2. The cell shows TRUE for material lines.
Color makes the table faster to scan. Excel's conditional formatting can add icon sets. Microsoft says an icon set can "annotate and classify data into three to five categories separated by a threshold value."
To add one, select the Variance % column. Then go to Home > Conditional Formatting > Icon Sets. Microsoft notes that in the 3 Arrows set, "the green up arrow represents higher values." For cost lines, reverse the icon order so a rise shows as a warning.
How to explain the drivers
Numbers show where the gap is. Drivers explain why. Microsoft's skill description lists five common drivers: price, volume, mix, rate, and timing.
Price. You sold or bought at a different price than planned. Price variance = (actual price − budget price) × actual units.
Volume. You sold or used a different quantity. Volume variance = (actual units − budget units) × budget price.
Mix. The share of products, regions, or customers changed. A shift toward lower-margin items lowers profit even if volume holds.
Rate. A cost rate changed, such as an hourly wage or a supplier fee.
Timing. Money moved between periods. A March invoice paid in April shows as a gap in both months.
Price and volume together explain the full revenue gap. That check is worth doing every time, since it confirms the math.
A variance analysis example
Here is a worked example for a made-up company, Cedar and Pine Coffee. Its finance lead compares third-quarter actuals with budget.
| Line item | Type | Budget | Actual | Variance | Variance % | Status |
|---|---|---|---|---|---|---|
| Revenue | Revenue | $250,000 | $236,500 | −$13,500 | −5.4% | Unfavorable |
| Cost of goods sold | Cost | $100,000 | $97,200 | −$2,800 | −2.8% | Favorable |
| Payroll | Cost | $60,000 | $64,800 | $4,800 | 8.0% | Unfavorable |
| Marketing | Cost | $20,000 | $26,000 | $6,000 | 30.0% | Unfavorable |
| Rent | Cost | $15,000 | $15,000 | $0 | 0.0% | On plan |
| Operating profit | $55,000 | $33,500 | −$21,500 | −39.1% | Unfavorable |
The total checks out. Revenue lost $13,500, cost of goods sold saved $2,800, payroll cost $4,800 more, and marketing cost $6,000 more. Together that is −$21,500, which matches the profit gap.
Three lines pass the materiality rule of over 5% and over $2,000: revenue, payroll, and marketing. Cost of goods sold moved only 2.8%, so it gets no note.
The revenue gap needs a closer look. The budget assumed 50,000 units at $5.00. The quarter sold 43,000 units at $5.50.
- Volume variance. (43,000 − 50,000) × $5.00 = −$35,000.
- Price variance. ($5.50 − $5.00) × 43,000 = +$21,500.
- Total. −$35,000 + $21,500 = −$13,500, which matches the revenue line.
So the price increase held up, but it cost volume. The note to leaders says exactly that. Marketing spent 30% more than planned, yet units fell. That points to a question about which campaigns worked.
Payroll needs its own check. The $4,800 gap could be a rate driver, such as a wage rise, or a volume driver, such as extra hours. The finance lead asks the operations manager and learns that two stores added weekend shifts. That makes it a volume gap tied to opening hours, not a pay rise.
The final summary for leaders fits in one line. Profit came in $21,500 under budget, as a price rise cost more volume than it gained, and marketing and weekend shifts ran over plan.
How to write the variance notes
The table is only half of a variance analysis. Leaders read the notes, so each one should answer three questions: what moved, why it moved, and what happens next.
A good note follows a simple pattern:
- What. "Marketing spent $6,000 over budget, 30.0% above plan."
- Why. "Two paid campaigns ran longer than planned."
- Next. "We paused one campaign on October 3 and expect Q4 to land on plan."
Keep each note to two or three sentences. Use the same order of lines as the table, so readers can match notes to numbers.
Separate one-off gaps from trends. A one-time repair bill is different from a wage rise that will repeat every month. Mark repeating gaps clearly, since they change the forecast.
Write the notes with the owner of each line. The finance team sees the numbers, but the budget owner knows the reason. A short check-in with each owner turns a guess into an explanation.
Finally, add a one-line summary at the top. For the example above, it would read: "Q3 operating profit came in $21,500 under budget, mainly from lower volume and higher marketing spend."
Timing matters as much as wording. Most teams run a variance analysis every month, after the books close. A monthly rhythm catches problems while there is still time to act.
Quarterly reviews go deeper. They add the price and volume split, a reforecast, and a review of whether the budget itself still makes sense. Year-end reviews look at the full year against the original plan.
Keep the same template each time. When the columns and rules stay fixed, a variance analysis takes less time each month, and trends are easier to see across periods.
How to chart the variances
A chart helps leaders see the path from budget to actual. The best fit is a waterfall chart.
Microsoft says a waterfall chart "shows a running total as values are added or subtracted." It adds that "waterfall charts are also called bridge charts."
To build one, list the steps from budget profit to actual profit. Start with $55,000, then add each line's effect on profit, and end with $33,500. Select the data and choose Insert > Insert Waterfall > Waterfall.
Set the first and last bars as totals. Microsoft's page notes that the start and end columns "often start on the horizontal axis," while the middle bars float. Increases and decreases are color coded, so the biggest drivers stand out.
For month-by-month tracking, a simple column chart of variance % works too. Our guide on how to build a budget vs. actual report covers the full monthly report around this table.
Do a variance analysis with AI
The formulas take minutes once the table exists. The slow part is often mapping accounts, finding drivers, and writing the notes. An AI tool can draft those steps from your exports.
Here is the method in three steps, using Powerdrill Bloom.
Step 1: Upload your budget and actuals
Export the budget and the actuals from your accounting or planning tool. The AI Budget Analysis Tool accepts "Excel, CSV or PDF exports from accounting and FP&A."
Include unit and price data if you want a price and volume split. Keep account names the same in both files.
Step 2: Ask for the variances and drivers in natural language
Describe the output you need. For example: "Compare Q3 actuals with budget by line item. Flag lines over 5% and $2,000, and split the revenue gap into price and volume."
The tool page lists "Variance by line item with materiality thresholds applied" as one view. It also lists a reforecast workbook with "original budget, YTD actuals and revised year-end."
Step 3: Check the numbers and export the report
Compare totals with your own sheet before you share anything. Then export the result. The Budget Analysis page lists a "Variance dashboard with material lines highlighted and drivers attached."
For an ongoing view, the AI Finance Dashboard Builder covers P&L, cash, working capital, and forecast. Try Powerdrill Bloom with last quarter's budget and actuals.
Common mistakes to avoid
A few errors show up in almost every first variance analysis.
- Mixing up the sign. Decide once whether variance is actual minus budget, and use it everywhere.
- Calling every positive gap good. Higher costs are unfavorable even though the number is positive.
- Adding percentages. Recalculate total percentages from total dollars.
- Comparing different periods. A year-to-date actual against a monthly budget gives a false gap.
- Ignoring timing. A late invoice can create two opposite gaps in two months.
- Explaining everything. Notes on small gaps hide the ones that matter.
- Typing numbers by hand. Paste or link actuals from the export, so a typo does not create a false gap.
- Skipping the total check. Line variances should add up to the total variance. If they do not, a row is missing or mapped twice.
Frequently asked questions
What is the formula for variance in Excel?
For budget variance, use actual minus budget, such as =C2-B2. For the percentage, use =IFERROR((C2-B2)/ABS(B2),"") and format the cell as a percentage.
How do I know if a variance is favorable?
It depends on the line type. For revenue, actual above budget is favorable. For costs, actual below budget is favorable. A Type column and an IF formula can label each row.
What is a material variance?
It is a gap large enough to explain. Teams set their own rule, such as over 5% and over $2,000. Lines that pass both tests get a written note.
What chart is best for a variance analysis?
A waterfall chart, also called a bridge chart, shows how each line moves the total from budget to actual. Excel has it under Insert > Insert Waterfall.
Can Copilot in Excel do a variance analysis?
Microsoft lists a variance-analysis skill for Copilot in Excel. It compares actuals with budgets, forecasts, or prior periods and drafts explanations for review. It needs a Copilot license.
Sources: Microsoft Support, Use built-in skills with Copilot in Excel · Microsoft Support, Create a waterfall chart · Microsoft Support, Use conditional formatting to highlight information in Excel.