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.

Dataset-Remove Subtotal in Pivot Table

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.

Extra Tabs


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).

Design Option-Remove Subtotal in Pivot Table

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

Design Tool result


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.

Context Menu-Remove Subtotal in Pivot Table

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

Context Menu Outcome


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).

Field setting-Remove Subtotal in Pivot Table

🔼 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.

Alternative option

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.

Field Setting window

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

Outcome

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.

VBA-How to Remove Subtotal in Pivot Table

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

macro

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.

Macro outcome


 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.

Hide Rows-Remove Subtotal in Pivot Table

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

Outcome

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

Final Outcome

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.

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