How to Remove Grand Total from Pivot Table (4 Quick Ways)

We’ll consider the following dataset in B4:D14 cells which shows the Item names, their Category, and the Sales in USD.

Method 1 – Using the Design Tool to Remove the Grand Total from a Pivot Table

Steps:

Select the dataset (B4:D14 cells), go to the Insert tab, and click the PivotTable button.

The PivotTable from table or range wizard appears.

Check the New Worksheet option and press OK.

This opens the PivotTable Fields pane on the right.

Drag the Category and Sales fields into the Rows and Values fields, respectively.

Click anywhere in the pivot table and select the Design tool.

Press the Grand Totals drop-down and choose the Off for Rows and Columns option.

The grand total has been removed from the pivot table.

Method 2 – Using the Remove Grand Total Option

Steps:

Select the dataset (B4:D14 cells), move to the Insert tab, then click the PivotTable button.

This opens the PivotTable from table or range dialog box.

Check the New Worksheet option and click the OK button.

Drag the Category and Sales fields into the Rows and Values fields, respectively.

Select the Grand Total (B9:C9 cells) and right-click on them, then choose the Remove Grand Total option.

Your result should look like the image given below.

Method 3 – Utilizing PivotTable Options to Remove the Grand Total

Steps:

Generate the pivot table.
Select anywhere in the pivot table.
Right-click and select the PivotTable Options.

A PivotTable Options wizard pops up.

Choose the Totals and Filters tab.
Uncheck the Show grand totals for rows and Show grand totals for columns options.
Press OK.

Your output should appear as the picture shown below.

Method 4 – Applying VBA Code to Remove the Grand Total from a Pivot Table

Step 1 – Open the Visual Basic Editor

Navigate to the Developer tab and select Visual Basic.

This opens the Visual Basic Editor in a new window.

Steo 2 – Insert VBA Code

Go to the Insert tab and select Module.

Copy the code from here and paste it into the window as shown below.

Option Explicit
Sub CreatePivotTable()

Dim pvt_tab As PivotTable
Dim pvt_cache As PivotCache

Sheet1.Activate

Set pvt_cache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("B4").CurrentRegion)

Sheets.Add , Sheets(Sheets.Count)

Set pvt_tab = ActiveSheet.PivotTables.Add(pvt_cache, Range("B4"), "Sales_Pivot")

pvt_tab.PivotFields("Category").Orientation = xlRowField
pvt_tab.PivotFields("Sales").Orientation = xlDataField

pvt_tab.ColumnGrand = False
pvt_tab.RowGrand = False

End Sub

⚡ Code Breakdown:

We give the sub-routine a name,
We define the variables.
We activate Sheet1 using the Activate method, and the memory cache is assigned using the PivotCache object.
In the second portion, we insert the PivotTable in a new sheet with the Add method.
We position the PivotTable in the preferred (B4) cell and give it a name. In this case, we’ve named it Sales_Pivot.
We add the Pivot Fields i.e. the Category in the RowField and Sales in the DataField.
We set the ColumnGrand and the RowGrand properties to False.

Step 3 – Running VBA Code

Close the VBA window and click the Macros button.

This opens the Macro dialog box.

Click the Run button.

The results should look like the screenshot given below.

Remove the Column Grand Total from a Pivot Table

Steps:

Select the dataset (B4:D14 cells).
Move to the Insert tab, click the PivotTable button.
Check the New Worksheet option and hit OK.

Drag the Item, Category, and Sales fields into the Rows, Columns, and Values fields, respectively.

Click anywhere in the pivot table, navigate to the Design tool, press the Grand Totals option, and choose On for Rows Only.

This removes the column grand total from the pivot table as shown in the screenshot below.

Remove the Row Grand Total from a Pivot Table

Steps:

Insert the pivot table as shown in the previous method.
Select any cell in the pivot table, go to the Design tool, click the Grand Totals drop-down, and select On for Columns Only.

The row grand total has been removed from the pivot table.

Things to Remember

When using the VBA code, make sure to enter the correct sheet name. In this case, Sheet1 contains the dataset hence we’ve written Sheet1.Activate command.