Removing Subtotals from Many PivotTable Fields

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:

http://www.contextures.com/xlPivot03.html#Subtotals

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Getting Word to Remember the Default Date and Time Format

One way to insert the current date into your document is to use the Date and Time dialog box. The Default button in the ...

Discover More

Capturing a Screen

Windows provides a way of "capturing" the image on the screen into the clipboard. You can then paste the image from the ...

Discover More

How Word Treats Normal.dot

Templates are at the core of how Word creates and formats documents. From the earliest days of Word, the most basic of ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

MORE EXCELTIPS (MENU)

Maintaining Formatting when Refreshing PivotTables

When you refresh the data in a PivotTable, Excel can play havoc with whatever formatting you applied. Here's how to protect ...

Discover More

Refreshing PivotTable Data

If you modify the data on which a PivotTable is based, you'll need to refresh the table so it reflects the modified data. ...

Discover More

Pointing PivotTables to Different Data

Changing the data source PivotTables go to can be a bit tricky. This tip explains what can happen when you re-point your ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

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.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share