How to Remove Grand Total from Pivot Table in Excel

A pivot table is an excellent tool to show data with regular and numerical values. It automatically counts the subtotals and grand totals of the fields and shows them in the table without manual input. However, having a grand total in a pivot table can sometimes result in clutter. For many worksheets, subtotals are sufficient for preparing a report, and grand totals merely divert attention from the main topic. In this article, we will learn how to remove the grand total from a pivot table, making your worksheet look cleaner.

The table we are using for this pivot table contains budget data for a few months. There are categories of the budgets, the amount that was actually spent, and the months the budgets are set for. We made a pivot table for these values. We can see the subtotals for each month, and the grand total is not needed. Therefore, we want to remove the grand total from the pivot table. Here is how to do that:

➤ Select a cell from the pivot table to enable the PivotTable Analyze and Design tab on the ribbon.
➤ The layout group can be located on the left. Click on Grand Totals to open the dropdown.
➤ Select Off for Rows and Columns to turn off the grand totals. You can also select On for Rows Only to remove only the grand totals for the columns, or select On for Columns Only to remove only the grand totals for the rows.

➤ As you can see, the grand totals aren’t visible anymore.

There is a pivot table options menu that lets us manipulate the pivot table in a lot of ways. We can easily use that to hide the grand totals.

➤ There are two ways to access the pivot table options. First, you can right-click on the pivot table and select PivotTable Options.

➤ The second method to do that is going to the PivotTable Analyze tab and selecting PivotTable > Options.

➤ In the PivotTable Options window, go to the Totals & Filters tab.
➤ Uncheck Show grand totals for rows and Show grand totals for columns, then hit OK.

➤ Now the pivot table should not have grand totals anymore.

A smart way of removing grand totals from the pivot table is to use VBA code. While this method is a bit more complex, it is helpful to automate things for expert users. Follow the steps below:

➤ Press  Alt  +  F11  to open the IDE.
➤ Go to Insert > Module to open the code editor.

➤ Now write the following code in the code editor:

Sub RemovePivotGrandTotals()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Set ws = ThisWorkbook.Sheets("Pivot") '
    Set pt = ws.PivotTables("PivotTable1")
    With pt
        .ColumnGrand = False
        .RowGrand = False
    End With
    MsgBox "Grand totals removed.", vbInformation
End Sub

➤ Replace Pivot with the name of the worksheet and PivotTable1 with the name of the pivot table.

➤ The pivot table name can be learned from the PivotTable Analyze tab.

➤ Now, run the code by going to Run > Run Sub/UserForm from the code window.

➤ A message box will show up with the following message, and the grand totals will be removed from the table.

The previous method only removes grand totals from a certain pivot table. But we can achieve more using VBA. Instead of removing grand totals from a specific pivot table, we can remove the grand totals from all pivot tables in a single sheet, or even multiple sheets. Follow the guide below.

➤ Press  Alt  +  F11  to open the code IDE.
➤ Go to Insert > Module to write code.

➤ Write the following code to hide grand totals for all PivotTables on the active worksheet:

Sub DisablePivotTableGrandTotals()
    Dim ptTable As PivotTable
    For Each ptTable In ActiveSheet.PivotTables
        ptTable.ColumnGrand = False
        ptTable.RowGrand = False
    Next ptTable
End Sub

➤ Write the following code to disable grand totals for all worksheets:

Sub DisableAllPivotTableGrandTotals()
    Dim ws As Worksheet
    Dim pt As PivotTable
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.ColumnGrand = False
            pt.RowGrand = False
        Next pt
    Next ws
End Sub

➤ Run the code by pressing  F5  and see the results.

Frequently Asked Questions

How to remove the “sum of” in a PivotTable?

Click on the value field in the Values area of the PivotTable Fields. Then select Value Field Settings, and a window should open. From that window, you can change the name of the field and remove “sum of”.

How do I remove a calculated item in a PivotTable?

Click on a cell of the pivot table to activate the pivot table tabs. Then, head to the PivotTable Analyze tab, and find Calculations > Fields, Items, & Sets > Calculated Field. A new window should pop up. In that window, select the calculated item from the Name box. Then, click Delete to delete the selected calculated field.

How to not calculate values in a pivot table?

Remove the fields that you don’t want to see the calculated values of and move them to either the Columns or Rows area in the PivotTable Fields panel. The values will no longer be calculated.

How do I remove zeros from a pivot table?

You can add a filter to the pivot table and uncheck the zeros so that they don’t show up. You can either use a slicer to do this or add a filter from the first cell of the pivot table. Click on the dropdown arrow of Row Labels, and deselect the zero values.

How do I remove .00 in Excel?

Click on the cell from where you want to remove .00 from. Then, in the Home tab, click on the Decrease Decimal icon from the Number group. Any amount of decimal zeros can be removed using this method.

Wrapping Up

In this article, we have learned how to remove grand total from the pivot table. All of the codes used in this article can be seen in action if you open the Excel file provided with this article. Should you have any questions, drop them in the comment section below. Hope to see you in another article soon.