How to Show Grand Total Rows for PivotTable Difference From Values in WPS Office Spreadsheets
Question details
The user needs to display grand total rows for a PivotTable in which a value field uses the Difference From setting, because the row totals are not calculated automatically.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The PivotTable has Difference From values across date columns, and the user wants a meaningful total for each row label.
- Observed behavior
- The PivotTable does not calculate the desired row totals automatically, even though column calculations may appear.
Before you start, confirm that your PivotTable is created in WPS Office Spreadsheets and that the value field is set to show values as Difference From. Identify the row label cells and the exact difference columns you want to total.
Calculate Row Totals with a SUM Formula Next to the PivotTable
Use this when the PivotTable value field uses Difference From and the Grand Total row is blank or incorrect; a simple worksheet sum gives the row-level total.
Excel PivotTables, including those in WPS Office Spreadsheets, do not calculate the desired row totals automatically when a value field is set to Difference From. The Grand Total column may show a result, but the row total based on those difference values must be calculated outside the PivotTable.
Click in an empty cell in the first data row, next to the PivotTable difference columns.
Type =SUM(F3:H3) and replace F3:H3 with the actual range of difference value columns for that row.
Press Enter, then drag the fill handle down to apply the formula to the remaining PivotTable rows.
Hide the Incorrect Grand Total Column
Use this if an unwanted Grand Total column is showing a value for Difference From fields and you want to keep only row-level totals.
Calculate PivotTable Difference From Row Totals in WPS Office Spreadsheets
Open the workbook in WPS Office Spreadsheets and use a SUM formula next to the PivotTable to total each row’s Difference From values. You can also adjust Grand Totals from the Design tab to show only the total you need.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the Excel workbook containing the PivotTable.
- 2. Calculate row totals with SUM: Click in the first empty cell beside the PivotTable row and enter =SUM(F3:H3), using your actual difference columns.
- 3. Fill the formula down: Press Enter and drag the fill handle down through all row labels.
- 4. Adjust Grand Totals if needed: Select any PivotTable cell, click Design > Grand Totals > On for Columns Only to remove an unwanted Grand Total column.
Frequently Asked Questions
Why doesn’t the PivotTable Grand Total row calculate correctly when I use Difference From?
When a value field uses Difference From, the PivotTable calculates each cell relative to a base item, and the Grand Total row is not designed to sum those row-level difference values automatically. You can calculate the row total with a worksheet formula such as =SUM(F3:H3) or adjust the Grand Total display from Design > Grand Totals.
How do I calculate cumulative row totals for Difference From values in WPS Office Spreadsheets?
Click in an empty cell next to the PivotTable row, enter =SUM(F3:H3) with the actual difference columns, press Enter, and fill the formula down to the remaining rows.
How do I remove the unwanted Grand Total column in a PivotTable?
Select any cell inside the PivotTable, go to the Design tab, click Grand Totals, and choose On for Columns Only.
Can I use a different range if my Difference From columns are not F through H?
Yes. Replace F3:H3 in the SUM formula with the range that contains your difference value columns for that row, then drag the fill handle down.
Will WPS Office preserve my PivotTable and formulas when saving as Excel?
Yes. WPS Office Spreadsheets supports Excel .xlsx workbooks, so your PivotTable settings, formulas, and Grand Totals options are preserved when you save.
