Remove a Grand Total or Subtotal From a Pivot Table

To remove a grand total from a pivot table, click inside the pivot, go to PivotTable Design > Grand Totals and choose Off for Rows and Columns. You can keep just the row or just the column total, and subtotals have their own switch next to it. This guide is for anyone tidying a pivot table for a report, a slide or a copy-and-paste into another file.

The example pivot table#

Every pivot table gets grand totals by default: a Grand Total row at the bottom and, if you have a field in Columns, a Grand Total column on the right. Here is a pivot of sales by region and product, built from seven rows of data:

The right-hand column totals each region. The bottom row totals each product. The corner cell, 15,300, is the total of everything. If you're new to pivots, the beginner's guide to pivot tables shows how to build this one.

How to remove the grand total from a pivot table#

The quickest way is the Grand Totals button on the ribbon:

1. Click any cell inside the pivot table. The pivot tabs appear on the ribbon.
2. Open the PivotTable Design tab. In older versions it's called Design, under PivotTable Tools. It's the same tab on a Mac.
3. Click Grand Totals at the left of the ribbon.
4. Choose Off for Rows and Columns.

Both the Grand Total row and the Grand Total column disappear. The region and product numbers stay exactly the same; only the totals are hidden.

You can also right-click (on a Mac, Control-click) any cell that says Grand Total and choose Remove Grand Total. That removes only the total you clicked, the row or the column.

A third route is right-click > PivotTable Options > Totals & Filters. Untick Show grand totals for rows and Show grand totals for columns.

The four Grand Totals options explained#

The Grand Totals menu has four choices, and two of them have confusing names:

"Rows" means the totals for each row, which Excel shows as a column on the right. "Columns" means the totals for each column, which Excel shows as a row at the bottom. So to drop the bottom row but keep the right-hand column, choose On for Rows Only. If you pick the wrong one, just choose again: nothing is lost.

How to remove subtotals from a pivot table#

Subtotals appear when you have two or more fields in Rows, for example Region and then Product. Each region gets its own subtotal line. To hide them all:

1. Click inside the pivot table.
2. Go to PivotTable Design > Subtotals.
3. Choose Do Not Show Subtotals.

The same menu has Show all Subtotals at Bottom of Group and Show all Subtotals at Top of Group, if you only want to move them.

To hide the subtotal for one field and keep the others, right-click any item of that field (such as "North") and untick Subtotal "Region". Or right-click, choose Field Settings, and set Subtotals to None.

Hiding subtotals and grand totals together, plus the tabular layout, gives you a flat table that pastes cleanly into other files. The guide to pivot table tabular form shows that layout step by step.

What changes when the totals are hidden?#

Hiding totals only changes what you see. The other numbers, the source data and the field layout stay the same. A few things are worth knowing:

• Percent of Grand Total still works. If you use Show Values As > % of Grand Total, each cell is still divided by 15,300, even though that total is no longer shown. The percent of total guide and the article on Show Values As options explain these calculations.
• Refreshing keeps your choice. When you refresh (Windows: Alt+F5 for the active pivot), the totals stay off. See how to refresh a pivot table.
• Formulas that read a hidden total break. A GETPIVOTDATA formula that points at the grand total returns #REF! once that total is hidden, because the value is no longer visible. The GETPIVOTDATA guide covers this error.
• Sorting still works on the remaining values. The pivot table sorting guide shows how.

Need one total back? Use a formula#

Sometimes you hide the totals for a clean layout but still need one number, such as North's total, next to the report. You don't have to switch the totals back on. A SUMIFS formula on the source data gives the same number:

=SUMIFS(C2:C8,A2:A8,"North")

Here C2:C8 is the Sales column and A2:A8 is the Region column. The result is 7,500 (4,200 + 1,800 + 1,500), the same as North's row total in the pivot. For the overall total, =SUM(C2:C8) returns 15,300.

The formula updates the moment the data changes, while the pivot waits for a refresh. That's one reason analysts often pair a pivot with a few SUMIFS checks.

Practise it#

Practise pivot table subtotals and totals in a live spreadsheet: you'll nest two row fields, show values as a percent of the grand total, add new rows to the source and see when SUMIFS is the better tool, with each step checked instantly. Try the SUMIFS exercise on this page first.