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.

pivot_table_row_totals.png

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

pivot_table_subtotals__2.png

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

Screen_Shot_2022-03-23_at_2.18.56_PM.png
  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)\]
    

    Screen_Shot_2021-12-22_at_3.40.58_PM.png

  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:
    Screen_Shot_2022-01-03_at_11.27.48_AM.png

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

Screen_Shot_2022-01-03_at_11.23.42_AM.png
  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. Screen_Shot_2022-01-03_at_11.25.33_AM.png

  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:
    Screen_Shot_2022-01-03_at_11.26.36_AM.png


Related resources