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.
Download Excel Workbook
5 Easy Ways to Collapse the Table to Show the Grand Totals Only
Excel Pivot Table Insertion (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 an 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 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. And 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
1 – take forward the macro by setting the Sub name.
2 – declare the variable and assign the initial value to statements. The row area gets deducted by 1, as the last field can’t be expanded.
Step 3: Run the macro by pressing F5. Then return to the worksheet. You see the entire table get collapsed to fields showing only the totals.
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.
Do check out our awesome website, Exceldemy, to find interesting articles on Excel.