How to Build a Budget vs. Actual Report From a Spreadsheet (2026)
A budget vs. actual report compares what you planned to spend or earn against what actually happened, line by line. The variance is shown in both currency and percentage terms. Building one from spreadsheets means reconciling two files that almost never share the same account names, period columns or level of detail.
Most finance and operations teams produce this report monthly, and most of the time goes to the reconciliation rather than the analysis. The budget was written in a planning template last autumn. The actuals come out of an accounting system in a completely different shape. Nothing lines up.
This guide covers why the mismatch happens, three workarounds people use, and where each one stops scaling.
Why budget vs. actual takes so long in a spreadsheet
The two files never share the same account names
Budgets get written by humans in categories that make sense for planning: "Marketing," "Tooling," "Travel." Actuals come out of a general ledger in categories that make sense for bookkeeping. Think "6420 · Advertising," "6110 · Software Subscriptions," "6300 · Travel & Entertainment."
There is no automatic join between those two lists. Someone has to decide that account 6110 rolls up to Tooling, and that decision has to be reapplied every time a new account appears. A single unmapped account does not throw an error. It just quietly drops out of the comparison, and your variance total is off by exactly that amount.
The period columns never line up
Budgets are frequently annual figures divided by twelve, or quarterly totals with no monthly split. Actuals arrive monthly, sometimes with a partial period at the edge if the close has not finished.
That forces a choice nobody documents. Do you compare the month against one twelfth of the annual plan, or against a phased budget that accounts for seasonality? Both are defensible. They produce different variances, and two people running the same report will produce two different numbers if the choice is not written down.
The detail levels do not match
Budgets are usually set at a coarser level than actuals are recorded. You budgeted one line for "Software," and the ledger has fourteen vendors. Comparing them requires aggregating one side before the join, which means the mapping table is doing double duty as both a translator and a roll-up.
What this costs you
Two hours before any analysis starts. Mapping accounts, aligning periods and stacking the files consumes the bulk of the report's production time. The actual variance calculation takes minutes.
A number you cannot fully defend. When someone asks why marketing is 18% over, the honest answer is often "let me check whether that account is mapped correctly." Every unmapped line is a small credibility risk in a meeting.
A report nobody re-runs. Because the rebuild is expensive, budget vs. actual gets produced once a month and then goes stale. Mid-month course correction — the entire point of tracking variance — does not happen.
The workarounds people try
Option 1: XLOOKUP against a mapping table
Build a mapping sheet with ledger account on one side and budget category on the other. Then use XLOOKUP to pull the budget figure onto each actuals row. Its if_not_found argument is genuinely useful here: set it to flag unmapped accounts rather than returning an error.
This works well and is easy for a reviewer to follow. It relies on someone maintaining the mapping table, and new accounts appear every month.
Option 2: A pivot table on a stacked file
Stack budget and actual rows into one sheet with a "scenario" column marking each row. Then pivot with scenario across the columns and category down the rows. Add a calculated field for the variance.
This handles multiple periods cleanly and is fast to refresh. It requires that both sides already use the same category labels, so it works only after the mapping problem is solved elsewhere.
Option 3: Power Query merge
Load both files, apply the mapping as a merge step, group to the budget's level of detail, then pivot the scenario column. Every cleanup step is recorded and replays on refresh, so next month is genuinely a one-click rebuild.
This is the most durable option, and it is the right answer for a report that will run for years. The setup cost is real — several hours the first time, and a different interface to learn.
Where all three hit the same ceiling
All three produce a variance table. None of them tells you which variances are worth a conversation.
A 40% overspend on a small line is usually noise. A 6% overspend on your largest cost centre may be the only thing in the report that matters. Rank variances by materiality rather than by percentage. Spot that a category has drifted three months running. Notice that an overspend is timing rather than volume. Those are the judgments that make the report useful, and none of them is a formula.
How to build the report with Powerdrill Bloom
Step 1: Upload your spreadsheet
Upload the budget file and the actuals export together. Powerdrill Bloom profiles both, so the account names, period formats and detail levels on each side are visible before you attempt a join.
Step 2: Describe the report in natural language
Ask for it directly: match these ledger accounts to the budget categories, compare by month, and show variance in both currency and percentage. Ask which accounts failed to map, rather than discovering it later from a total that looks wrong. Then follow up — which categories have been over budget three months running, which variances are large enough to matter against their own size.
Step 3: Export the chart, report, or deck
Take out the variance table, or a waterfall chart showing what drove the gap. A written summary for the monthly review pack comes out of the same request.
Why this beats rebuilding the mapping every month
| Spreadsheet route | Powerdrill Bloom | |
|---|---|---|
| New ledger accounts | Update the mapping table by hand | Flagged at upload |
| Period alignment | Decided once, rarely documented | Stated in the request, visible later |
| Materiality ranking | Manual judgment per line | Ask for it |
| Mid-month re-run | Full rebuild | Upload the newer export |
The row that changes behaviour is the last one. When re-running the report costs minutes rather than hours, variance becomes something you check mid-month instead of explaining after the fact. That is the difference between a control and a postmortem.
Best practices
Write the phasing rule into the file. State explicitly whether the monthly budget is one twelfth of the annual figure or a seasonally phased plan. This is the single most common reason two people produce different variances.
Show currency and percentage together. Percentage alone overstates small lines; currency alone hides fast-growing ones. Reviewers need both to rank anything.
Report unmapped accounts as a visible line. Never let them fall out silently. An "unmapped" row with its own total makes the gap impossible to miss.
Separate timing from volume. An invoice landing a month early is not an overspend. Flagging timing differences separately stops the report from generating false alarms.
Keep prior months in the same file. A single month of variance carries much less information than three. Trend is what turns a variance table into a decision.
Conclusion
Building a budget vs. actual report from spreadsheets is a mapping problem wearing an analysis costume. XLOOKUP with a maintained mapping table, a pivot on a stacked file, or a Power Query merge will all get you a correct variance column. The Power Query route is worth the setup if the report will run for years.
What none of them shortens is the judgment step — deciding which variances deserve attention. If that is where your monthly close actually stalls, try Powerdrill Bloom on this month's two files. Related reading: our AI budget analysis tool page and the guide to turning PDF financial statements into charts. See also what a waterfall chart is, the standard way to show what drove a variance. For recurring expense workflows, see the expense report page.
Frequently asked questions
What is a budget vs. actual report?
It compares planned figures against recorded results for the same period, line by line. The difference is expressed in both currency and percentage. Finance and operations teams typically produce one monthly to track whether spending and revenue are tracking to plan.
How do I calculate variance in Excel?
Subtract budget from actual for the currency variance, then divide that difference by the budget figure for the percentage. For expense lines, a positive currency variance means overspending; for revenue lines it means outperformance, so label the direction clearly.
Should I compare against a monthly or annual budget?
Compare against whichever your budget was actually built on. If only an annual figure exists, dividing by twelve is acceptable for steady costs but misleading for seasonal ones. Document which rule you used inside the file.
How do I handle accounts that are not in my budget?
Give them a visible "unmapped" line with its own total rather than excluding them. A silently dropped account makes the variance total wrong by exactly its amount, and nobody catches it in review.
What chart works best for budget vs. actual?
A waterfall chart shows what drove the gap between planned and actual, bridging from one total to the other. A grouped bar chart works better when you need to compare many categories at once rather than explain a single total.