How to Collapse the Table to Show the Grand Totals Only – 5 Methods

Pivot Tables contain multiple fields with multiple items. Pivot Fields can be collapsed to show the Grand Totals only.

To collapse the table and display the Totals only:

Collapse the Table to Show the Grand Totals Only

 


Insert an Excel Pivot Table (Insert > Pivot Tables) to customize data reports, to show or hide details regarding entries and Totals (Subtotals or Grand Totals). To display the Grand Totals:

  • Place the Cursor on any cell within the Pivot Table to display the PivotTable Analyze and Design tabs.
  • Click Design and go to the Grand Total section.
  • Select one of the 3 options to display the Grand Totals Field inside the Table.

Show Grand Totals

Repeat the same process to display the Subtotals within the Pivot Table.

Show Subtotals

 


Method 1 – Double-Clicking to Collapse the Table and Show the Grand Totals Only

Steps:

Double-click any field to hide or collapse product details.

  •  Repeat the previous step for other fields.

This is the output.


Method 2 – Enabling the Expand/Collapse Button to Collapse Items Within Table

Step 1:

  • Select any Pivot Table cells and go to PivotTable Analyze > Show > Click Buttons.

Collapse the Table to Show the Grand Totals Only

Step 2:

Excel displays the Expand/Collapse Buttons.

  • Click them to expand/collapse the table.

This is the output.


Method 3 – Selecting the Context Menu Option to Show Grand Totals Only

Steps:

  • Right-click any field to open the Context Menu.
  • Click Expand/Collapse > Collapse Entire Field.

This is the output.

Collapse the Table to Show the Grand Totals Only


Method 4 – Using Keyboard Shortcuts to Collapse Table Items

Step 1:

  • In the Excel worksheet that contains the Pivot Table, press ALT. Excel displays the key command options.

Keyboard Shortcuts

Step 2:

  • Press A to select the Data tab.

Step 3:

  • Press H to collapse the table.

Collapse the Table to Show the Grand Totals Only


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

 

VBA Macro

Step 1:

  • Press ALT+F11 or go to Developer > Visual Basic to open the Microsoft Visual Basic window.
  • Insert a Module by clicking Insert > Module.

Step 2:

  • Enter 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 – activates the macro by setting the Sub name.

2 – declares the variable and assigns the initial value to statements. The row area is deducted by 1, as the last field can’t be expanded.

3 – uses VBA FOR and VBA IF statements to check the Pivot Items’ status and collapses the table to fields.

 

Step 3:

  • Run the macro by pressing F5. 
  • Go back to the worksheet. The entire table collapsed to fields showing the totals only.

Collapse the Table to Show the Grand Totals Only


Download Excel Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo