Show an Excel PivotTable Grand Total as a Number Instead of a Percentage
Question details
Show a PivotTable grand total as a numeric value while displaying detail values as percentages of row totals.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A PivotTable value field uses Show Values As % of Row Total, but the user wants the grand total column to show the underlying number.
- Observed behavior
- The grand total uses the same percentage calculation and number format as the other values in the field.
Click inside the PivotTable and check that the original numeric field is available in the PivotTable Fields list. Confirm whether the desired total should use Sum or Count.
Add the value field a second time for a numeric total
Use a second copy of the field to keep the percentage calculation and the numeric total in the same PivotTable.
A PivotTable value field applies its Show Values As calculation across that field, including the grand total. Changing the grand total's number format alone does not restore the underlying sum or count.
Click the PivotTable, then drag the same numeric field from the PivotTable Fields list into the Values area a second time.
Open Value Field Settings for the first copy, select Show Values As, and choose % of Row Total.
Open Value Field Settings for the second copy. Under Summarize Values By, choose Sum or Count, and under Show Values As, choose No Calculation.
In the second field's Value Field Settings, select Number Format and choose Number with the desired decimal places.
Display the numeric grand total outside the PivotTable
Use an external formula when you want a separate cell to show the numeric total alongside the percentage-based PivotTable.
Show Percentage and Numeric PivotTable Values in WPS Spreadsheet
In WPS Spreadsheet, add the same source field twice to a PivotTable: use one copy for row percentages and the other for a numeric sum or count.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and click inside the PivotTable.
- 2. Add the value field twice: In the PivotTable field list, drag the numeric source field into the Values area a second time.
- 3. Set the percentage field: Open the first value field's settings and select Show Values As > % of Row Total.
- 4. Set the numeric field: Open the second value field's settings, select Sum or Count, and set Show Values As to No Calculation. Use its number format settings to choose the desired decimal places.

Frequently Asked Questions
Can I change just the grand total's format to show the original number?
No. Changing a percentage cell's number format changes its appearance, not the underlying calculation. Add the source field again and set the second copy to No Calculation to display its sum or count.
Why does the grand total show 100%?
With Show Values As set to % of Row Total, the grand total for a row represents the whole row, so it normally displays 100%.
How do I choose between Sum and Count for the numeric total?
Right-click a value in the second field and open Value Field Settings. Under Summarize Values By, choose Sum to add numeric values or Count to count entries.
Can I show the numeric total in a separate cell?
Yes. After adding a numeric copy of the value field, type = in a cell outside the PivotTable and click its numeric grand total. Excel may create a GETPIVOTDATA formula that retrieves the total.




