To show percent change in an Excel PivotTable, add the value field to the Values area twice, then right-click the second value field and choose Show Values As > % Difference From. Select the field to compare and choose (previous) as the base item; Excel calculates the change between the summarized values, not between individual source rows.
What percent change means in a PivotTable
Percent change expresses how much a value rose or fell relative to an earlier value. The calculation is (current value − earlier value) ÷ earlier value. If sales rise from $100 to $120, the difference is $20 and the percent change is 20%. If they fall from $100 to $80, the change is −20%. A negative result therefore indicates a decrease; it does not mean that the underlying sales value is negative.
Excel’s % Difference From display calculation applies that comparison to PivotTable summaries. If a PivotTable sums sales by month, it compares one month’s total with the selected base month’s total. This is useful for month-over-month or year-over-year reporting, but it is distinct from calculating a percentage change for every individual transaction and then averaging those percentages.
Prepare the PivotTable and source data
Start with a consistent source table. For a sales comparison, useful columns include Date, Region, Product, and Sales. Make sure dates are real Excel dates rather than text that merely looks like a date, and check that the numeric measure contains numbers. Select the source data and choose Insert > PivotTable to create the report.
Put the time field in Rows or Columns, add the category fields you want to analyze, and put the measure—such as Sales—in Values. Before adding percent change, confirm that the value field is summarized as intended. For sales totals, it will usually say Sum of Sales; if Excel shows Count of Sales, check for text or blank entries in the source column and correct the data or change the summary setting.
A reliable comparison depends on a clear sequence of periods. Sort dates chronologically, not alphabetically, and decide whether the report should compare adjacent months, adjacent years, or another specific baseline. If the same month name appears under multiple years, use a layout that makes the intended year comparison explicit rather than relying on an ambiguous month sequence.
Show percent change from the previous period
- In the PivotTable Fields pane, drag the same numeric field into Values a second time. Excel creates a second copy, such as Sum of Sales2. Keeping the original lets readers see both the amount and the rate of change.
- Right-click a number in the second value field and choose Show Values As > % Difference From. Alternatively, open Value Field Settings for that value field and use the Show Values As tab.
- In Base field, choose the field that contains the comparison periods, such as Year or a chronological month field.
- In Base item, choose (previous) to compare each item with the preceding item in that field’s order. Choose (next) only when you intentionally want to compare against the following item.
- Select OK. Rename the second value field to something meaningful, such as Sales % Change, and format it as a percentage if needed.
These commands describe Excel for Microsoft 365 on Windows. In Excel for Mac, use the equivalent value-field settings or the right-click Show Values As menu; wording or placement can vary by release. Excel for the web has a more limited or changing interface, so if the required comparison option is missing, open the workbook in the desktop app.
Choose the right base field and item
The base field tells Excel which field supplies the items to compare. The base item identifies the reference item. For year-over-year change, a PivotTable with years as columns can use Year as the base field and (previous) as the base item. The value under 2025 is then compared with 2024, assuming those are the adjacent years in the displayed sequence.
For month-over-month change, use a field whose items represent individual months in chronological order. If months are represented only by labels such as Jan, Feb, and Mar, the sequence may repeat across years or sort unexpectedly. A unique month key—such as a date representing the first day of each month—helps make the sequence unambiguous. If you group dates into years and months, check the year boundary in the result: the chosen base field and PivotTable hierarchy determine what Excel treats as the previous item.
- Previous item: Best for adjacent periods in a correctly ordered series.
- A named item: Useful when every period should be compared with one fixed baseline, such as the first year in a report.
- A different field: Choose this when the comparison should follow a different dimension from the one shown in the row or column labels.
Do not assume that “previous” always means the previous calendar month or year. It means the previous item in the selected base field’s sequence. Inspect the labels and ordering, especially when periods are missing or dates have been grouped.
Read a concrete example
Suppose a PivotTable summarizes annual sales as $100,000 in 2024 and $120,000 in 2025. With Year as the base field and (previous) as the base item, the 2025 percent difference is 20%. The 2024 row has no earlier year in this comparison, so it cannot show a change against a previous item. This is expected; the first period is the baseline, not a failed calculation.
For a decline, if sales move from $100,000 to $90,000, the result is −10%: the $10,000 decrease divided by the earlier $100,000. The percentage is relative to the base period. Reversing the comparison would give a different percentage: a move from $90,000 back to $100,000 is about 11.1%, because the increase is divided by $90,000. Percent change is not symmetric.
Format results and show useful context
Percent change is easier to scan when it appears beside the original value. Keep both copies in Values, rename them clearly, and use a consistent number format such as one decimal place. A displayed 20.0% communicates more precision than 20%, but extra decimal places are not necessarily more informative; choose a level that suits the report.
To format the result, right-click a value in the percent-change field and choose Number Format, then choose Percentage and set decimal places. Using the value field’s number format is preferable to formatting only one visible cell, because the setting is intended to apply to that PivotTable field as it updates. A clear heading such as Year-over-year change also tells readers what the percentage compares.
Consider the Grand Total separately. A grand total is not automatically a meaningful “change from the previous period,” because it combines items rather than representing another time period. If the total row displays a percentage that could mislead readers, adjust the PivotTable layout or presentation so the period comparisons remain clear.
Common problems and what to check
- The first period is blank: There is no previous item in the selected sequence. This is normal for a previous-period comparison.
- The result compares the wrong periods: Check the base field, sort order, date grouping, and missing items. “Previous” follows field order, not an assumed calendar rule.
- The result is a count or looks unexpectedly small: Confirm the original value field is summarized by Sum, Average, or the intended measure. The display calculation works from that summary.
- A zero or missing baseline causes an unhelpful result: Percent change divides by the base value, so a zero baseline cannot produce an ordinary finite percentage. Missing periods also cannot supply a valid comparison. Review the source and decide whether to show a blank, mark the case as not applicable, or use a different baseline.
- New data is not included: Refresh the PivotTable after changing its source. If new rows fall outside the original source range, convert the source to an Excel Table or update the PivotTable data source, then refresh.
When diagnosing a surprising result, compare the displayed current and base amounts first. If those are not the two numbers you intended, changing the number format will not fix the underlying comparison setup.
PivotTable calculation or a worksheet formula?
Use % Difference From when you want an interactive report that recalculates as PivotTable fields, filters, and periods change. It works on summarized values and avoids adding a separate calculation column to the source data. It is particularly convenient for a quick year-over-year or previous-period view.
A worksheet formula can be more suitable when you need a custom rule, a stable report outside the PivotTable, or explicit handling for zero baselines. For a manual comparison, assume the earlier-period total is in B2 and the current-period total is in B3; enter this in C3 to return a blank if B2 is zero or otherwise unusable: =IFERROR((B3-B2)/B2,""). With B2 equal to 100 and B3 equal to 120, the formula returns 0.2, which displays as 20% when C3 is formatted as Percentage. The formula compares the two cells directly; it does not automatically adapt to PivotTable items the way a display calculation does.
Do not confuse percent change with % of Grand Total or % of Row Total. Those options answer what share a value represents within a total. For example, a region’s 20% of total sales is not the same claim as sales increasing by 20% from the previous year.
Frequently Asked Questions
How do I calculate month-over-month percentage change in a PivotTable?
Put a chronological month field in the PivotTable, add the measure to Values twice, and set the second copy to % Difference From. Select the month field and (previous). Make sure each item represents one month and the year boundary behaves as intended.
Can I show the dollar change and percent change together?
Yes. Add the numeric measure to Values three times if you want the original amount, the absolute difference, and the percent difference. Set the second copy to Difference From and the third to % Difference From, using the same base field and item.
Why is the first percentage blank?
With (previous) selected, the first item has no earlier item in the sequence. Excel has no base value for that position, so a blank is normal rather than evidence that the entire PivotTable calculation is broken.
Can I compare every year with the same baseline year?
Yes. In Value Field Settings > Show Values As, choose % Difference From, select Year as the base field, and choose the specific baseline year as the base item instead of (previous). The result then compares each displayed year with that fixed year.
Why is my PivotTable showing a percentage of total instead of percent change?
Open the second value field’s Show Values As setting and select % Difference From. % of Grand Total, % of Row Total, and similar options calculate a share of a total, not a change between periods.
Related
- Excel Merge Shortcut: How to Merge Cells in Microsoft 365
- Excel Merge Shortcut: Keys, Options, and Safer Methods
- Excel Merge Keyboard Shortcut and Safer Alternatives
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Google Sheets: How to Create and Customize a GraphTo create a graph in Google Sheets, select the cells containing your data, including their labels, then choose…
- Excel Bar Charts for Civil Construction ProjectsYes. In Excel for Microsoft 365 on Windows, put civil construction categories in one column and the quantities…
- Excel Pivot Table Refresh All Not Working? Try These FixesIf Excel’s Refresh All command is not updating a PivotTable, first check whether the PivotTable’s source includes the…
- Excel Not Making Graphs Correctly? Fix Common CausesIf Excel is not making graphs correctly, first check that the chart uses the intended data range, treats…
- Activate Power Pivot in Excel: Windows Setup GuideTo activate Power Pivot in Excel for Microsoft 365 on Windows, select File > Options > Add-ins, choose…
- Line of Balance Graph: Meaning and How to Read It ClearlyA line of balance graph is a time-and-location chart used to plan and monitor work that repeats across…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.