Pivot Table Issues

Show an Excel PivotTable Grand Total as a Number Instead of a Percentage

Phi Hung VoPhi Hung Vo Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Add the field again

Click the PivotTable, then drag the same numeric field from the PivotTable Fields list into the Values area a second time.

2
Keep the percentage calculation

Open Value Field Settings for the first copy, select Show Values As, and choose % of Row Total.

3
Set the numeric calculation

Open Value Field Settings for the second copy. Under Summarize Values By, choose Sum or Count, and under Show Values As, choose No Calculation.

4
Format the numeric field

In the second field's Value Field Settings, select Number Format and choose Number with the desired decimal places.

Both calculations may appear in detail columns: The second field can add numeric values to the detail columns as well as the grand total. Adjust the PivotTable layout or use a formula outside the PivotTable if you need a separate numeric total display.
Work with PivotTables in WPS

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and click inside the PivotTable.
  2. 2. Add the value field twice: In the PivotTable field list, drag the numeric source field into the Values area a second time.
  3. 3. Set the percentage field: Open the first value field's settings and select Show Values As > % of Row Total.
  4. 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.
Display percentage and numeric calculations in the same PivotTable.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface with PivotTable tools.
microsoft office alternative - wps office

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.