Pivot Table Issues

Show PivotTable Values as a Percentage of a Parent Row Grand Total in Excel

Anum MushtaqAnum Mushtaq Nov 18, 2026 868 views

Question details

Calculate PivotTable values as a percentage of the intended parent row grand total rather than the immediate parent total.

Show PivotTable Values as a Percentage of a Parent Row Grand Total in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Open value settings

Right-click a value in the PivotTable and select Value Field Settings.

2
Choose the calculation

Open the Show Values As tab, select % of Column Total, and click OK.

3
Verify the result

Compare a displayed percentage with the original value divided by the intended grand total. If the denominator is different, use an outside formula instead.

Use % of Column Total when the column total is the correct denominator
Check the layout: % of Column Total uses the total for the value's column. It does not automatically select an arbitrary parent row grand total.
Calculate PivotTable percentages

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. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Identify the cells: Find the PivotTable value and the parent row grand total you want to use as the denominator.
  3. 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. 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.
Calculate percentages using the parent total you specifyOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and percentage formattingWork with spreadsheets in a free, easy-to-use office suite
microsoft office alternative - wps office

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.