ExcelInsider Team
Pivot tables are handy for doing calculations in Microsoft Excel. In a pivot table, Excel provides subtotals for every field and grand total at the bottom. In a big spreadsheet, having subtotals can be annoying as they might obstruct the view of other calculations. In this article, we will learn how to remove subtotal in pivot table so that we don’t face that kind of issue anymore.
➤ Select a cell of the pivot table and go to the Design tab.
➤ From the Layout group, find the Subtotals icon, and click on Do Not Show Subtotals.
That was easy, but that method removes all subtotals from the entire table. But what if you wanted to remove subtotals for each field? Don’t worry, we have a solution for that as well. Therefore, keep reading the article so that you can get a clear idea of how to remove subtotals in pivot tables properly.
Removing All Subtotals in a Pivot Table
In today’s datasheet, we have sales performance data of a certain organization. The table contains the names of the salespeople, the regions they have sold certain products in, the names of the products, the units, and the revenue from those sales. We are going to analyze them further in a pivot table and adjust subtotals as needed.
After converting the regular table to a pivot table, the output looks like the image below. The subtitles are at the top, which is the default for pivot tables.
Follow the steps below to remove the subtotals:
➤ Click on any cell of the pivot table. This will enable the PivotTable Analyze and Design tabs.
➤ Go to the Design On the left, you will see the Layout group.
➤ Click on the small arrow pointing downwards on the Subtotals icon.
➤ Select Do Not Show Subtotals
➤ Now, all of the subtotals will be removed from the pivot table.
Removing Subtotals of Certain Fields in a Pivot Table
The previous method removed all subtotals in the pivot table. However, we can choose to remove the subtotals of certain fields instead of removing all of the subtotals. Here is how to do it:
➤ Go to the Rows or Columns We are going to the Rows section for this example, but if you want to remove the subtotal for one column in a pivot table, you should go to the Columns section.
➤ Click on the field you want to remove subtotals from to open the context menu. We are choosing “Salesperson” here.
➤ Select “Field Settings” from there.
➤ A new window will pop up. In the “Subtotals & Filters” tab, Automatic should be selected in the Subtotals Select None and hit OK.
➤ Now, as we can see on the table, only the subtotals of the Salesperson field have been removed.
Removing All Subtotals in a Pivot Table Using VBA
Now we are in the advanced section. In this method, we will use some VBA code in order to remove the subtotals of our pivot table. Follow the instructions below:
➤ Make sure you are in the worksheet where the pivot table exists, and press Alt + F11 to open the Visual Basic window.
➤ Go to Insert > Module. A code editor will open.
➤ Write this code in the code editor:
Sub TurnOffAllSubtotalsInPivotTables()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Set ws = ActiveSheet
For Each pt In ws.PivotTables
With pt
For Each pf In .RowFields
pf.Subtotals(1) = False
Next pf
.RefreshTable
End With
Next pt
End Sub
➤ Press F5 (or Fn+F5 for some keyboards) to run the code.
➤ Go back to your worksheet to see the results.
Frequently Asked Questions
How to remove blank total in PivotTable?
Click on a cell of the pivot table and go to the Design tab. From the Layout group, select Blank Rows, and click on Remove Blank Line after Each Item. The blank rows will be removed from the pivot table now.
How to sum in pivot table?
Pivot tables, in general, select the sum function by default. However, if you have received a file from someone that does not include a sum, make sure that the field is in the Values area. Then click on the field, and select Sum from the new window.
How do I remove a pivot table?
Right-click on any cell of the pivot table and select Delete PivotTable. After a confirmation dialog, the pivot table will be removed from the sheet.
How to remove grand total from PivotTable?
Go to the Design tab (enabled after clicking on any cell of the pivot table), and head to the Layout section. From the Grand Totals button, select Off for Rows and Columns to remove grand total from your pivot table.
How to move subtotal in PivotTable to top?
Just like the methods used before, you need to go to the Layout section of the Design tab. Upon entering the Subtotals button, you will find an option called Show all Subtotals at Top of Group. Click on it to move the subtotal to the top. However, by default, a pivot table shows the subtotals at the top.
Wrapping Up
In this article, we have learned how to remove subtotal in pivot table using three different methods. We hope that the article has been useful to you. If you need more clarifications, download the workbook we have used here to see the methods in action. Leave your thoughts under the tutorial, and we will see you in another article soon.
