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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Create a complex PivotTable and you may find that your system slows to a crawl. The reason for this may be due to the way ...
Discover MoreChanging the data source PivotTables go to can be a bit tricky. This tip explains what can happen when you re-point your ...
Discover MoreOne of the ways you can use PivotTables is to generate counts of various items in a data table. This is a great technique ...
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 © 2023 Sharon Parq Associates, Inc.
Comments