When you create a new pivot table, and add multiple fields to the row or column area, subtotals are automatically created. For Row fields the automatic subtotals usually appear at the top, and you can move them to the bottom, if that’s your preference. There are a few limitations though, which you can see in the video and the written notes below.
Video: Pivot Table Subtotals Top or Bottom
Watch this 3-minute video, to see how to add pivot table subtotals, and move them to the top or bottom of the group. You’ll also see the limitations for moving the pivot table subtotals.
Automatic Subtotals – Row Fields
When you add two or more fields to the pivot table Row area, subtotals are automatically created for the outer fields.
Usually, the subtotals appear at the top, like the Region subtotals in the pivot table shown below.
Automatic Subtotals – Column Fields
In the screen shot below, I added two fields to the pivot table Column area – Category and Product.
The Category field got automatic subtotals, and those appear at the bottom of each group – to the right of the category’s products.
Report Layout Limitations
There are limitations to where you can move the pivot field subtotals, based on the pivot table Report Layout you choose, and the pivot field’s position (Row or Column)
- For Column fields, the subtotals are always at the bottom of the group — there is no way to change them to appear at the top of the group.
- In Compact Layout and Outline Layout:
- Row subtotals can be shown at either the top or bottom of the group.
- Labels for the outer Row fields are always above the labels for the related inner fields, even when the subtotals are at the bottom of the group
- In Tabular Layout:
- Row subtotals can only be shown at the bottom of the group
- Labels for the outer Row fields are on the same row as the first label for the related inner fields
Get the Sample File
To get the sample file that I used for the video, please go to the Pivot Table Subtotals page on my Contextures website.
You’ll also find written instructions, and more Excel Pivot Table subtotal tips on that page. For example, see how to show multiple subtotals per field, using different functions.
_______________________
Show Excel Pivot Table Subtotals Top or Bottom
_______________________