officefileapi-118502-spreadsheet-document-api-examples-pivot-tables-how-to-subtotal-fields-in-a-pivot-table.md
This topic describes how to manage subtotals in a pivot table. Subtotals automatically appear for outer fields when you add multiple row and/or column fields to your report.
Select the task you wish to perform.
To show or hide subtotals in a PivotTable report, use the following methods of the PivotLayout object accessible using the PivotTable.Layout property.
| Method | Description |
|---|---|
| PivotLayout.ShowAllSubtotals | Displays all subtotals in a pivot table. |
| The method’s topOfGroup parameter specifies the subtotal location for the outer row fields in compact or outline form. | |
| PivotLayout.HideAllSubtotals | Hides all subtotals in a pivot table. |
The following code example displays all subtotals in a pivot table. Subtotals for the “Category” row field are shown at the bottom of each item in the field.
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Show all subtotals at the bottom of each group.
pivotTable.Layout.ShowAllSubtotals(false);
Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Show all subtotals at the bottom of each group.
pivotTable.Layout.ShowAllSubtotals(False)
The image below shows the resulting report (the workbook is opened in Microsoft® Excel®).
The table below describes properties and methods you can use to specify and adjust subtotals for a specific row or column field in a report.
|
Member
|
Description
| | --- | --- | |
PivotField.SetSubtotalAutomatic
|
Displays automatic subtotals for a given field.
| |
|
Allows you to change the default summary calculation or to show multiple subtotals for a given field.
To remove subtotals for a field, pass the PivotSubtotalFunctions.None value to the method as a parameter.
| |
|
Specifies summary functions used to calculate subtotals for a PivotTable field.
| |
PivotFieldLayout.SubtotalOnTop
|
Specifies the subtotal location for an outer row field shown in outline or compact form.
You can display subtotals at the top or bottom of the field’s items.
| |
|
Specifies the text to be displayed in the field’s subtotal row or column heading.
|
The following example demonstrates how to use multiple functions to subtotal the “Category” row field. To do this, combine the required PivotSubtotalFunctions enumeration values with a bitwise OR operator.
Note
You cannot change the subtotal function for a field containing a calculated item. In this case, a System.InvalidOperationException exception will be thrown.
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["Category"];
// Display multiple subtotals for the field.
field.SetSubtotal(PivotSubtotalFunctions.Sum | PivotSubtotalFunctions.Average);
Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Access the pivot field by its name in the collection.
Dim field As PivotField = pivotTable.Fields("Category")
' Display multiple subtotals for the field.
field.SetSubtotal(PivotSubtotalFunctions.Sum Or PivotSubtotalFunctions.Average)
The image below shows the resulting report (the workbook is opened in Microsoft® Excel®).