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

• Sep 19, 2023
• 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.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;

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

The image below shows the resulting PivotTable report (the workbook is opened in Microsoft® Excel®).

Hide Grand Totals for Columns

View Example

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;

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

The image below shows the resulting PivotTable report (the workbook is opened in Microsoft® Excel®).