Written by Allen Wyatt (last updated June 25, 2018)
This tip applies to Excel 97, 2000, 2002, and 2003
Shairal develops PivotTables on a daily basis, using various data sources such as Excel lists, Access tables, and OLAP data. One of the most irritating things he deals with is suppressing the automatic subtotal function on each field, one at a time. This can be time consuming depending on the number of fields he's used. Shairal wondered if it might be easier to use a macro to suppress the subtotals for all the fields at once.
The answer is that it would be easier to use a macro. (That is what macros are for—to take care of the tedious things you tire of.) Rather than reinvent the wheel, however, a good solution is to consider the following code, adapted from Microsoft MVP Debra Daglisesh's site:
Sub NoSubtotals()
'turns off subtotals in pivot table
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
Next pt
End Sub
Just display the PivotTable you want to affect, and then run the macro. The subtotals for all the fields in the PivotTable are suppressed at once. The original for this code is available here, at Debra's site:
http://www.contextures.com/xlPivot03.html#Subtotals
The site also contains some other good information for working with PivotTables.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3103) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
PivotTables are used to analyze huge amounts of data. The number of rows used in a PivotTable depends on the type of ...
Discover MoreIf you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can ...
Discover MoreIf you create a PivotTable in Excel 2007, you may have problems editing or updating that PivotTable in Excel 2003. The ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-07-04 06:09:24
O.Ezeakile
Thanks a lot.
2019-11-26 14:17:34
Denis
Nice cod bro. Thanks a lot.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments