How to Show the Change in PivotTable Values After Refreshing in WPS Office Spreadsheets
Question details
The user wants to display the increase or decrease in completed activities after a PivotTable refresh instead of the current total.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an Excel-compatible PivotTable of completed activities and needs to compare refreshed values against the previous period rather than view absolute totals.
- Observed behavior
- After refreshing, the PivotTable currently displays the current total for completed activities; the user needs the value field configured to show the difference from the previous refresh.
Make sure your PivotTable contains at least two refresh periods or comparable snapshots so the Previous base item can be calculated. Open the workbook in WPS Office Spreadsheets and identify the value field that holds completed activities.
Use Show Values As Difference From to Display Change From Previous
Use this solution when you want the PivotTable to show the increase or decrease between the current refresh and the previous period instead of the current total.
The Difference From calculation compares each value against the previous base item. This is useful for period-over-period activity changes, but the source data must include a field that identifies the refresh periods or snapshots.
Click any cell in the PivotTable value area that contains the completed activities total, right-click, and choose Value Field Settings. You can also open it from the PivotTable Fields pane for the selected value field.
In the Value Field Settings dialog, go to the Show Values As tab, choose Difference From from the list, and then select the base field that identifies your refresh periods.
In the Base item list, choose Previous so WPS Spreadsheets calculates each value as the difference from the previous refresh period.
Click Number Format, choose Number or a custom numeric format, and confirm the dialog to display the change as a positive or negative number.
Display PivotTable Changes with WPS Spreadsheets
Use WPS Office Spreadsheets to configure a PivotTable value field with Show Values As > Difference From and Previous as the base item, so you can see the increase or decrease after each refresh without switching tools.
- 1. Download and install WPS Office: Get WPS Office from the official WPS website and install WPS Spreadsheets on your computer.
- 2. Open the Excel workbook: Launch WPS Spreadsheets and open the .xlsx or .xls file that contains your PivotTable.
- 3. Open Value Field Settings: Right-click the PivotTable value field, choose Value Field Settings, and go to the Show Values As tab.
- 4. Apply Difference From Previous: Choose Difference From, set the base field to your refresh period, and select Previous as the base item.
- 5. Apply the number format: Set the number format and refresh the PivotTable to display the change in completed activities.
Frequently Asked Questions
Why does my PivotTable still show the current total instead of the change after refresh?
The value field is likely still using the default calculation. Open Value Field Settings > Show Values As, choose Difference From, and set the base item to Previous.
Can I show the change as a percentage instead of a number?
Yes, in Value Field Settings > Show Values As, choose % Difference From if available, select the same base field and Previous base item, and apply a percentage number format.
What should I do if the Previous base item is grayed out?
Make sure the selected base field contains refresh periods or comparable snapshots that can be ordered, and that the PivotTable has more than one refresh period. If not, add a refresh period column to the source data.
Will this calculation survive when I refresh the PivotTable?
Yes, the Show Values As setting is saved in the PivotTable, so WPS Spreadsheets recalculates the difference each time you refresh with updated source data.
Is this method compatible with PivotTables created in Microsoft Excel?
Yes, WPS Spreadsheets opens .xlsx and .xls PivotTables and supports the same Show Values As options, so you can apply and keep the difference calculation.
