Calculate a percentage for subtotals in a pivottable

To show percentages such as % of Parent Total, % of Grand Total or % Running Total In in a PivotTable, choose from the Show Values As options.

To show calculations side by side with the values they're based on (for example, to show the % of Grand Total next to the subtotal), first you need to duplicate the value field by adding it more than once.

1. In the Field List, drag the field you want to duplicate to the Values area, and place it right below the same field.

   Excel adds the value field to the PivotTable with a unique identification number appended to its name. Change the name if you want.

2. Right-click the value field in the PivotTable, and then pick Show Values As.

3. Click the option you want.

   Use this option:	To show or calculate:
   No Calculation	The value that is entered in the field.
   % of Grand Total	Values as a percentage of the grand total of all the values or data points in the report.
   % of Column Total	All the values in each column or series as a percentage of the total for the column or series.
   % of Row Total	The value in each row or category as a percentage of the total for the row or category.
   % Of	Values as a percentage of the value of the selected Base item in its Base field.
   % of Parent Row Total	Values as: (value for the item) / (value for the parent item on rows)
   % of Parent Column Total	Values as: (value for the item) / (value for the parent item on columns)
   % of Parent Total	Values as: (value for the item) / (value for the parent item of the selected Base field)
   Difference From	Values as the difference from the value of the selected Base item in its Base field.
   % Difference From	Values as the percentage difference from the value of the selected Base item in its Base field.
   Running Total in	The value for successive items in the chosen Base field as a running total.
   % Running Total in	The value as a percentage for successive items in the chosen Base field that are displayed as a running total.
   Rank Smallest to Largest	The rank of selected values in a specific field, listing the smallest item in the field as 1, and each larger value with a higher rank value.
   Rank Largest to Smallest	The rank of selected values in a specific field, listing the largest item in the field as 1, and each smaller value with a higher rank value.
   Index	Values as: ((value in cell) x (Overall Grand Total)) / ((Grand Row Total) x (Grand Column Total))

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Create a PivotTable to analyze worksheet data

Create a PivotTable to analyze external data

Create a PivotTable to analyze data in multiple tables

Show or hide subtotals and totals in a PivotTable

Sum values in a PivotTable