How to Remove Grand Total from Pivot Table

  • Home
  • / How to Remove Grand Total from Pivot Table

With a pivot table in Excel, you can maintain your dashboards as well as reports while saving time for other activities. Do you know that when you add a field in your pivot table, you will get a subtotal filed as well? For whatever reason, you may want to hide or remove grand totals from a pivot table.

As you know that while creating a pivot table, you will get grand totals by default. That’s why sometimes it might be possible that you don’t need that grand total column and row as they might be irrelevant to the settings of your reports.

While creating a pivot table you will see:

An irrelevant grand total column that might be not important to your report does not need to be there. Let’s dive in to find the steps to remove grand total from a pivot table.

  1. At first, you need to activate the Design Tab. For this, you have to click anywhere on the pivot table.  
  1. Next, click on the Design tab that appeared on the ribbon and find Grand Totals.
  1. You will see a few options to choose from.
  1. From these options, you need to choose the right one to add or remove grand totals.
  1. Off for Rows and Columns: Choosing this option can remove Grand totals for both rows and columns.
  2. On for Rows and Columns: With this option, you can show your hidden or removed Grand totals.
  3. On for Rows Only: Choose this option only if you need to show Grand totals for rows.
  4. On for Columns only: This option can show the Grand totals for your columns only.

How to Remove All Pivot Table Grand Totals at Once

Following these steps will help you remove all subtotals from the Pivot table at once.

  1. By clicking anywhere in your pivot table, you can activate the Pivot Table Tools context tab available on the ribbon.
  2. Next, you have to choose the Design tab given on the ribbon.
  3. Now, click the subtotals icon and choose Do Not Show Subtotals.

See the following image that excludes subtotals easily.

How to Remove the Subtotals for Only One Field

At times, you may need to remove the subtotals for just one field. In that case, you have to follow the steps given below:

  1. Within the given field, right-click any value.
  2. Choose Field Settings. The Field Settings dialogue box will pop up.
  3. Now, select the None option given under Subtotals. See the image given below for a better understanding.
  1. Press OK to apply all the changes.

How to Remove Pivot Table Grand Totals

It might be possible that sometimes you need to remove Grand totals from pivot table. For this, follow the given steps: 

  1. Right-click on your pivot table. 
  2. Choose PivotTable options. A dialogue box will pop up. 
  3. Open the Totals & Filters tab.  
  4. To deselect the option, click on the Show Grand Totals for Rows. 
  5. To deselect the option, click on the Show Grand Totals for Columns. 

How to Hide the Original Grand Total

The default grand total given at the bottom can be turned off with the new Grand Total given at the top.

For Excel 2007 and Excel 2010:

  1. In the pivot table, choose the cell and in the Excel Ribbon given under the PivotTable Tools, you need to click on the Design tab.
  2. Click Grand Totals and choose the On for Rows Only option.

For Excel 2003:

  1. In the Pivot Table, you need to right-click on a cell and choose Table Options.
  2. Now, remove the checkmark given for Grand Totals for Columns.
  3. Click OK.

How to Add a Grand Total Field

  1. Add a new column in your data and format the column directing to “Grand Total.”
  2. Let the column blank apart from the heading.
  1. In the Field List, you will get to see a new field by refreshing the pivot table. 
  2. Insert the Grand Total field in the pivot table as the first field in the Row area. 
  3. The field label appears as a blank that’s why you need to choose the label cell and type a space character. 

To Sum Up

In order to Remove Grand Totals from Pivot table, you have plenty of options given above in this article. Now, it’s up to you, what your project requirements are and how you want to set it up. If you don’t want to remove the grand total column or row permanently, you have options to hide it. The grand total is a by default column that appears as a result of summing other values given in the data set.

Write your comment Here