by Allen Wyatt
(last updated June 8, 2016)
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:
The site also contains some other good information for working with PivotTables.
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!
If you are using a data set that includes a number of zero values, you may not want those values to appear in a PivotTable ...Discover More
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 in ...Discover More
Need to reduce the size of your workbooks that contain PivotTables? Here's something you can try to minimize the ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.