How to Remove Subtotal in Pivot Table (5 Useful Ways)

Pivot Tables are effective when it comes to sorting or grouping data. In general, Pivot Tables adds an extra field as Subtotal when it displays any dataset as Pivot Table. Sometimes it’s annoying or unnecessary to have Subtotal fields in Pivot Tables. As a result, users remove Subtotal from Pivot Table entries. There are many Pivot Table features and options as well as VBA Macros that easily remove the Subtotal entries.

In this article, we demonstrate multiple features and options to remove Subtotal in Pivot Table.

Download Excel Workbook

5 Easy Ways to Remove Subtotal in Pivot Table

While working with Pivot Table, every time we place the mouse cursor within the Pivot Table, Excel shows PivotTable Analyze and Design tab along with the other tabs. Excel offers multiple options to alter Fields, Display, or Orientation of a Pivot Table. We’ll use these two tabs in our latter section to remove the Subtotal entries from Pivot Table.

Method 1: Using Pivot Table Design Tool to Remove Subtotal

Excel displays PivotTable Analyze and Design tab whenever we work with the Pivot Table. Pivot Table provides a Subtotal section in the Design tab. From there we can choose the offered options not to show Subtotal in the Pivot Table entries.

Step 1:  Excel shows the Design tab whenever you select a cell within the Pivot Table. Go to Design > Click on Subtotal > Select Do Not Show Subtotals (from the Subtotal options).

Step 2: After selecting Do Not Show Subtotals, Excel removes all the Subtotal fields from the Pivot Table as shown in the below picture.

Method 2: Remove Subtotal by Deselecting Context Menu Option

If we Right-Click on any City entries, we see multiple options and Subtotal City will be one of them. We can unselect the option to remove the Subtotal entries.

Steps: Right-Click on any City Entry. The Context Menu appears. From the Context Menu, Deselect the Subtotal City option.

🔼 Within seconds Excel removes Subtotal fields from the Pivot Table as depicted in the below image.

Method 3: Remove Subtotal in Pivot Table Using Field Setting Options

As we said earlier Excel shows two Extra tabs PivotTable Analyze and Design to further modify the Pivot Tables. We can use the Pivot Table Analyze tab to alter the Field Settings.

Step 1: Select the PivotTable Analyze tab > Click on Field Setting (in the Active Field section).

🔼 Alternatively, you can Right-Click on any City cell entries (i.e., Subtotal of whom) to bring out the Context Menu with Field Setting in its options similar to the screenshot below. Otherwise, Excel displays Value Field Setting in case you Right-Click on Value entries.

Step 2: The Field Setting window appears. From the window,

➧ Choose the Subtotals & Filters section (in case Excel automatically doesn’t select).

➧ Under Subtotals, Mark None.

➧ Click on OK.

🔼 Afterward, Excel displays all the fields except Subtotal of the Cities in the Pivot Table.

Read More: How to Use SUBTOTAL in Excel with Filters (With Quick Steps)

Method 4: Using VBA Macro to Remove Subtotal in Pivot Table

VBA Macros are effective when we need specific outcome-oriented results. A macro can simply remove all the Subtotal fields from a Pivot Table.

Step 1: Use ALT+F11 or go to the Developer tab > Visual Basic (in the Code section) to open Microsoft Visual Basic window. In the Window, Click on Insert > Select Module to insert a Module.

Step 2: Paste the below macro in the Module.

Sub SubtotalRemoval()
Dim mPT As PivotTable
Dim mPF As PivotField
On Error Resume Next
For Each mPT In Application.ActiveSheet.PivotTables
For Each mPF In mPT.PivotFields
mPF.Subtotals(1) = True
mPF.Subtotals(1) = False
Next
Next
End Sub

In the macro, the macro declares mPT and mPF as Pivot Table and Pivot Field respectively. Then using Application.ActiveSheet statement to assign Pivot Table and Pivot Fields. Also, the macro runs a VBA FOR loop to remove Subtotal values from the fields of the Pivot Table.

Step 3: To run the macro, use the F5 key. Then, return to the Worksheet, and you see Excel removes all the Subtotal fields from the Pivot Table similar to the following image.

 Method 5: Hiding Subtotal Rows in Pivot Table

Excel’s typical operations for rows such as Hide, Unhide, Insert, Height, etc. are also available within the Pivot Table range. So, we can Hide or Unhide any Pivot Table rows.

Step 1: Place the cursor on City Subtotal’s Row Numbers then Right-Click on it. Excel brings out the Context Menu as depicted in the below image. Select Hide from the Context Menu options.

🔼 Instantly, you see Excel removes the Subtotal Row (i.e., Row Number 7).

Step 2: Repeat Step 1 for other Subtotal Rows and you see a final image similar to the following picture.

Hiding individual rows using Context Menu options is tiring for huge datasets. However, you can use this method for Pivot Tables containing a handful of rows.

Conclusion

In this article, we use the Pivot Table Options, Excel Feature, as well as VBA Macro to remove Subtotal entries. Each method we demonstrate is unique in its way and doesn’t require any specific data type to suit well. Hope you find these above-mentioned methods handy to work with. Comment, if you have further inquiries or have anything to add.