Show PivotTable Values as Percentages of Different Totals in Excel
Question details
Display two PivotTable value columns as percentages using different denominators.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- A user wants one value expressed as a percentage of a state or column total and another as a percentage of its row total.
- Observed behavior
- The desired result is a PivotTable or adjacent calculation that shows each value against its specified total.
Check which PivotTable cells contain the state or column total and the row total you want to use. If you need two percentages of the same value, add that value field to the Values area twice.
Set Different Show Values As Options for Each PivotTable Field
Use built-in settings when the required denominators are the PivotTable column and row totals.
In the PivotTable Fields pane, drag the required fields into the Values area. To calculate two percentages from the same field, drag that field into Values twice.
Right-click a value in the first field and select Show Values As > % of Column Total.
Right-click a value in the second field and select Show Values As > % of Row Total.
Compare the results with the PivotTable column and row totals. If the state total is a parent subtotal rather than the column total, check Show Values As for an appropriate parent-total option.

Calculate Percentages Beside the PivotTable
Use worksheet formulas when the required state or row denominator is not available through Show Values As.
Calculate Different PivotTable Percentages in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use adjacent formulas to divide each PivotTable value by its required state, column, or row total.
- 1. Open the workbook: In WPS Spreadsheets, choose Open and select the Excel workbook containing your PivotTable.
- 2. Identify the totals: Find the value cells and the state, column, or row total cells that should serve as denominators.
- 3. Add formulas: In empty cells outside the PivotTable, enter formulas such as =IFERROR(B5/B$10,0) and =IFERROR(C5/$E5,0), replacing the references with the appropriate cells.
- 4. Format the results: Select the formula cells and use the percentage format on the Home toolbar. Recheck the references after refreshing the PivotTable.

Frequently Asked Questions
Can I show the same PivotTable value as both a row and a column percentage?
Yes. Add the same field to the Values area twice, then right-click each value field and choose its respective option under Show Values As.
Why does the column percentage not match the state percentage I need?
The PivotTable column total may include more than one state. Check whether the state is a parent group and whether a parent-total option under Show Values As matches your layout; otherwise, divide by the state subtotal in a worksheet formula.
How can I prevent a divide-by-zero error in a percentage formula?
Wrap the division in IFERROR, for example =IFERROR(B5/B$10,0). This displays zero if the denominator is zero or the division produces another error.
What should I check after refreshing the PivotTable?
Confirm that worksheet formulas still reference the intended values and totals, especially if the PivotTable has expanded or moved. GETPIVOTDATA can retrieve values by PivotTable field and item.




