Pivot Table Issues

Show PivotTable Values as Percentages of Different Totals in Excel

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

Display two PivotTable value columns as percentages using different denominators.

Show PivotTable Values as Percentages of Different Totals in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Arrange the value fields

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.

2
Set the column percentage

Right-click a value in the first field and select Show Values As > % of Column Total.

3
Set the row percentage

Right-click a value in the second field and select Show Values As > % of Row Total.

4
Check the denominators

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.

Set Different Show Values As Options for Each PivotTable Field
State totals may need a different calculation: A column total is not necessarily the same as a state subtotal. If the available parent-total options do not match your layout, use worksheet formulas for the specific totals.
Work with PivotTable percentages in WPS

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. 1. Open the workbook: In WPS Spreadsheets, choose Open and select the Excel workbook containing your PivotTable.
  2. 2. Identify the totals: Find the value cells and the state, column, or row total cells that should serve as denominators.
  3. 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. 4. Format the results: Select the formula cells and use the percentage format on the Home toolbar. Recheck the references after refreshing the PivotTable.
Calculate percentages against different PivotTable totalsOpen and edit Microsoft Excel XLSX filesUse familiar worksheet formulas and percentage formattingWork in a free, easy-to-use office suite
microsoft office alternative - wps office

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.