How to Remove Grand Total in a Pivot Table in Excel

If you find that the grand total in your pivot table do not add much value, or perhaps you want to create a report where grand total values are not required, then it is fine to remove it.

• Grand Total Row – This row shows the totals for the columns.
• Grand Total Column – This column shows the totals for all the rows.

Steps to Remove Grand Total from Pivot Table

There are multiple ways to remove a pivot table. You can remove them entirely, or you can also remove them partially.

1. Click Pivot Table Cell: The first step is to click the pivot table from which you want to remove the grand totals.
2. Navigate to the ‘Design’ Tab: The “Design” tab will appear on the Excel ribbon once you select the pivot table.
3. Locate the ‘Layout’ Group: In the ‘Design’ tab, you will see several groups of options. Look for the ‘Layout’ group.
4. Click on the ‘Grand Totals’ drop-down: Within the “Layout” group, you’ll find an option labeled “Grand Totals”. This option has four options to change the total setting for the pivot table.
5. Choose Off for Rows and Columns: If you want to remove the grand totals for the entire pivot table at once, click on “Off for Rows and Columns.”

Different Options Available to Add or Remove Grand Total from Pivot Table (Displaying Grand Totals)

As you have seen in the drop-down, there are four options available for displaying the grand totals:

• Off for Rows and Columns: This option removes the display of grand totals in your pivot table. This can be helpful if you focus solely on individual values and provide a less cluttered view.
• On for Rows and Columns: This is the default setting for pivot tables. With this option, Excel shows the grand total for both totals. This comprehensive view lets you see the sum total of all row and column data.
• On for Rows Only: This will show the grand total for rows. For example, if your rows represent different months, this option would allow you to see the total result for each month.
• On for Columns Only: This shows the grand total only for columns. If your columns represent different categories or groups, this setting will provide the total for each category or group.

Note – Removing and adding grand totals doesn’t make any changes to the pivot table data (source data).

Remove Grand Total from Pivot Table using Pivot Table Options

You can remove the grand totals from a pivot table by accessing the Pivot Table options.

When you right-click on a pivot table, you can open the “Pivot Table Options…” from the right-click menu that opens the PivotTable options dialog box.

Now, from the dialog box, go to the “Totals & Filters” tab. From there, click on the un-tick “Show grand totals for rows” and “Show grand totals for columns”.

Remove All Pivot Table Grand Totals at Once

VBA code is the best way to remove grand totals from all the pivot tables in a worksheet or workbook.

Sub remove_pivottable_grandtotals()
Dim myPivot As PivotTable
  For Each myPivot In ActiveSheet.PivotTables
        myPivot.ColumnGrand = False
        myPivot.RowGrand = false
  Next myPivot
End Sub

• Dim myPivot As PivotTable: This line declares a variable called “myPivot” that will be used to reference each Pivot Table in the active sheet.
• For Each myPivot in the Active Sheet.PivotTables: This line starts a loop that iterates over all Pivot Tables in the active sheet. For each iteration, “myPivot” will reference a different Pivot Table.
• myPivot.ColumnGrand = False: This line removes the grand total for columns in the current Pivot Table referenced by “myPivot”.
• myPivot.RowGrand = True: This line enables the grand total for rows in the current Pivot Table referenced by “myPivot”.
• Next myPivot: After executing this line, the code returns to the “For Each” line and “myPivot” references the following Pivot Table.

Remove Grand Total from all the Pvot tables from a Workbook

Sub remove_pivottable_grandtotals()
Dim myPivot As PivotTable
Dim mySheet As Worksheet
    'removes grand total rows and columns from a pivot in all the worksheet
    For Each mySheet In ActiveWorkbook.Worksheets
      For Each myPivot In ActiveSheet.PivotTables
            'removes grand total columns
            myPivot.ColumnGrand = True
            'row grand totals
            myPivot.RowGrand = True
      Next myPivot
    Next mySheet
End Sub

Hide Grand Totals Instead of the Removing

Removing a grand total from a pivot table is easy. Sometimes, however, you want to hide it instead.

The only solution for this is to hide the row or columns containing the grand total.