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

Excel’s Pivot Table is an effective tool for sorting and grouping data. Generally speaking, pivot tables add an extra grand total field when it displays the dataset. However, this may sometimes be irrelevant and you may want to remove it altogether. Keeping this in mind, this article shows 4 ways how to remove grand total from pivot table.


Download Practice Workbook

You can download the practice workbook from the link below.


4 Ways to Remove Grand Total from Pivot Table

Throughout this article, we’ll consider the following dataset in B4:D14 cells which shows the Item names, their Category, and the Sales in USD. Therefore, without further delay, let’s see each method one by one.

Dataset

Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


Method-1: Using Design Tool to Remove Grand Total from Pivot Table

We’ll start things with the most obvious way to remove grand total from the pivot table i.e. using the contextual Design tool. So, let’s see it in action.

📌 Steps:

  • At the very beginning, select the dataset (B4:D14 cells) >> go to the Insert tab >> click the PivotTable button.

Inserting Pivot Table

In an instant, the PivotTable from table or range wizard appears.

  • Next, check the New Worksheet option and press OK.

How to Remove Grand Total from Pivot Table Using Design Tool

Now, this opens the  PivotTable Fields pane on the right.

  • Here, drag the Category and Sales fields into the Rows and Values fields respectively.

How to Remove Grand Total from Pivot Table Using Design Tool

  • Then, click anywhere in the pivot table and select the Design tool.

How to Remove Grand Total from Pivot Table Using Design Tool

  • Following this, press the Grand Totals drop-down >> choose the Off for Rows and Columns option.

How to Remove Grand Total from Pivot Table Using Design Tool

That’s it the grand total has been removed from the pivot table. It’s that simple!

How to Remove Grand Total from Pivot Table Using Design Tool

Read More: How to Use Excel Formula to Calculate Percentage of Grand Total


Method-2: Employing Remove Grand Total Option

For our next method, we’ll use the aptly named Remove Grand Total option. Therefore, just follow these steps.

📌 Steps:

  • To begin with, select the dataset (B4:D14 cells) >> move to the Insert tab >> click the PivotTable button.

Now, this opens the PivotTable from table or range dialog box.

  • Then, check the New Worksheet option >> click the OK button.

Inserting Pivot Table

  • Next, drag the Category and Sales fields into the Rows and Values fields respectively.

How to Remove Grand Total from Pivot Table Using Remove Grand Total option

  • Secondly, select the Grand Total (B9:C9 cells) >> right-click on the mouse button >> choose the Remove Grand Total option.

How to Remove Grand Total from Pivot Table Using Remove Grand Total option

Finally, your result should look like the image given below.

How to Remove Grand Total from Pivot Table Using Remove Grand Total Option

Read More: How to Make Subtotal and Grand Total in Excel (4 Methods)


Method-3: Utilizing PivotTable Options to Remove Grand Total

Another way to remove grand total from the pivot table is to use the PivotTable Options. So, let’s see the process in detail.

📌 Steps:

  • Firstly, generate the pivot table by following the steps shown in the previous methods.
  • Secondly, select anywhere in the pivot table >> right-click on the mouse >> click the PivotTable Options.

Using PivotTables Options

Subsequently, a PivotTable Options wizard pops up.

  • Now, choose the Totals and Filters tab >> un-check the Show grand totals for rows and Show grand totals for columns options >> press OK.

How to Remove Grand Total from Pivot Table Using PivotTables Options

Consequently, your output should appear as the picture shown below.

How to Remove Grand Total from Pivot Table Using PivotTables Options

Read More: How to Show Grand Total in Pivot Table (3 Easy Methods)


Method-4: Applying VBA Code to Remove Grand Total from Pivot Table

Admittedly, removing the grand total is easy, however, if you often need to do this then you may consider the VBA code below. So, just follow along.


📌 Step-01: Open Visual Basic Editor

  • Firstly, navigate to the Developer > Visual Basic.

Inserting VBA Code

This opens the Visual Basic Editor in a new window.


📌 Step-02: Insert VBA Code

  • Secondly, go to the Insert tab >> select Module.

How to Remove Grand Total from Pivot Table Applying VBA Code

For your ease of reference, you can 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

How to Remove Grand Total from Pivot Table Applying VBA Code

Code Breakdown:

Now, I will explain the VBA code used to remove grand total. Here, the code is divided into 2 steps.

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

Code Explanation


📌 Step-03: Running VBA Code

  • Now, close the VBA window >> click the Macros button.

This opens the Macros dialog box.

  • Following this, click the Run button.

How to Remove Grand Total from Pivot Table Applying VBA Code

Eventually, the results should look like the screenshot given below.

How to Remove Grand Total from Pivot Table Applying VBA Code

Read More: How to Collapse the Table to Show the Grand Totals Only (5 Ways)


Remove Column Grand Total from Pivot Table

So far we’ve discussed removing the grand total completely from the pivot table. What if you want to remove only the column grand total? Then you’re in luck since the following method answers this question. So, let’s begin.

📌 Steps:

  • To start, select the dataset (B4:D14 cells) >> move to the Insert tab >> click the PivotTable button >> then, check the New Worksheet option.

Inserting Pivot Table

  • Then, drag the Item, Category, and Sales fields into the Rows, Columns, and Values fields respectively.

How to Remove Column Grand Total from Pivot Table

  • In turn, click anywhere in the pivot table >> navigate to the Design tool >> press the Grand Totals option >> choose the On for Rows Only.

How to Remove Column Grand Total from Pivot Table

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

How to Remove Column Grand Total from Pivot Table


Remove Row Grand Total from Pivot Table

By now you’ve probably figured out that you can also remove the row grand total. Just follow along.

📌 Steps:

  • Likewise, insert the pivot table as shown in the previous method.
  • Next, select any cell in the pivot table >> go to the Design tool >> click the Grand Totals drop-down >> select the  On for Columns Only.

How to Remove Row Grand Total from Pivot Table

Thus, the row grand total has been removed from the pivot table.

How to Remove Row Grand Total from 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.

VBA Code

  • If you change the name to, for instance, Dataset.Activate you’ll get an error as shown in the screenshot below.

VBA Code


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Conclusion

I hope this article helps you understand how to remove grand total from pivot table. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo