To show a variance between column items in an Excel PivotTable, right-click a value, choose Show Values As > Difference From, then select the field and base item to compare against. For separate Actual and Budget value columns, calculate the variance in the source data or use a suitable Data Model measure; Difference From compares items in a PivotTable field, not two separate value fields.
What a PivotTable variance means
A variance is the amount by which one value differs from a reference value. In a report, it might mean this year minus last year, this month minus the previous month, or actual sales minus budget. The calculation is usually subtraction, but the right PivotTable method depends on where the two values come from.
For example, suppose a PivotTable has Year across its columns and Sum of Sales as its value. You can show each year’s sales as a difference from the previous year. If the PivotTable instead has Sum of Actual and Sum of Budget as two value columns, those are separate value fields, so the Difference From command is not a direct Actual-minus-Budget calculation.
It also helps to distinguish an absolute variance from a percentage variance. An absolute variance is the difference in the original units, such as dollars or units sold. A percentage variance expresses that difference relative to a baseline. A value of 200 dollars may be significant against a baseline of 500 dollars but small against a baseline of 100,000 dollars.
Compare column items with Difference From
Use Difference From when the comparison values are items in a field placed in the PivotTable’s Columns area, such as years, months, regions, or scenarios. The field supplies the items being compared, and a base item supplies the reference. Excel displays each item’s value minus the base item’s value.
- Click a value cell in the PivotTable, such as a sales total.
- Right-click and choose Show Values As > Difference From. You can also open Value Field Settings and use the Show Values As tab.
- In Base field, choose the field whose column items you want to compare, such as Year.
- In Base item, choose a specific year or select (previous) to compare each item with the preceding item in the field’s order.
- Select OK. If you want to retain the original totals as well, add the same numeric field to Values a second time and apply the comparison to that copy.
For instance, if the Year items are 2023 and 2024 and sales are 80,000 and 92,000, respectively, choosing Year as the base field and (previous) as the base item produces a 2024 difference of 12,000. The first year has no preceding item in the displayed sequence, so its comparison is blank. The exact layout depends on the PivotTable’s fields and sort order.
When the field is in Rows rather than Columns, the same calculation can still be useful, but the result appears beside the row items. The location of a field does not change the subtraction; it changes how the report is laid out.
Show percentage variance as well
To compare relative change, use Show Values As > % Difference From instead of Difference From. Choose the same base field and base item. Excel calculates the change relative to the base value: the difference divided by the base value. In the example above, a rise from 80,000 to 92,000 is a difference of 12,000 and a percentage increase of 15%.
Percentage variance is useful when categories have very different sizes, but it needs careful interpretation. A change from 10 to 15 is 50%, while a change from 10,000 to 10,500 is 5%. The first change is larger in percentage terms; the second is larger in absolute terms. If the base value is zero or blank, a percentage comparison may be unavailable or not meaningful, so check the underlying totals rather than treating a blank or error as zero.
You can keep both views in one PivotTable by adding the value field more than once to Values. Leave one copy as the ordinary total, set another to Difference From, and set a third to % Difference From if you need all three. Give each copy a clear custom name, such as Sales, Change, and Change %, so readers can tell which measure they are viewing.
Compare separate Actual and Budget columns
If the source table has separate numeric fields named Actual and Budget, adding both to Values creates two value fields. Difference From is designed to compare items in a field, so selecting Actual or Budget as the base field will not turn those two measures into a direct variance. This is a common source of confusion: the displayed columns may sit next to each other, but their visual position does not make them comparable items to that command.
For a straightforward PivotTable, add a Variance column to the source data before creating or refreshing the PivotTable. For example, assume row 1 contains headers, column D is Actual, column E is Budget, and data begins on row 2. With Actual and Budget in D2 and E2, put this formula in F2, under a Variance header:
=D2-E2
If D2 is 1,200 and E2 is 1,000, the result is 200. Fill the formula down the source rows, include the Variance field in the PivotTable, and summarize it by Sum. This gives the total of the row-level differences. Confirm that the source rows represent the same units and comparison period; subtracting mismatched records can produce a mathematically valid but misleading total.
For a percentage variance helper column, use the same layout, with Actual in D2 and Budget in E2. The formula below returns the row’s difference divided by Budget:
=IFERROR((D2-E2)/E2,0)
With Actual 1,200 and Budget 1,000, it returns 20%. Format the source column as a percentage. The formula returns zero when Budget is zero or causes an error, so use that treatment only if zero is an appropriate reporting choice; otherwise, leave the result blank or flag the zero-budget records for review. When aggregating percentages, a simple sum or average may not represent the overall percentage variance. For a total-level percentage, calculate the difference between aggregated Actual and Budget and divide by aggregated Budget.
Use a scenario field for a more flexible layout
A long, or normalized, source layout can make scenario comparisons easier. Instead of separate Actual and Budget columns, each record has fields such as Product, Period, Scenario, and Amount. The Scenario field contains items such as Actual and Budget, while Amount holds the number. Put Scenario in Columns and Sum of Amount in Values; then use Show Values As > Difference From, with Scenario as the base field and Budget as the base item. The Actual column then shows Actual minus Budget.
This arrangement is useful when you expect to add more scenarios, such as Forecast or Revised Budget, because the PivotTable can display each scenario as an item in one field. It does require changing the source structure, and the difference is only meaningful when each scenario is recorded at a comparable level of detail. If the existing data is small and stable, a helper column may be simpler. For a recurring report with many scenarios, a normalized layout may be easier to extend and maintain.
Calculated fields, Data Model measures, and when to use them
Another option for a conventional PivotTable is a calculated field. In Excel for Microsoft 365 on Windows, select the PivotTable, then choose PivotTable Analyze > Fields, Items, & Sets > Calculated Field. A calculated field can define an expression using source fields, and the result can be added to the PivotTable. It can be convenient for straightforward calculations, but it is not the same as comparing displayed column items with Difference From.
Calculated fields are evaluated using the source records, then summarized in the PivotTable. That behavior matters: a percentage calculated for each record and then averaged is not necessarily the same as total variance divided by total budget. If you need a ratio of aggregated totals, a source helper column may not be the right aggregation approach. A Data Model measure can define a calculation at the PivotTable’s filter context and is often a better fit for advanced reports. Measures use Data Analysis Expressions (DAX), so their setup and formula rules differ from ordinary worksheet formulas.
Calculated fields are not available in the same way for PivotTables based on the Data Model or certain external sources. If the Calculated Field command is unavailable, check the PivotTable’s source and design. For a simple Actual-minus-Budget report, a source variance field is often the most transparent option. For a reusable model with complex filtering, use a measure designed for the model rather than trying to force a basic calculated field to do a different job.
Common problems and checks
- The command compares the wrong values: Confirm that Base field is the field containing the comparison items, not the name of a separate numeric value field. For year-over-year comparison, the base field is usually Year.
- The first item is blank: With (previous), the first item has no prior item to subtract. That is expected. Choose a specific base item if you want every column compared with one fixed period.
- Previous means the wrong period: Check the field’s sort order and labels. Text dates may sort alphabetically, and fiscal periods may not follow calendar order. Sort the items into the intended sequence before relying on (previous).
- The PivotTable does not include a new helper column: Refresh the PivotTable after adding the source field. If the original source range did not expand to include the new column or rows, adjust the source or use an Excel Table as the source, then refresh.
- Totals do not match expectations: Verify the value field’s summary setting, filters, hidden items, and source duplicates. A PivotTable may summarize a field by Count rather than Sum if the source contains text or blanks that affect how Excel recognizes the data.
- A percentage looks unexpectedly large: Check the base value and confirm whether the report should show a percentage change or percentage-point difference. These are different comparisons when the source values are already percentages.
Choosing the right method
Use Difference From for movement between items in one field, such as one year versus the previous year. Use % Difference From when the relative change is more informative than the raw amount. Use a source helper field when comparing separate measures such as Actual and Budget in a simple report. Use a Scenario field when the source can be organized as one amount column with several scenario items. Consider a Data Model measure when the report needs a calculation based on aggregated values, complex filters, or reusable model logic.
Before sharing the result, check the sign convention. Actual minus Budget makes positive values favorable only if exceeding budget is favorable in that context; for expenses, a positive variance may mean overspending. Label the measure explicitly, for example Actual − Budget, and state the baseline and period. Also decide how to handle missing values and zero baselines rather than letting readers guess what blanks mean.
Frequently Asked Questions
Can I calculate variance between two columns in a PivotTable?
Yes, but the method depends on what the columns represent. For items of one field, use Show Values As > Difference From. For separate value fields such as Actual and Budget, create a source variance field or an appropriate Data Model measure.
How do I compare this year with last year?
Place Year in Columns, add the measure to Values, and apply Show Values As > Difference From. Set Base field to Year and Base item to (previous), after checking that the year items are in chronological order.
How do I show variance as a percentage?
For field items, choose % Difference From and select the comparison field and base item. For separate Actual and Budget fields, calculate the percentage using the intended baseline, usually Budget, and decide how zero budgets should be displayed.
Why is the first column blank when I choose previous?
The first item has no earlier item in the sequence, so Excel has no value to subtract from it. A blank there is normal; choose a fixed base item if you want every period compared with the same reference.
Can I show the original values and the variance together?
Yes. Add the value field to Values again and apply the variance display to the second copy, leaving the first as the ordinary total. Rename the fields so the original amount and comparison result are clearly labeled.
Related
- Data Validation Reference Table in Excel
- Remove Duplicates in Power Query: A Practical Guide
- Power Query Basics: Import, Clean, and Transform Data
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Make a Trendline Solid in Excel and Fix DashesTo make a trendline solid in Excel for Microsoft 365 on Windows, select the trendline, press Ctrl+1, open…
- Excel Waterfall Chart: How to Show the Final TotalTo show a final total in an Excel waterfall chart, select the bar for that value, right-click it,…
- Add a Trendline to an Excel PivotChart (Workarounds)Excel for Microsoft 365 on Windows does not let you add a trendline directly to a PivotChart. Create…
- How to Make Charts Wider in Excel Without DistortionTo make a chart wider in Excel, select the chart and drag a middle sizing handle on its…
- Excel KPI Dashboard Templates: How to Choose and UseExcel KPI dashboard templates are prebuilt workbooks that turn performance data into a compact view of key measures,…
- Excel Histogram: How to Specify BinsYes. In Excel for Microsoft 365 on Windows, insert a histogram, right-click its horizontal axis, choose Format Axis,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.