Show PivotTable Values as a Percentage of a Parent Row Grand Total in Excel
Question details
Calculate PivotTable values as a percentage of the intended parent row grand total rather than the immediate parent total.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A PivotTable has nested row categories, and the user wants each value divided by a specific parent row's grand total.
- Observed behavior
- The built-in % of Parent Row Total calculation uses the immediate parent as its denominator, which may not be the desired grand total.
Identify the value to divide and the exact grand total that should be its denominator. Check whether that total appears as the column total in the current PivotTable layout.
Use % of Column Total when the column total is the correct denominator
Use this built-in calculation when the relevant column total matches the parent row grand total you need.
Right-click a value in the PivotTable and select Value Field Settings.
Open the Show Values As tab, select % of Column Total, and click OK.
Compare a displayed percentage with the original value divided by the intended grand total. If the denominator is different, use an outside formula instead.

Calculate the percentage outside the PivotTable
Use a worksheet formula when the built-in column total is not the desired denominator.
Calculate a Parent Total Percentage in WPS Spreadsheet
Use WPS Spreadsheet to open your workbook and calculate a PivotTable value as a percentage of the specific total you choose.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Identify the cells: Find the PivotTable value and the parent row grand total you want to use as the denominator.
- 3. Enter the calculation: In an empty cell outside the PivotTable, enter a formula such as =B5/$D$5, replacing the references with your value and total cells.
- 4. Format the result: Select the formula cell and apply Percentage formatting from the Home tab. Verify the references after refreshing or rearranging the PivotTable.

Frequently Asked Questions
Why does % of Parent Row Total give me a different percentage?
Excel divides by the immediate parent row total. If you need a higher-level or grand total instead, check whether % of Column Total uses that total or calculate the percentage outside the PivotTable.
When should I use % of Column Total?
Use it when the total at the bottom of the relevant value column is the denominator you want. Select Value Field Settings > Show Values As > % of Column Total, then verify a result against the intended total.
Can I use GETPIVOTDATA for the calculation?
Yes. Divide a GETPIVOTDATA formula that retrieves the desired value by another that retrieves the intended total. Check the field and item arguments so the denominator matches the correct parent and filters.
What happens to an outside formula when the PivotTable is refreshed?
The formula recalculates, but ordinary cell references may point to different values if the PivotTable layout changes. Recheck the references after refreshing or consider GETPIVOTDATA to retrieve values by field and item.




