To show percent change in an Excel PivotTable, select a value cell, choose Show Values As > % Difference From, then set the Base field and Base item—usually the date field and (previous). Excel calculates each value’s change relative to the selected comparison value, so a move from 100 to 125 appears as 25%.
What percent difference means in a PivotTable
In Excel, % Difference From is a built-in way to compare each value in a PivotTable with another item in the same field. It calculates the relative change, not the raw numerical difference. The calculation is (current value minus base value) divided by the base value. A positive result means the value increased from the base; a negative result means it decreased.
For example, comparing February sales of 125 with January sales of 100 gives a difference of 25 and a relative change of 25%. Comparing 100 with a base of 125 gives a change of -20%. The direction matters: percent change is not symmetrical, because the denominator is the base value.
This feature is useful for monthly sales, year-over-year revenue, units sold by quarter, or any PivotTable where readers need to see movement rather than just totals. It does not alter the source data. It changes how a value field is displayed in the PivotTable.
Set up percent difference in Excel
These steps are for Excel for Microsoft 365 on Windows. First, arrange the PivotTable so the periods or categories you want to compare appear as items in a row or column field, and add the measure—such as Sales—to Values.
- Click any numerical cell in the value field you want to compare.
- Right-click and choose Show Values As, then choose % Difference From.
- If Excel needs more information, open Value Field Settings for that value field and go to the Show Values As tab.
- Set Base field to the field containing the comparison items, such as Month or Year.
- Set Base item to (previous) for a comparison with the preceding item, or select a particular item such as January or 2025.
- Choose OK. If needed, format the result as a percentage using the Home tab’s Number controls.
You can also reach the settings by right-clicking a value and choosing Value Field Settings, then selecting % Difference From on the dialog’s Show Values As tab. The ribbon includes the contextual PivotTable Analyze tab when the PivotTable is selected, but the right-click route is often the quickest. On Excel for Mac, select the PivotTable and use its contextual PivotTable tab or the value field’s settings; dialog labels and placement can differ slightly by release.
Choose the right base field and item
The Base field identifies which PivotTable field contains the sequence or categories being compared. The Base item identifies the comparison point within that field. Choosing the wrong base is a common reason for numbers that look plausible but answer a different question.
- Month as the base field, (previous) as the base item: each month is compared with the preceding month in the displayed order.
- Year as the base field, (previous) as the base item: each year is compared with the preceding year.
- Month as the base field, January as the base item: each month is compared with January, rather than with the month immediately before it.
With date data, check that dates are sorted in chronological order and that the PivotTable’s grouping matches the question. If dates are grouped into years and months, choose the field that represents the interval you actually intend to compare. “Previous” follows the order of items in the PivotTable; it does not automatically know which period is meaningful to your analysis. A custom sort or a non-date category order can therefore produce an unexpected comparison.
Worked example: monthly sales change
Suppose a PivotTable has Month in Rows and Sum of Sales in Values. January totals 100, February 125, and March 100. Set the base field to Month and the base item to (previous). January has no preceding month in this displayed sequence, so it has no prior-month comparison. February is calculated against January: (125 − 100) ÷ 100 = 25%. March is calculated against February: (100 − 125) ÷ 125 = -20%.
The last figure is not -25%, even though sales fell by 25 units. The drop is 25 divided by the prior value of 125, which is 20%. This illustrates why a percentage change must always be interpreted with its base period in mind.
If you want to verify one comparison outside the PivotTable, with the old value in B2 (100) and the new value in C2 (125), enter =(C2-B2)/B2 in another cell and format it as a percentage. It returns 25%. This formula is a useful check, but it will not automatically adapt to PivotTable items the way the built-in display calculation does.
What the PivotTable compares and totals mean
Percent difference is calculated from the value field’s summarized results. If the source contains many transactions, Excel first aggregates them according to the PivotTable layout—for example, it sums each month’s sales—and then compares those monthly totals. It does not calculate a percentage change for every source row and average those percentages.
This distinction matters when a PivotTable includes categories such as product or region. If Month is in Rows and Product is in Columns, each product’s February value is compared with that product’s January value. If you add Region as another grouping field, the comparison is made within the relevant PivotTable context. A grand total is based on the aggregated total for each period; it is not necessarily the average of the displayed product or region percentages.
When there are multiple value fields, apply the setting to the intended field. For example, units and revenue can both be in Values, but their percent changes answer separate questions. Clear field names such as Sales and Units Sold help prevent a percentage from being mistaken for a raw amount.
Format and interpret the result carefully
Excel generally presents the result as a percentage, but the number format is worth checking. Select a result and use Home > Number to set Percentage and choose an appropriate number of decimal places. One decimal place may be helpful for small changes; whole percentages can make a compact report easier to scan. Formatting changes display precision, not the underlying calculation.
A percent change is different from a percentage-point change. If a conversion rate rises from 10% to 12%, that is an increase of 2 percentage points, but a 20% relative increase compared with the original 10%. A PivotTable’s % Difference From reports the relative change between the displayed values. It does not automatically report the percentage-point difference between rates.
Also distinguish percent difference from % Of or % of Grand Total. Those options show a value as a share of another value or of the total. For example, a region contributing 25% of annual sales is not the same as sales in that region growing 25% from last year.
Common problems and how to fix them
- The first period is blank: with (previous), the first displayed item has no earlier item to use as a base. This is expected. If you need a comparison against a period outside the displayed range, include that period in the PivotTable or choose a specific base item that is present.
- The comparison is against the wrong period: verify Base field and Base item, then check the order of the items. Sort dates chronologically and confirm that the date field is grouped as intended.
- A result is an error or cannot be calculated: a base value of zero makes the percent-change calculation undefined because it would require division by zero. Decide whether to show a dash, a note, or a separately explained measure for those cases; do not interpret an undefined change as zero percent.
- Values seem too large or small: confirm that the value field is summarized correctly—Sum, Count, or another calculation—and that the comparison is using the intended level, such as month rather than day. Then check whether the result is formatted as a percentage instead of a decimal or currency.
- New data is missing: refresh the PivotTable after changing its source data. Use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. If new rows fall outside the original source range, update the PivotTable’s data source or base the source on an Excel table that expands with added rows.
Built-in PivotTable setting or worksheet formula?
Use % Difference From when the comparison should follow the PivotTable’s categories and respond to filtering, grouping, and refreshes. It is quick to set up and keeps the relative-change calculation alongside the summarized values. You can change the base item without adding calculation columns to the source data.
Use a worksheet formula when you need a custom rule that the built-in display calculation does not express, such as special handling for a zero base, a comparison across nonadjacent periods, or a result placed outside the PivotTable. The trade-off is maintenance: formulas may need to be adjusted when the PivotTable changes shape. For a standard prior-period comparison, the PivotTable setting is usually clearer and less fragile.
Frequently Asked Questions
How do I show month-over-month percent change in an Excel PivotTable?
Put Month in Rows or Columns and the measure in Values. Right-click a value, choose Show Values As > % Difference From, then set Base field to Month and Base item to (previous). Check the month order before relying on the results.
How do I calculate year-over-year percentage change?
Arrange Year as the comparison field, then use % Difference From with Year as the Base field and (previous) as the Base item. The first year shown will not have a previous-year result unless an earlier year is included.
Why is the first percent difference blank?
There is no earlier item in the displayed sequence to act as the base when you choose (previous). A blank at the start is expected, not a missing calculation for a period that has a prior item.
Can I compare every month with January instead of the previous month?
Yes. In the Show Values As settings, use Month as the Base field and select January as the Base item. January must be available as an item in the PivotTable’s base field.
Does percent difference change my original data?
No. It is a PivotTable display calculation applied to summarized values. The source records remain unchanged, and refreshing the PivotTable updates its summaries from the source data.
Related
- Excel Number Format for Leading Zeros: How It Works
- Excel Number Format for Leading Zeros: A Practical Guide
- Excel Number Format: How to Show Zero as a Dash in Cells
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Build Clustered and Stacked Combo Charts in ExcelYes. In Excel for Microsoft 365 on Windows, use Insert > Insert Combo Chart > Create Custom Combo…
- Excel Pie Chart With Subcategories: How to Make OneTo show categories and their subcategories in Excel, select a table with one column for each level and…
- Think-Cell Waterfall Chart: How It Works and When to Use ItA think-cell waterfall chart is a PowerPoint chart that shows how a sequence of positive and negative changes…
- Power Pivot From Multiple Excel Files: How It WorksYes. In Excel for Microsoft 365 on Windows, use Power Query to import or combine data from multiple…
- Excel Pivot Chart: How to Remove a SeriesTo remove a series from an Excel PivotChart, select the chart, open PivotChart Analyze > Field List, and…
- How to Update Pivot Table Data in Google SheetsGoogle Sheets pivot tables update automatically when cells in their source range change. If you add rows or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.