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 |
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 Files1) Click here to download the sample workbook for the Subtotal Date Format tutorial. The zipped Excel file is in xlsx format, and does not contain any macros. 2) Click here to download the sample file for Pivot Table Custom Subtotals. The zipped Excel file is in xlsx format, and does not contain any macros. |
Last updated: March 7, 2023 11:05 AM