Pivot Table for Budget vs Actual Variance Analysis: A Symptom-Cause-Fix Guide

A budget vs actual variance analysis is a comparison between planned figures and realized figures for the same category and period, expressed as either a raw difference (Actual minus Budget) or a percentage difference (Variance divided by Budget), organized so that a reviewer can spot which line items are over, under, or on target at a glance. In a pivot table, this typically means two value fields — Budget and Actual — sitting side by side, with a third calculated field computing the gap between them.

That setup sounds straightforward, and for the first ten minutes it is. Then the variance column shows a number that doesn’t match a manual check, or a department vanishes from the report the moment a filter gets applied, or the percentage variance throws an error on a row where budget was zero. None of these are pivot table bugs. Each one has a specific, identifiable cause, and each has a specific fix. Below is a symptom-by-symptom walkthrough of the ones that come up most often.

Symptom: The variance column shows the wrong sign, or flips between reports

What you see: Overspending shows up as a positive number in one report and a negative number in another, or a “favorable” variance reads as negative when everyone expects favorable to mean positive.

Cause: There’s no universal convention for how variance is signed, and different calculated fields in the same workbook can quietly use different formulas. Actual - Budget treats overspending as positive. Budget - Actual treats overspending as negative. Both are legitimate depending on whether you’re building a spending report (where a positive number means “we spent more than planned”) or a revenue report (where a positive number means “we made more than planned”).

Fix: Pick one convention and document it in the field name itself rather than trusting people to remember it. Name the calculated field something explicit like Variance (Actual minus Budget) instead of just Variance, so the sign is self-explanatory even months later when someone else opens the file. To build it, go to PivotTable Analyze, then Fields, Items & Sets, then Calculated Field, and enter the formula exactly as it will be read: = Actual - Budget for spending, or = Actual - Budget reversed for revenue lines where under-target is the concern.

Symptom: Percentage variance shows #DIV/0! on certain rows

What you see: Most rows calculate a clean percentage variance, but a handful throw a division error, usually on line items that had no budget allocated at all.

Cause: Percentage variance is calculated as (Actual - Budget) / Budget, and dividing by zero is mathematically undefined. Any budget line that was left at zero — a new expense category that didn’t exist when the budget was drafted, for instance — will break this formula every time.

Fix: Wrap the calculated field in an IFERROR check: =IFERROR((Actual-Budget)/Budget,"N/A"). This won’t make the underlying ambiguity disappear — a brand-new line item with zero budget doesn’t have a meaningful percentage variance no matter how you calculate it — but it replaces a jarring error with a label that tells the reader exactly why the number is missing. If you’d rather see a number instead of text, some analysts substitute the actual amount itself in place of the percentage for zero-budget rows, since 100% of a nonzero actual against a zero budget isn’t really “infinite overspend,” it’s a category that needs a different comparison method entirely.

Symptom: A department or category disappears entirely after filtering

What you see: You filter to a specific quarter or region, and a line item that clearly has actuals recorded vanishes from the pivot table instead of showing as a variance.

Cause: This almost always means that category had budget or actual data in a different period, but zero in the one you just filtered to, combined with “Show items with no data” being switched off — the pivot table’s default behavior is to drop rows that have nothing to display for the current filter context, rather than showing them with a zero.

Fix: Right-click the row field, choose Field Settings, go to the Layout & Print tab, and check “Show items with no data.” This forces every category to remain visible across every filter state, showing zero rather than disappearing, which matters a great deal in variance reporting — a line item with a budget but no recorded spending is itself a finding worth surfacing, not a row that should quietly drop out of view.

Symptom: Grand totals don’t match a manual sum of the visible rows

What you see: You add up the variance column by hand for the rows you can see, and the total at the bottom of the report doesn’t match.

Cause: Two separate things produce this, and they need different diagnoses. First, check whether any rows are hidden by a filter but still counted in the total — pivot table subtotals sometimes include filtered-out detail depending on how the source data and slicers are configured. Second, and more commonly, check whether the calculated field is being summed correctly. A calculated field defined as Actual - Budget computes correctly at the total level only because subtraction is associative — sum of actuals minus sum of budgets equals sum of the differences. A calculated field involving division, like percentage variance, does not behave this way. The grand total for a percentage variance field is not the average of the individual percentages; Excel recalculates it as (Total Actual - Total Budget) / Total Budget, which can look wildly different from what you’d expect if you mentally averaged the visible rows.

Fix: For additive variance (the raw dollar difference), trust the grand total — it’s built correctly. For percentage variance, treat the grand total as its own separate calculation rather than an average of the rows above it, and label it accordingly so nobody assumes it was computed the same way. If a true weighted average is what’s needed instead, that’s a case for a DAX measure in the Data Model rather than a standard calculated field.

Symptom: The variance updates instantly for one month but lags behind for others

What you see: Refresh the pivot table, and the current month’s variance reflects the latest actuals, but a prior month still shows an old, out-of-date figure.

Cause: This is a source data problem, not a pivot table setting. It typically means the actuals feed for prior periods is being appended to rather than overwritten — old snapshot rows from a previous data pull are sitting in the source table alongside newer ones for the same period, and the pivot table is summing all of them together instead of just the latest version.

Fix: Check your source data for duplicate period entries before touching the pivot table at all. If actuals get re-pulled weekly, make sure each pull replaces the prior week’s rows for that period rather than adding to them — a Power Query step that removes duplicates based on period and category, keeping only the most recent load timestamp, solves this cleanly and keeps the fix in the data layer where it belongs, rather than trying to compensate for it inside the pivot table itself.

Symptom: Variance looks fine for expense categories but nonsensical for revenue lines

What you see: The same Actual - Budget calculated field produces variance numbers that read backwards for revenue — a positive number on a revenue line that came in under target, when the report is meant to flag underperformance as a negative.

Cause: Expense and revenue variance are conceptually inverted. For an expense line, spending more than budgeted is unfavorable (bad), so Actual - Budget being positive correctly signals a problem. For a revenue line, earning more than budgeted is favorable (good), so the same formula produces a positive number for a situation that’s actually good news, in the opposite sense of how the expense side reads.

Fix: Build two separate calculated fields rather than forcing one formula to serve both purposes: one for expense-type rows using Actual - Budget where positive means unfavorable, and one for revenue-type rows using Budget - Actual inverted, or equivalently, a “Favorable/Unfavorable” flag layered on top rather than relying on sign alone. Many finance teams solve this with a helper column in the source data that tags each row as Expense or Revenue, then use that tag inside a single calculated field with an IF statement, so the sign convention adjusts automatically based on the row type instead of requiring two parallel reports.

Symptom: Conditional formatting highlights the wrong rows as “over budget”

What you see: You’ve applied color scales or icon sets to flag variance, but rows that are clearly fine get flagged red, or rows with real overspending stay unhighlighted.

Cause: Conditional formatting rules in a pivot table are usually applied to a fixed range or based on the values present the first time the rule was set up. When the pivot table’s layout shifts — new rows appear, categories get added, filters change which rows are visible — the formatting rule can end up pointing at the wrong cells, or fail to expand to cover new rows entirely.

Fix: Apply conditional formatting using the “All cells showing [field name] values for [row field]” option rather than a fixed cell range. This option appears in the small dropdown that pops up right after you first set a conditional formatting rule inside a pivot table, and it ties the formatting to the field itself rather than a specific set of cells, so it follows the data even as the pivot table’s shape changes on refresh.

A Quick Reference for Variance Pivot Table Problems

Most variance reporting problems come down to one of two things: an inconsistent sign convention that nobody wrote down, or a calculated field that behaves differently at the total level than it does row by row. Check those two before assuming the source data itself is wrong — in a lot of cases, the numbers underneath were correct all along, and the pivot table was just displaying them under an assumption nobody stated out loud.