Pivot Table Subtotals
Summary of Content
Definitions and Key Concepts
Tutorial: Calculate a Percentage for Row Subtotals in a Pivot Table
Tutorial: Calculate a Percentage for Column-Binned Row Subtotals
Related Resources
Definitions and Key Concepts
Grand Total – The sum of all individual rows in a column.
Parent Row Total – The sum of all grouped rows for each group in a grouped column
Row Total – The value of an individual row in a column
Note: Each parent row total is also considered a row total within its parent grouping. In the screenshot below, the East and West rows both show a parent row total calculating the sum of all states in the region. These two parent row totals are also considered row totals of their parent row’s total, which happens to be the column’s grand total.
Percent of Total – A row total or parent row total’s percentage of the column’s grand total
Percent of Parent Row Total – A row total’s percentage of its parent row’s total
Tutorial: Calculate a Percentage for Subtotals in a Pivot Table
In this tutorial, we will calculate the % of total state retail sales broken down by region.
Let’s start with a pivot table displaying [Sales Amount] broken down first by [Store Region] and then by [Store State].
- In the editor panel, click the plus icon (+) to the right of VALUES.
- Select Add New Column.
- In the formula bar, type:
[Sales Amount (Row Total)] / [Sales Amount (Parent Row Total)]
- Hit Enter to save the formula.
- Click the percent icon (%), located in the workbook toolbar, to format the calculated column to percent.
Result:
Tutorial: Calculate a Percentage for Column-Binned Row Subtotals
In this tutorial, we will calculate the % of total state retail sales broken down by region and sales quarter.
Let’s start with a pivot table displaying [Sales Amount] broken down into pivot rows, first by [Store Region] and then by [Store State], and binned into pivot columns based on [Sales Quarter].
- In the editor panel, click the plus icon (+) to the right of VALUES.
- Select Add New Column.
- In the formula bar, type:
[Sales Amount] / [Sales Amount (Parent Row Total)]
- Hit Enter to save the formula.
- Click the percent icon (%), located in the workbook toolbar, to format the calculated column to percent.
Result: