Home > Pivot Tables > Calculation > Subtotals Excel Pivot Table SubtotalsShow or hide subtotals in a pivot table, for all fields, or individual pivot fields. Show multiple subtotals, with data summarized with different functions |
Video: Show and Move SubtotalsWatch this video to see how to move the subtotals to the top or bottom of a group, and see how the report layout affects the subtotal position. There are written steps below the video. Video Timeline
|
Show Subtotals at Top or BottomIn a new pivot table, when you add fields to the Row Labels area, subtotals are automatically shown, at the top of each group of items, for the outer fields. In the screenshot below, Region is the outer field, and City is the inner field. The subtotal for each region is shown at the top of its group of cities. Column Field SubtotalsIf you add two or more fields to the Columns area, subtotals are automatically added for the outer fields.
In the screenshot below, Category is the outer field in the Column area, and Product is the inner field. The subtotal for each Category is shown at the bottom (right side) of its group of products. Move Subtotals to BottomAlthough you can't change the position of the Column field subtotals, you can move the Row field subtotals to the bottom of the group, if you prefer. To move the subtotals to the bottom, follow these steps.
Move Subtotals to TopLater, if you want to move the subtotals back to the top, follow these steps. Note: This will not work for pivot tables in Tabular Layout. See Report Layout notes below.
Report Layout EffectThe position of the subtotals is also affected by the Report Layout applied to the pivot table.
|
Subtotal Summary Function - AutomaticWhen a subtotal is added to a pivot table, its Summary Function is set to Automatic. With that setting, the subtotal automatically uses the same Summary Function as the Value fields in each column. In the pivot table shown above, the Value fields are using the SUM function, so the subtotals also show the SUM of the values. In the pivot table shown below, the Value fields have been changed to the MAX function, so the subtotals also show the MAX of the values. A few of the MAX values are highlighted in green, to show that the values and both subtotals are the same. Even though the Summary Function has been changed to MAX, each subtotal still shows the name of the item, and "Total", such as Central Total. |
Create a Custom SubtotalInstead of using the Automatic setting for subtotals, you can create a custom subtotal in a pivot table -- either a single custom subtotal, or multiple custom subtotals. Watch this short video to see the steps, and the written instructions are below the video. To create a custom subtotal:
Note: The subtotals will be calculated on the source data, not on the values showing in the pivot table. For example, if a field uses the MAX summary function, and the subtotal shows the AVERAGE, it will be an average from the values in the source data, not an average of the MAX values. (To calculate the Average of the Max values, you could use formulas outside of the pivot table, or create a new pivot table, based on the original one.) |
Show Multiple SubtotalsIn the Field Settings dialog box shown above, there are two functions, Count and Max, selected in the list of Summary Functions for the Service field. After selecting these functions, the pivot table shows two subtotals for each Service type. When you use Custom functions, the subtotal row shows the item name, and the name of the Function, such as Install Count. The subtotals for District are not changed, nor are the other values in the pivot table. View the steps in the short Pivot Table Subtotals video, in the previous section. |
Subtotal Date FormattingIf you show subtotals for a date field, the dates might be in a format that is different from the other dates in that field. Watch this short video to see why this happens, and how to avoid the problem of different formats. You can get the sample file for this video in the Download section, below. Different Date Format in SubtotalsThere's an example of different date formats for subtotals, in the screen shot below,
Fix Different Date FormatThis problem occurs if you select the date cells and format them, and then add subtotals. To prevent the problem, change the Number Format of the date field, by following these steps.
The subtotal number format will now match the other dates in the field. View the steps in a short Subtotal Date Format video.
|
Get the Sample Files
|
Last updated: July 18, 2023 4:01 PM