How to compare budget vs actuals in Excel
By the Analistable team · Updated · 6 min read
Get the budget and the actuals into the same shape — one row per department, account and month — using a mapping table if the accounting system's account codes differ from the budget's lines. Join them, then calculate variance = actual − budget and variance % = variance ÷ budget, and flag each line as favourable or adverse (for costs, spending less than budget is favourable).
Part of our guide: How to reconcile data in Excel
Map accounts to budget lines
| Ledger account | Budget line |
|---|---|
| 6100 Software subscriptions | Software |
| 6110 Cloud hosting | Software |
| 6200 Travel | Travel & events |
| 6210 Conferences | Travel & events |
Add the budget line to every actuals row with XLOOKUP on the mapping table, then sum actuals by department, budget line and month with SUMIFS or a pivot.
Calculate variances
Actual =SUMIFS(Actuals[Amount], Actuals[Dept], [@Dept], Actuals[Line], [@Line], Actuals[Month], [@Month])
Variance =[@Actual] - [@Budget]
Variance % =IF([@Budget] = 0, "n/a", [@Variance] / [@Budget])
Assessment =IF([@Type] = "Cost", IF([@Variance] <= 0, "Favourable", "Adverse"),
IF([@Variance] >= 0, "Favourable", "Adverse"))| Line | Type | Budget | Actual | Variance | Variance % | Assessment |
|---|---|---|---|---|---|---|
| Software | Cost | 2000 | 2340 | 340 | 17% | Adverse |
| Travel & events | Cost | 3500 | 1900 | -1600 | -46% | Favourable |
| Revenue | Income | 40000 | 43200 | 3200 | 8% | Favourable |
Focus on what matters
- Flag only variances above both a percentage and an amount threshold (for example >10% and >£500), so small lines don't drown out big ones.
- Separate timing variances (spend moved to next month) from permanent ones.
- Show year-to-date alongside the month — monthly noise often cancels out.
If the budget and actuals live in different files every month, build the join once in Power Query and refresh. Background: joining spreadsheets.
Year to date and forecast
YTD budget =SUMIFS(Budget[Amount], Budget[Line], [@Line], Budget[Month], "<=" & CurrentMonth)
YTD actual =SUMIFS(Actuals[Amount], Actuals[Line], [@Line], Actuals[Month], "<=" & CurrentMonth)
Full-year FC =[@[YTD actual]] + SUMIFS(Budget[Amount], Budget[Line], [@Line], Budget[Month], ">" & CurrentMonth)The simple forecast assumes the rest of the year lands on budget. Replace the remaining months with your latest forecast where you have one.
Getting both sides into the same shape
Budgets are usually built wide — one column per month — while accounting exports are long, with one row per transaction or per account and month. Turn the budget into the long shape before joining:
- Excel 365 / 2016 and later on Windows: load the budget into Power Query, select the line and department columns, and use Transform → Unpivot Other Columns. Rename the result to Month and Budget.
- Excel for Mac: recent versions of Excel for Microsoft 365 on Mac include Power Query; if yours doesn't, build the long table with a formula or a PivotTable on the actuals instead of reshaping the budget.
- Google Sheets: one way is
=FLATTEN()combined withSPLIT, but for a one-off it is often quicker to copy each month's column under the previous one with a Month label.
Make the month a real date (the first of each month) on both sides. Text labels such as “Sep-26” and “September 2026” look the same to a person and never match in a lookup.
Second example: a timing variance
Travel & events is £1,600 under budget in September. The team explains that a conference budgeted for September takes place in October. Comparing the two months together shows whether it is really a saving:
| Month | Budget | Actual | Variance |
|---|---|---|---|
| September | 3500 | 1900 | -1600 |
| October | 1500 | 3300 | 1800 |
| Two months | 5000 | 5200 | 200 |
Over the two months the line is £200 over budget, not £1,600 under. Reporting year to date alongside the month makes this visible without anyone having to remember last month's explanation. Label timing variances in a Comment column so they are not treated as savings in a forecast.
The same report in SQL
A full outer join keeps lines that exist in only one table — budgeted lines with no spend, and spend on lines nobody budgeted for:
SELECT COALESCE(b.dept, a.dept) AS dept,
COALESCE(b.line, a.line) AS line,
COALESCE(b.month, a.month) AS month,
COALESCE(b.amount, 0) AS budget,
COALESCE(a.amount, 0) AS actual,
COALESCE(a.amount, 0) - COALESCE(b.amount, 0) AS variance
FROM budget b
FULL OUTER JOIN (
SELECT dept, line, month, SUM(amount) AS amount
FROM actuals GROUP BY dept, line, month
) a ON a.dept = b.dept AND a.line = b.line AND a.month = b.month
ORDER BY dept, line, month;Summing actuals before the join keeps one row per department, line and month. For the general pattern, see joining data in Python, R and SQL.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Actuals total lower than the trial balance | Accounts missing from the mapping table | List actuals rows where the mapped line is blank |
| Costs show as favourable when overspent | Costs exported as negative numbers | Flip the sign of cost accounts before comparing |
| A whole month is blank | Month stored as text on one side | Convert both to the first day of the month |
| Variances double after refresh | Actuals appended twice | Load from one folder query and remove duplicates by journal ID |
| Department totals right, lines wrong | Account mapped to two budget lines | Check the mapping table for duplicate accounts |
Checking the result
Before sending the report, check that total actuals for the month equal the profit and loss total in the accounting system, and total budget equals the approved budget for the month. If either is off, the problem is in the mapping or the import, not in the variances. Anything under 10% and £500 can stay in the table without comment.
Common mistakes
- Comparing a monthly budget phased evenly (one twelfth per month) with seasonal actuals, which creates variances every month that cancel out over the year.
- Including accruals in actuals for some departments but not others.
- Reporting variance percentages on very small budgets, where a £50 overspend shows as 250%.
- Changing the budget mid-year without keeping the original, so nobody can see what the variance was against.
If you reforecast, keep Budget and Forecast as two separate columns and report actuals against both.
Doing it in Google Sheets
Put the mapping table, the long budget and the actuals export on three tabs. XLOOKUP adds the budget line to each actuals row, and a pivot table with department and line in rows and month in columns gives actuals in the same layout as the budget. Then a simple subtraction sheet, or SUMIFS per cell, produces the variance grid. Conditional formatting rules for values above your threshold make the lines that need a comment stand out.
Sharing the report
Send each budget holder only their department's lines, with the variance, the threshold flag and an empty Comment column. Collect the comments into the master report before it goes to management, so every flagged variance arrives with an explanation attached.
Frequently asked questions
- How do I calculate budget vs actual variance in Excel?
- Variance = actual − budget, and variance % = variance ÷ budget. Decide favourable or adverse based on whether the line is a cost or income.
- What if the accounting codes don't match the budget lines?
- Create a mapping table from ledger account to budget line and add the budget line to the actuals with XLOOKUP before summing.
- How do I avoid dividing by a zero budget?
- Wrap the formula: =IF(Budget=0, "n/a", Variance/Budget).
- Should variance be actual minus budget or budget minus actual?
- Either works if you're consistent. Actual minus budget is common; then label each line favourable or adverse depending on whether it's a cost or income.
- How do I show budget vs actual in a chart?
- Use a clustered column chart per line, or a bar chart of variances sorted by size so the largest gaps stand out.