Pivot tables are extremely useful in analyzing and summarizing a large amount of data with a few clicks.
When you create a Pivot Table in Excel, by default it would add the Grand Total values in the last row and the last column.
And in case you have sub-fields within fields in the pivot table, it would also add sub-totals for them (as highlighted below).
While in most cases, this is the required format, some users may not want to show the grand total altogether or may want to selectively switch off the grand total only for the row or the column.
In this short tutorial, I will show you three simple ways you can use to quickly remove grand totals in Pivot tables in Excel.
Removing Grand Totals in Pivot Table
Below is the Pivot Table example that I will be using while showcasing how to remove grand totals from a pivot table.
As you can see, there are three types of grand totals in this pivot table example:
- A grand total row at the bottom of the pivot table
- A grand total column at the right of the PivotTable
- A sub-total row for each quarter (while this is technically not a grand total row but a subtotal row, I will still include this in our tutorial and show you how to remove this if you want)
Now let’s look at the options that you can use to quickly remove grand totals from the Pivot Table.
Using the Design Tab
When you click on any cell in the pivot table, you will see that the Design tab becomes available in the ribbon.
This is a contextual tab, which means that it would only become visible when you select any cell in the pivot table.
Below are the steps to remove the grand totals using the design tab:
- Click any cell in the Pivot Table
- Click the Design tab in the ribbon
- In the Layout group, click on the ‘Grand Totals’ option
- Click on ‘Off for Rows and Columns’
The above steps would remove the grand total row and column and you will get a pivot table as shown below:
Also, note that the grand totals options in the design tab give you some flexibility with the below four options:
- Off for Rows and Columns – this is the option we used in our example above, and it would remove the grand total row as well as the column
- On for Rows and Columns – in case you have removed the grand total row/column, and you want it back, you can use this option
- On for Rows only – this would only keep the grand total for the rows, and the grand total for the columns would be turned off
- On for Columns only – this would only keep the grand total for the columns, and the grand total for the Rose would be turned off
Also read: Standard Deviation in Pivot Tables in Excel
Using the ‘Remove Grand Total’ Options When You Right-Click
Another quick way to remove grand total row or column would be to select the cell that contains the text Gand Total, and right-click on it.
In the options that show when you right-click, click on the ‘Remove Grand Total’ option.
It would remove the grand total for that selected row or column only.
This means that if you want to remove the Grand Totals from the row as well as the column, you would have to do this twice – first, using the cell that says Grand Total in the column and then the same thing for the row.
Also read: Connect Slicer to Multiple Pivot Tables
Using the Pivot Table Options Dialog Box
And the third way you can use to remove the grand totals from the Pivot table is by using the pivot table options.
Below are the steps to do this:
- Select any cell in the Pivot table
- Click on the ‘Pivot Table Analyze’ tab in the ribbon
- In the Pivot Table group, click on the Options icon. This will open the Pivot Table Options dialog box
- Select the ‘Totals & Filters’ tab
- Under the Grand Total options, uncheck both the options to remove the Grand Total rows as well as the column
- Click OK
In case you only want to remove the Grand total row or the column, you can only uncheck that option in Step 5.
Also read: Change Count to Sum in Excel Pivot Table
Removing Subtotal Row/Columns From Pivot Table
In any Pivot Table, you can only have two grand total values (one is a grand total row, and the other one is the grand total column).
However, you would also notice that there are subtotal rows and columns in your pivot table. In our example, you can see that there are subtotal rows for each quarter.
While this is useful, some users find it cluttering the pivot table, and want to get rid of it.
Below are the steps to remove the subtotal for a specific field in Pivot Table:
- Select any cell with the row label (or the column label) of the ‘Subtotal’ row/column you want to remove. In this case, I would select the cell that contains Q1
- Click the PivotTable Analyze tab in the ribbon
- In the Active Field group, click on the ‘Field Settings’ option. This will open the Field Settings dialog box
- Select the Subtotals & Filters tab
- Under the Subtotals option, select ‘None’
- Click OK
The above steps would remove the Subtotal values for the selected field (which is Quarters in our example).
In this tutorial, I showed you three simple ways you can use to remove Grand Totals from pivot tables in Excel. Additionally, I also covered how to remove subtotals for specific fields in the pivot table.
I hope you found this tutorial helpful.
Here are some other Excel tutorials you may also find useful:
