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.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
You can format PivotTables using either manual formatting or automatic formatting. You need to be careful, however, as ...
Discover MorePivotTables are often used to aggregate lots of information, and they do it beautifully. What do you do if Excel starts ...
Discover MoreNeed to reduce the size of your workbooks that contain PivotTables? Here's something you can try to minimize 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