How to How to Show Difference From in Pivot Table in Excel in Excel - Practical Guide

Learn how to display percentage or value differences in pivot tables to compare data against totals, previous periods, or parent items. This feature reveals performance gaps and trends instantly, enabling faster data-driven decisions without manual calculations.

Why This Matters

This skill lets you analyze performance variations and identify outliers quickly without creating helper columns. It's essential for business analysis, sales tracking, and variance reporting.

Prerequisites

•Basic understanding of pivot table creation and layout
•Data organized in a table format with headers
•Excel 2016 or later version installed

Step-by-Step Instructions

Create or open your pivot table

Create a new pivot table via Insert > Pivot Table or open an existing one. Ensure your values field is in the Values area and your data is organized in rows/columns.

Right-click the value field to modify

Click on any cell in your pivot table values area, then right-click and select Summarize Values By > (your chosen function). This prepares the field for showing differences.

Access Show Values As option

Right-click the same value field again and select Show Values As from the context menu. This opens options for displaying differences, percentages, and comparisons.

Choose difference calculation type

Select from options like % of Total, % of Row, % of Column, Difference From, or % Difference From depending on your comparison need.

Configure base item for comparison

If using Difference From or % Difference From, specify the base field and item to compare against (e.g., compare each month to January). Click OK to apply.

Alternative Methods

Using the PivotTable Field List

Access the PivotTable Field List panel, locate your value field, click the dropdown arrow next to it, and select Value Field Settings > Show Values As to configure differences directly.

Quick calculation via Analyze tab

With the pivot table selected, go to PivotTable Analyze > Fields, Items & Sets > Calculated Field to create custom difference formulas without Show Values As.

Tips & Tricks

✓Use % Difference From to highlight percentage changes (e.g., variance from budget) more intuitively than absolute values.
✓Compare against parent items to see how subcategories perform relative to their category total.
✓Refresh your pivot table after data changes to ensure difference calculations update automatically.
✓Format difference columns as percentages or decimals from the Format Cells menu for clarity.

Pro Tips

★Combine Show Values As with slicers to dynamically show differences filtered by specific criteria.
★Use Difference From with the (previous) option to track sequential changes across time periods automatically.
★Apply conditional formatting to difference columns to color-code positive and negative variances instantly.

Troubleshooting

Show Values As option is greyed out

Ensure your value field contains numeric data and is properly placed in the Values area of the pivot table. Text fields cannot show differences.

Differences show as #N/A or blank cells

Verify that the base item you selected in the Difference From configuration actually exists in your data. Check spelling and formatting match exactly.

Percentage differences display as decimals instead of percentages

Right-click the column header, select Format Cells, and choose Percentage format with desired decimal places.

Related Excel Formulas

IFERROR IF

Frequently Asked Questions

Can I show difference from multiple base items simultaneously?

No, each Show Values As calculation applies to one base item at a time. To compare multiple items, duplicate your value field and configure each with different base items.

What's the difference between Difference From and % Difference From?

Difference From shows absolute value changes (e.g., Sales = 500 vs base = 400, difference = 100). % Difference From shows percentage change (difference = 25%).

Does Show Values As work with calculated fields?

Yes, but calculated fields must contain numeric results. Create the calculated field first, then apply Show Values As to display differences.

Can I export pivot table differences to a formula-based table?

Yes, copy the pivot table and paste as values, then create formulas if you need dynamic calculations outside the pivot table.