How to Remove Subtotals in Excel Pivot Table [4 Methods]

Excel Pivot Tables are indispensable tools for summarizing and analyzing data, allowing users to organize dynamically and present information. However, default settings often include subtotals, which may only sometimes align with your reporting needs. In this article, I will guide you to remove subtotals in the Excel Pivot Table in 4 methods. Let’s begin to learn all the methods of removing subtotals from the Pivot Table.

4 Methods to Remove Subtotals in Pivot Table in Excel

In Excel, subtotals in a pivot table can be removed using various methods. Here are 4 different ways to achieve this:

1. Remove Subtotals from Excel Ribbon

The dataset contains the sales figures for each product. In addition, there are the sales figures, the categories, and the number of products. Now, I am going to insert and create a Pivot Table.
Now, select the data > Insert tab > PivotTable dropdown > For Table/Range to create a Pivot Table in Excel. Then, select the location to place the Pivot Table. Next, organize the Pivot Table showing the subtotals.
In this Pivot Table, the subtotals are at the top of each item in Excel. These are the default settings of the Excel Pivot Table.
Use the Excel Ribbon option to remove the subtotals from the Pivot Table. Go through the steps below:

1. Select anywhere in the Pivot Table.
2. Go to the Design tab in the PivotTable Tools.
3. Navigate to the Layout group > Subtotals dropdown > “Do Not Show Subtotals”.
   

After clicking on the option, it will remove the subtotals from the Pivot Table.

2. Remove Subtotals from the Context Menu

You can remove the subtotals with only one click from the Context Menu. Follow the steps below:

1. Select the cell in the first column in the Pivot Table.
2. Right-click on the selected cell.
3. Click on the Subtotal option.
   

By following these steps, you will be able to remove the subtotals from the Excel Pivot Table.

3. Remove Subtotals from PivotTable Field Settings

Do a little change in the Excel Pivot Table. Then, it will help to erase the subtotals from the Excel Pivot Table. To do that, go through the procedure below:

1. Select anywhere in the Excel Pivot Table.
2. Go to the Analyze tab in the PivotTable Tools.
3. In the Active Field group, click on the Field Settings.
   
4. In the Field Settings dialog box, go to the Subtotals & Filters.
5. Choose None in the Subtotals section and click OK.
   

This will remove the subtotals from the Pivot Table immediately.

4. Remove Subtotals from Excel Pivot Table Using VBA

Remove subtotals using a VBA code in the Excel Pivot Table. Now, follow the steps below:

1. Go to the sheet’s name and right-click on that.
2. Click on View Code to open Visual Basic Editor.
   
3. Navigate to the Insert tab > Module option in the Visual Basic Editor window.
   
4. Copy the VBA code and paste it into the Module.

   Sub RemovePivotSubtotals()
   On Error Resume Next
   Dim pt As PivotTable
   
   Set pt = ActiveSheet.PivotTables("PivotTable1") ' Change "PivotTable1" to the name of your PivotTable
   On Error GoTo 0
   If pt Is Nothing Then
   MsgBox "PivotTable not found.", vbExclamation
   Exit Sub
   
   End If
   Dim field As PivotField
   For Each field In pt.PivotFields
   field.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
   Next field
   
   End Sub

5. Press ALT+F8 to open the Macro dialog box.
   
6. Click Run.
   

In this Pivot Table, you have removed the subtotals from the Pivot Table with a VBA Code.

How to Hide the Row of Subtotals in Excel Pivot Table

You can just hide the rows containing the subtotals. However, it won’t remove the subtotals permanently. So, go through the following steps:

1. Click the row containing subtotals.
2. Press CTRL to select more.
3. Right-click on that and click Hide.
   

By doing the steps, you will hide the rows rather than remove the subtotals from the Pivot Table permanently.

Conclusion

In conclusion, managing subtotals in Excel Pivot Tables is a crucial aspect of data analysis and presentation. When you need to remove, edit, or restore subtotals, Excel provides intuitive tools to streamline these processes. In this article, you will have a guideline to remove subtotals from the Excel Pivot Table in 5 methods. You will get a VBA code from those methods. So, have the guidelines and resolve issues regarding removing subtotals from the Excel Pivot Table.

Frequently Asked Questions

What is subtotal in Pivot Table?

In a Pivot Table, a subtotal is a summary or aggregation of data within a specific grouping or category. It represents the total of values for a subset of data, often corresponding to a particular row or column grouping in the Pivot Table. Subtotals provide a quick overview of the aggregated data, aiding in the analysis of trends and patterns. They can be displayed for individual rows or columns, allowing users to examine the summarized information within the context of the overall dataset. Subtotals are a valuable feature in Pivot Tables, enhancing the ability to comprehend and interpret complex data sets efficiently.

How do I edit subtotals?

To edit subtotals in a Pivot Table, follow these steps:

1. Open the Excel workbook containing the Pivot Table, and click on any cell within the Pivot Table to activate it.
2. Navigate to the Design tab on the Excel ribbon. This tab is specific to Pivot Table tools.
3. In the Design tab, locate the Subtotals dropdown menu. Click on it to reveal options for customizing subtotals.
4. From the Subtotals dropdown, choose the option that suits your needs. If you wish to remove subtotals altogether, select Do Not Show Subtotals.
5. Optionally, you can also manage grand totals. Use the Grand Totals option in the Design tab to include or exclude grand totals for rows and columns.
6. After making changes, it’s advisable to refresh the Pivot Table to reflect the modifications. Right-click on the Pivot Table and choose the Refresh option.

By following these steps, you can easily edit and customize subtotals in your Pivot Table, tailoring the presentation of data to meet your analysis requirements.

How to restore subtotals in a Pivot Table in Excel?

To restore subtotals in a Pivot Table in Excel, follow these steps:

1. Open your Excel workbook containing the Pivot Table, and click on any cell within the Pivot Table to activate it.
2. Navigate to the Design tab on the Excel ribbon. This tab holds tools specific to Pivot Tables.
3. In the Design tab, locate the Subtotals dropdown menu.
4. From the Subtotals dropdown, choose the desired option based on your analysis needs. You can select Automatic, “Show all Subtotals at Top of Group,” or “Show all Subtotals at Bottom of Group“.
5. After making changes, refresh the Pivot Table to apply the restored subtotals. Right-click on the Pivot Table and choose the Refresh option.

By following these steps, you can easily restore subtotals in your Pivot Table, tailoring the presentation of data to meet your analytical requirements.