Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Collapse the Table to Show the Grand Totals Only

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.

Show Grand Totals

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

Show Subtotals

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.

Read More: How to Use Excel Formula to Calculate Percentage of Grand Total


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.

Collapse the Table to Show the Grand Totals Only

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.

Read More: How to Remove Grand Total from Pivot Table (4 Quick Ways)


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.

Collapse the Table to Show the Grand Totals Only

Read More: How to Show Grand Total in Pivot Table (3 Easy Methods)


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.

Keyboard Shortcuts

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

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

Collapse the Table to Show the Grand Totals Only

Read More: [Fixed!] Pivot Table Grand Total Column Not Showing (6 Solutions)


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.

VBA Macro

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

Macro to Collapse the Table to Show the Grand Totals Only

Macro Explanation

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.

3 – use VBA FOR, VBA IF statements to check the Pivot Items’ status and collapse the table to fields.

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.

Collapse the Table to Show the Grand Totals Only

Read More: How to Show Grand Total with Secondary Axis in Pivot Chart


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.

Do check out our awesome website, Exceldemy, to find interesting articles on Excel.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo