excel how to remove subtotals from pivot table

Now click the “Clear Outline” option to remove any grouping for that spreadsheet.

In the “Outline” section, click the “Ungroup” drop-down menu.If you don’t want to delete the subtotals, but want to remove all groups to further rearrange your data, do the following: This will ungroup all the data in the spreadsheet, effectively removing any subtotal rows you might have there.In the “Subtotal” menu, click the “Remove All” button.

In the “Outline” section of the top menu, click “Subtotal”.Open the Excel spreadsheet you want to edit.Whether you simply want to remove subtotal rows from your spreadsheet or reorganize the entire sheet, the process is basically the same. Subtotals ( 1 ) = False Next pf pt.Removing Subtotals from Standard Spreadsheets ManualUpdate = True For Each pf In pt.PivotFields pf. Sub NoSubtotals () Dim pt As PivotTable Dim pf As PivotField On Error Resume Next For Each pt In ActiveSheet.PivotTables pt. In our macro, we use this trick to turn off subtotals for every pivot field in the active PivotTable. In this piece of code, we are setting the first Subtotal to True, and then setting it to False. We then set the same Subtotal to False, effectively hiding all Subtotals. This automatically forces the other 11 Subtotal types to False. So when you turn off Subtotals while recording a macro, Excel sets each of the possible Subtotal types to False.Īn alternative way of turning off Subtotals is to first set one of the 12 Subtotals to True. There are 12 instances of False because there are twelve types of Subtotals - Sum, Avg, Count, Min, and Max, just to name a few.

Subtotals = Array ( _ False, False, False, False, False, False, False, False, False, False, False, False )Įxcel passes an array with exactly 12 False settings. If you record a macro while hiding a Subtotal in a PivotTable, Excel produces code similar to this: ActiveSheet. But if you're constantly hiding subtotals, you can save a little time by automating the process with a simple macro. We can of course manually remove Subtotals. When you create a PivotTable, Excel includes subtotals by default.

Excel how to remove subtotals from pivot table