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.
How to Remove Grand Total from Pivot Table: 4 Ways
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.
Here, we have used the Microsoft 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.
In an instant, the PivotTable from table or range wizard appears.
- Next, check the New Worksheet option and press OK.
Now, this opens the PivotTable Fields pane on the right.
- Here, drag the Category and Sales fields into the Rows and Values fields respectively.
- Then, click anywhere in the pivot table and select the Design tool.
- Following this, press the Grand Totals drop-down >> choose the Off for Rows and Columns option.
That’s it the grand total has been removed from the pivot table. It’s that simple!
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.
- Next, drag the Category and Sales fields into the Rows and Values fields respectively.
- Secondly, select the Grand Total (B9:C9 cells) >> right-click on the mouse button >> choose the Remove Grand Total option.
Finally, your result should look like the image given below.
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.
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.
Consequently, your output should appear as the picture shown below.
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.
This opens the Visual Basic Editor in a new window.
📌 Step-02: Insert VBA Code
- Secondly, go to the Insert tab >> select Module.
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
⚡ 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.
📌 Step-03: Running VBA Code
- Now, close the VBA window >> click the Macros button.
This opens the Macro dialog box.
- Following this, click the Run button.
Eventually, the results should look like the screenshot given below.
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.
- Then, drag the Item, Category, and Sales fields into the Rows, Columns, and Values fields respectively.
- In turn, click anywhere in the pivot table >> navigate to the Design tool >> press the Grand Totals option >> choose the On for Rows Only.
This removes the column grand total from the pivot table as shown in the screenshot below.
Read More: [Fixed!] Pivot Table Grand Total Column Not Showing
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.
Thus, 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.
- If you change the name to, for instance, Dataset.Activate you’ll get an error as shown in the screenshot below.
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.
Download Practice Workbook
You can download the practice workbook from the link below.
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
<< Go Back to Pivot Table Grand Total | Pivot Table Calculations | Pivot Table in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
