Pivot Table VBA: How to Hide Subtotals

VBA & macros · Updated November 15, 2026

To hide subtotals in an Excel PivotTable with VBA, set the Subtotals property to twelve false values for each row or column field. The macro below does this for a named PivotTable; it does not turn off grand totals, which are separate settings.

What PivotTable subtotals are

A subtotal is a summary Excel inserts within a group in a PivotTable. For example, if a report has Region above City in the Rows area, Excel might show a subtotal for each region followed by the cities in that region. Subtotals can make a report easier to scan, but they can also add lines that are unwanted in a compact export or a report where only detail rows and a final total are needed.

In VBA, subtotal choices belong to individual PivotFields. A field can have options such as Sum, Count, Average, Maximum, or Minimum. The Subtotals property represents the twelve available subtotal types as a Boolean array. Setting every entry to False disables them for that field.

This is different from turning off grand totals. Grand totals summarize the whole PivotTable across rows or columns; subtotals summarize groups within it. It is also different from removing a field from the Rows or Columns area, which changes the structure of the report.

Macro to hide subtotals in a PivotTable

For a standard, non-OLAP PivotTable, this example turns off subtotals for every field currently in the Rows or Columns area. Replace the worksheet and PivotTable names with the names in your workbook.

Sub HidePivotTableSubtotals()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField

    ' Use the worksheet and PivotTable that contain the report.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set pt = ws.PivotTables("PivotTable1")

    ' Disable all twelve subtotal types for each row field.
    For Each pf In pt.RowFields
        pf.Subtotals = Array(False, False, False, False, _
                             False, False, False, False, _
                             False, False, False, False)
    Next pf

    ' Do the same for each column field.
    For Each pf In pt.ColumnFields
        pf.Subtotals = Array(False, False, False, False, _
                             False, False, False, False, _
                             False, False, False, False)
    Next pf
End Sub

Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z. Save a macro-enabled workbook as an .xlsm file if you need to keep the code. To run it in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert the code in a standard module, then run the macro from the Macros dialog with Alt+F8. On a Mac, open the editor through Tools > Macro > Visual Basic Editor; keyboard shortcuts can depend on the keyboard and function-key settings.

Choose the right sheet and PivotTable

The example uses explicit names rather than whichever PivotTable happens to be selected. That reduces the chance of changing the wrong report. To find the PivotTable name, click inside the PivotTable and look for its name box on the PivotTable Analyze tab. The worksheet name is the tab name at the bottom of Excel. Both names in the code must match the workbook exactly, including spaces.

For example, if the worksheet tab is “Monthly Sales” and the PivotTable name is “ptSalesByRegion,” change the two assignment lines to use those names. Keep the quotation marks around each name. If the workbook stores the PivotTable on a different sheet, set ws to that sheet before referring to its PivotTable collection.

The ThisWorkbook reference means the workbook containing the VBA code. If code is stored in a personal macro workbook or an add-in but should act on the currently active workbook, that reference may not be appropriate. In that case, deliberately choose the target workbook rather than relying on whichever workbook is active by accident.

Apply the setting to one field only

If you want to retain subtotals for most fields and suppress them for just one, use the field’s name instead of looping through every row and column field. For example, this changes the Region field in the named PivotTable:

Sub HideRegionSubtotals()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set pt = ws.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Region")

    ' Disable all twelve subtotal types for Region.
    pf.Subtotals = Array(False, False, False, False, _
                         False, False, False, False, _
                         False, False, False, False)
End Sub

Use the field caption Excel recognizes in the PivotTable, not necessarily the source column’s address. If the source heading is “Sales Region” but the field has been renamed to “Region” in the PivotTable, use the displayed field name. A misspelled or outdated name causes a run-time error when VBA tries to find the field.

A field must be in the PivotTable layout for its subtotal setting to have a visible effect. A field in the Rows area can show row subtotals; a field in the Columns area can show column subtotals. The broad macro handles both areas but does not change report filters or values in the Values area.

How the VBA setting relates to Excel’s menus

For a one-time change, the ribbon is often quicker than VBA. Click inside the PivotTable, open the contextual Design tab, choose Subtotals, then select Do Not Show Subtotals. This is a PivotTable layout command, so it affects the displayed report rather than deleting source records or changing the underlying data.

The VBA approach is useful when the change must be repeated across reports, performed after a refresh, or applied as part of a larger reporting routine. The array assignment explicitly disables all twelve subtotal types on the selected fields. It is more precise than changing only one subtotal type, such as Sum, because other types such as Count or Average would otherwise remain available.

For a single field, the menu can also be more convenient if you want to decide which subtotal type to show. VBA is a better fit when the requirement is consistently “show none” and the same operation must be repeated. You can use the menu and VBA for the same report, but make sure the macro targets the intended PivotTable and fields.

Subtotals and grand totals are separate

A frequent misunderstanding is that the subtotal setting also removes the final total row or column. It does not. If the report should have no grand totals either, use the PivotTable’s grand-total properties separately. For example, add these lines after setting the PivotTable reference in the macro:

    ' Turn off the overall totals separately from field subtotals.
    pt.RowGrand = False
    pt.ColumnGrand = False

RowGrand controls the grand total for rows, and ColumnGrand controls the grand total for columns. Choose the settings that match the report: a table may need to retain a final total even when group subtotals are hidden. The Design tab also has a Grand Totals menu for changing this without VBA.

In a hierarchy, hiding subtotals does not remove the underlying levels. You can still expand or collapse grouped items, and detail rows remain available. If the goal is to show only a flat list, hiding subtotals alone may not be sufficient; consider changing the layout or removing an unwanted field as well.

Common problems and limitations

  • The macro says a name is invalid. Check the worksheet tab, PivotTable name, and field caption. Excel does not infer the intended object if a name differs.
  • Subtotals return after a refresh or layout change. Refreshing a standard PivotTable usually preserves field settings, but a rebuilt report, changed layout, or newly added row field may require the macro to run again. The loop only handles fields present in the Rows and Columns areas when it runs.
  • The code fails on a Data Model or OLAP PivotTable. PivotTables connected to OLAP sources, including some Data Model reports, have restrictions on field subtotal settings. The twelve-value assignment is intended for conventional, non-OLAP PivotTables; the source may reject it or expose different controls. Check the PivotTable’s available menu options and adjust the approach to that connection rather than assuming the standard macro applies.
  • The field is not in the report layout. A field outside the Rows and Columns areas will not display group subtotals in the same way. Add it to the relevant area or confirm the field placement before debugging the property.
  • Macros are blocked. Excel may require permission to run VBA. Only enable macros in files you trust, and keep the workbook in a macro-enabled format if the code needs to be retained.

If the macro is part of a refresh workflow, run it after the PivotTable refresh and after any code that changes the field layout. For a report that is recreated each time, call the subtotal-setting routine once the new PivotTable and its fields exist. This avoids relying on settings from an earlier layout.

Frequently Asked Questions

How do I hide subtotals for all fields in a PivotTable?
Loop through pt.RowFields and pt.ColumnFields, setting each field’s Subtotals property to an array of twelve False values. The first macro above shows the complete pattern for a standard PivotTable.

Does setting subtotals to False remove grand totals?
No. Subtotals are summaries within groups; grand totals summarize the PivotTable as a whole. Set pt.RowGrand or pt.ColumnGrand to False separately if you also want to hide the corresponding overall total.

Can I hide subtotals for just one row field?
Yes. Get that field from pt.PivotFields("Field Caption") and assign the twelve-false array to its Subtotals property. Use the field caption recognized by the PivotTable.

Will the macro work with an Excel Data Model PivotTable?
Not necessarily. OLAP and Data Model PivotTables can restrict subtotal settings, so the regular PivotField array assignment may not work. Check the available controls for that PivotTable and test on a copy before using automation in a report workflow.

Can I hide PivotTable subtotals without VBA?
Yes. Click inside the PivotTable, go to the Design tab, choose Subtotals, and select Do Not Show Subtotals. This is usually the simplest choice for a one-off layout change.

Do I need to run the macro every time I refresh?
Not always. Existing field settings may remain after a normal refresh, but newly added fields or a recreated PivotTable will not have been processed by the earlier run. Run the macro after refresh when you need to ensure every current row and column field has subtotals disabled.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.