Show or hide subtotals and totals in a PivotTable

When you create a PivotTable that shows value amounts, subtotals and grand totals appear automatically, but you can also show or hide them.

PivotTable Grand Totals and Subtotals

Show or hide subtotals

  1. Click anywhere in the PivotTable to show the PivotTable Tools.

    PivotTable Tools

  2. Click Design > Subtotals.

    Subtotals button on the Design tab

  3. Pick the option you want:

    • Do Not Show Subtotals

    • Show all Subtotals at Bottom of Group

    • Show all Subtotals at Top of Group

      Tip:  You can include filtered items in the total amounts by clicking Include Filtered Items in Totals. Click this option again to turn it off. You’ll find additional options for totals and filtered items on the Totals & Filters tab in the PivotTable Options dialog box (Analyze> Options).

Show or hide grand totals

  1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

    PivotTable Tools

  2. Click Design > Grand Totals.

Grand Totals button on the Design tab

  1. Pick the option you want:

    • Off for Rows and Columns

    • On for Rows and Columns

    • On for Rows only

    • On for Columns only

Tip:  If you don’t want to show grand totals for rows or columns, uncheck the Show grand totals for rows or Show grand totals for columns boxes on the Totals & Filters tab in the PivotTable Options dialog box (Analyze> Options).

When you create a PivotTable that shows value amounts, subtotals and grand totals appear automatically, but you can also show or hide them.

Example PivotTable showing subtotals and grand totals

Tip: Row grand totals are only displayed if your data just has one column, because a grand total for a group of columns often doesn't make sense (for example, if one column contains quantities and one column contains prices). If you want to display a grand total of data from several columns, create a calculated column in your source data, and display that column in your PivotTable.

Show or hide subtotals

To show or hide subtotals:

  1. Click anywhere in the PivotTable to show the PivotTable Analyze and Design tabs.

    PivotTable Analyze and Design tabs

  2. Click Design > Subtotals.

    On the Design tab, select Subtotals or Grand Totals

  3. Pick the option you want:

    • Don't Show Subtotals

    • Show All Subtotals at Bottom of Group

    • Show All Subtotals at Top of Group

      Tip:  You can include filtered items in the total amounts by clicking Include Filtered Items in Totals. Click this option again to turn it off.

Show or hide grand totals

  1. Click anywhere in the PivotTable to show the PivotTable Analyze and Design tabs.

    PivotTable Analyze and Design tabs

  2. Click Design > Grand Totals.

    On the Design tab, select Subtotals or Grand Totals

  3. Pick the option you want:

    • Off for Rows & Columns

    • On for Rows & Columns

    • On for Rows Only

    • On for Columns Only

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

Filter data in a PivotTable

Calculate a percentage for subtotals 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.

×