How to: Display or Hide Grand Totals for a Pivot Table

• Jun 07, 2019
• 2 minutes to read

To control how grand totals are displayed in a pivot table, use the following properties.

Property	Description
PivotLayout.ShowRowGrandTotals	Specifies whether to display a grand total column.
PivotLayout.ShowColumnGrandTotals	Specifies whether to display a grand total row.
PivotViewOptions.GrandTotalCaption	Specifies the text label for both the grand total column and grand total row.

Hide Grand Totals for Rows

View Example

PivotTableLayoutActions.vb

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")
' Add the "Region" field to the column axis area. 
pivotTable.ColumnFields.Add(pivotTable.Fields("Region"))

' Hide grand totals for rows.
pivotTable.Layout.ShowRowGrandTotals = False

PivotTableLayoutActions.cs

Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Add the "Region" field to the column axis area. 
pivotTable.ColumnFields.Add(pivotTable.Fields["Region"]);

// Hide grand totals for rows.
pivotTable.Layout.ShowRowGrandTotals = false;

The image below shows the resulting PivotTable report.

Hide Grand Totals for Columns

View Example

PivotTableLayoutActions.vb

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")
' Add the "Region" field to the column axis area. 
pivotTable.ColumnFields.Add(pivotTable.Fields("Region"))

' Hide grand totals for columns.
pivotTable.Layout.ShowColumnGrandTotals = False

PivotTableLayoutActions.cs

Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Add the "Region" field to the column axis area. 
pivotTable.ColumnFields.Add(pivotTable.Fields["Region"]);

// Hide grand totals for columns.
pivotTable.Layout.ShowColumnGrandTotals = false;

The image below shows the resulting PivotTable report.