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

  • 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

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.

DXSpreadsheet_PivotTable_HideGrandTotalColumn

Hide Grand Totals for Columns

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.

DXSpreadsheet_PivotTable_HideGrandTotalRow