Save Space With Pivot Table Subtotals

When you summarize data in a pivot table, it usually shows a sum of the values. (If there are blanks or text values in the field, usually the pivot table shows a count instead.) In this pivot table, you can see the total labour cost for each Service Type.


If there are two or more fields in the Row or Column area, subtotals are automatically created for all the fields except the last one. In the screenshot below, the District field was added below the Service Type field. Subtotals were automatically added to Service Type.


Change the Summary Function

Instead of seeing the Sum of the data, you can change the summary function, and show the Average, or any of the other options. You could even put the same field in the Values area of a pivot table multiple times, and use different summary functions in each column.

In this example, the sum of the labour cost is shown in column C, and the Max function is used in column D.


Change the Subtotal Summary Function

If your pivot table already has lots of columns, you might not want to make it wider, by adding another copy of one of the Value fields. Instead, you can change the summary function for the Subtotal, so it uses a different function than the Value fields.

Now the Value fields show the sum of labour costs, and the subtotal for each service type shows the highest labour cost that was charged for that service. You can see the maximums, without adding extra columns to the pivot table.


More Info on Pivot Table Subtotals

You can read more about pivot table subtotals, and the steps for changing them, on the Contextures website: Excel Pivot Table Subtotals.

Watch the Pivot Table Subtotals Video

To see the steps for changing the pivot table subtotals, and creating multiple subtotals, you can watch this short video.


You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *