Pivot table subtotals

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].

1. In the editor panel, click the plus icon (+) to the right of VALUES.

2. Select Add New Column.

3. In the formula bar, type:

   1. \[Sales Amount (Row Total)\] / \[Sales Amount (Parent Row Total)\]
   

   

4. Hit Enter to save the formula.

5. 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].

1. In the editor panel, click the plus icon (+) to the right of VALUES.

2. Select Add New Column.

3. In the formula bar, type:

   1. Sales Amount\] / \[Sales Amount (Parent Row Total)\]
   

4. 

5. Hit Enter to save the formula.

6. Click the percent icon (%), located in the workbook toolbar, to format the calculated column to percent.
   Result: