Search Contextures Sites

Amazon.com      Amazon.com	Excel Pivot Table Subtotals Creating Excel Pivot Table Subtotals   Add Another Subtotal   Automatic Subtotal Summary Function   Change the Subtotal Summary Function   Show Multiple Subtotals   Pivot Table Tutorial List   View the steps in a short Pivot Table Subtotals video
	Creating Excel Pivot Table Subtotals  If your pivot table has only one field in the Row Labels area, you won't see any Row subtotals. In the pivot table shown below, Service is in the Row Labels area, Lead Tech is in the Column Labels area, and Labor Cost is in the Values area. Because Service is the only field in the Row Labels area, it has no subtotal.
	When you add another field to the Row Labels area, a subtotal is automatically created for the first field. In this example, the District field is added to the Row Labels area, below the Service field. The Service field is an Outer Field, because there is a field below it (District). The District field is an Inner Field, because there is NO field below it. Because Service is now an Outer Field, it automatically has a subtotal after each Service type. Each subtotal shows the name of the Service type, and "Total", such as Install Total.
	Add Another Subtotal  If you add another field to the Row Labels area, below the District field, the new field becomes the Inner Field, and District changes to an Outer Field. In the pivot table below, the Technician Count field was added below District, and the District field now has a subtotal after each District name.
	Automatic Subtotal Summary Function  When 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.
	Change the Subtotal Summary Function  Instead of using the Automatic setting for subtotals, you can select a Custom setting. To change the setting: Right-click a label for the field in which you want to change the subtotal. In this example, right-click cell B5, which has the Install label. In the pop-up menu, click Field Settings In the Field Settings dialog box, click the Subtotals & Filters tab Under Subtotals, click Custom In the list of Summary Functions, click one or more function names Click OK to close the dialog box.
	Show Multiple Subtotals  In 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 a short Pivot Table Subtotals video.

Pivot Table Tutorials Excel Pivot Table -- Introduction  Excel Pivot Table -- Clear Old Items Excel Pivot Table -- Create a Pivot Table in Excel 2007  Excel Pivot Table -- Custom Calculations  Excel Pivot Table -- Data Field Layout Excel Pivot Table -- Dynamic Data Source Excel Pivot Table -- FAQs Excel Pivot Table -- Field Settings Excel Pivot Table -- Filter Source Data   Excel Pivot Table -- Filters, Top 10  Excel Pivot Table -- GetPivotData Excel Pivot Table -- Grand Totals Excel Pivot Table -- Grouping Data Excel Pivot Table -- Layout, Excel 2007 Excel Pivot Table -- Multiple Consolidation Ranges Excel Pivot Table -- Pivot Cache    Excel Pivot Table -- PivotTable Style Excel Pivot Table -- Printing    Excel Pivot Table -- Protection   Excel Pivot Table -- Report Filters Excel Pivot Table -- Running Totals   Excel Pivot Table -- Show and Hide Items  Excel Pivot Table -- Sorting Excel Pivot Table -- Subtotals  Excel Pivot Table -- Summary Functions Excel Pivot Table -- Unique Items

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2010 All rights reserved.	Debra Dalgleish

Last updated: November 15, 2009 2:24 PM