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, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

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

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×