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

Pivot Tables contain multiple fields with multiple items. Often, users only need specific Pivot Fields without any details. Therefore, users collapse the table to show the Grand Totals only. There are many ways to collapse a table, such as Double Clicking, enabling Expand/Collapse Button, selecting Context Menu options, using Keyboard Shortcuts, and VBA Macro.

Let’s say we have an inserted Pivot Table using source data as shown in the following image. Now, we want to collapse the table to display only the Totals.

In this article, we demonstrate multiple methods to collapse the table to show the Grand Totals only.

How to Collapse the Table to Show the Grand Totals Only: 5 Easy Ways

Inserting an Excel Pivot Table (Insert > Pivot Tables) enables users to customize data reports to show or hide details regarding entries and Totals (i.e., Subtotals or Grand Totals). Firstly, to display the Grand Totals, go through:

➤ Place the Cursor on any cell within the Pivot Table. The PivotTable Analyze and Design tabs appear.

➤ Click on Design. Then go to the Grand Total section.

➤ Select any of the last 3 options to display the Grand Totals Field inside the Table.

Repeat the same process as Grand Totals to display the Subtotals within the Pivot Table.

Go through the below section to be able to execute the collapse of the table to display Grand Totals only.

Method 1: Using Double-Click to Collapse the Table to Show the Grand Totals Only

After displaying the Grand Totals and Subtotals fields in the table, users may want to hide the details. As a result, users need to collapse the table only showing Grand Totals.

Steps: Double-click on any field (i.e., Bars, Cookies, or etc.) to hide or collapse the product details.

🔺 Execute the previous step for others and you get a depiction similar to the image below.

Method 2: Enabling Expand/Collapse Button to Collapse Items Within Table

Another alternative way to collapse the table is to enable Buttons, then use them to collapse or expand data details.

Step 1: Highlighting any Pivot Table cells, go to PivotTable Analyze > Show > Click on Buttons.

Step 2: Excel displays the Expand/Collapse Buttons immediately. Click on them to expand/collapse the table.

🔺 The final outcome may look like the below screenshot.

Method 3: Selecting Context Menu Option to Show Grand Totals Only

Typical Pivot Tables offer multiple Context Menu options to conduct operations from. One of them is Expand/Collapse.

Steps: Right-click on any fields. The Context Menu appears. Click Expand/Collapse > Collapse Entire Field.

🔺 Selecting the option collapses the entire table, as shown in the picture below.

Method 4: Using Keyboard Shortcuts to Collapse Table Items

Keyboard Shortcuts are effective when swift-outcomes are needed. Users can use ALT+A+H key to collapse the entire table to show the Grand Totals only.

Step 1: In an Excel worksheet that contains the Pivot Table, press ALT. Excel displays the key command options.

Step 2: Afterwards, hit A to select the Data tab.

Step 3: Finally, press H to collapse the table.

Method 5: Running a VBA Macro to Display Grand Totals Only

As we showed earlier, the pivot table looks like the latter image. Users want to collapse the table to hide the data details.

Step 1: Use ALT+F11 or go to Developer > Visual Basic to open the Microsoft Visual Basic window. In the window, insert a Module by clicking Insert > Module.

Step 2: Paste the following macro in the module.

Sub Collapse_Totals_Only()
Dim mPT As PivotTable
Dim mPF As PivotField
Dim mPI As PivotItem
Dim mFieldCount As Long
Dim mPosition As Long
Set mPT = ActiveSheet.PivotTables(1)
mFieldCount = mPT.RowFields.Count - 1
For mPosition = mFieldCount To 1 Step -1
 For Each mPF In mPT.RowFields
  If mPF.Position = mPosition Then
   For Each mPI In mPF.PivotItems
    If mPI.ShowDetail = True Then
     mPF.ShowDetail = False
     Exit Sub
    End If
   Next mPI
  End If
 Next mPF
Next mPosition
End Sub

Step 3: Run the macro by pressing F5.  Then return to the worksheet. You see the entire table collapse to fields showing only the totals.

Download Excel Workbook

Conclusion

This article demonstrates multiple ways to collapse the table to show the Grand Totals only. Quick methods such as using double-click and Keyboard Shortcuts, enabling Expand/Collapse Buttons, selecting Context Menu options, and also running a VBA macro are put to test. We hope these methods help you deal with Table Totals. Comment if you have further inquiries or have anything to add.

Related Articles

• How to Show Grand Total in Pivot Table
• How to Remove Grand Total from Pivot Table
• [Fixed!] Pivot Table Grand Total Column Not Showing

<< Go Back to Pivot Table Grand Total | Pivot Table Calculations | Pivot Table in Excel | Learn Excel