Excel Pivot Table: How to Turn Off Subtotals

Charts & pivot tables · Updated November 18, 2026

To turn off every subtotal in an Excel PivotTable, click inside it and choose PivotTable Design > Subtotals > Do Not Show Subtotals. To remove subtotals from just one field, open that field’s settings and select None under Subtotals & Filters.

Turn off all PivotTable subtotals

In Excel for Microsoft 365 on Windows, use these steps to remove subtotal rows from the entire PivotTable:

  1. Click any cell in the PivotTable. This activates the contextual PivotTable tabs on the ribbon.
  2. Open the PivotTable Design tab. Depending on the ribbon layout, it may appear as Design under PivotTable Tools.
  3. In the Layout group, select Subtotals.
  4. Choose Do Not Show Subtotals.

Excel removes the subtotal lines generated for the PivotTable’s row fields. The source data is not changed, and the PivotTable’s values and field arrangement remain in place. If the command is unavailable or the contextual tab is missing, click inside the PivotTable first; clicking an ordinary worksheet cell hides those tabs.

This setting applies to the whole PivotTable. It is useful when a report has several nested row fields and you want detail rows without intermediate totals. It is also easy to reverse: return to the same Subtotals menu and choose one of the options to show subtotals again.

What PivotTable subtotals do

A subtotal summarizes records within a group in a PivotTable. For example, if a sales report has Region and Salesperson in the Rows area and Revenue in Values, Excel can show a subtotal for each region as well as the salesperson-level results. Subtotals make a hierarchy easier to scan, but they add rows that may be unnecessary when readers only need the detail or the final total.

Excel commonly adds subtotals to outer row fields when a PivotTable has more than one row field. A subtotal might appear after each region’s salespeople, separating that group from the next. The exact appearance depends on the report layout and field settings. Subtotals are calculated summaries; they do not represent additional records in the underlying source data.

Removing them does not delete categories or filter out records. It changes how the PivotTable displays summaries. The underlying source range or table stays intact, and you can restore the subtotal display later.

Remove subtotals from one field only

If you want to keep some group summaries but remove others, change the settings for a particular row field instead of using the global command. For example, you could retain a total for each region while removing subtotals for individual salespeople or product categories.

  1. Click a label belonging to the field whose subtotal you want to change. For instance, click a region name in the PivotTable.
  2. Right-click and choose Field Settings. You can also select the field and use PivotTable Analyze > Field Settings when that command is available.
  3. In the Field Settings dialog box, open Subtotals & Filters.
  4. Select None, then choose OK.

Depending on the field and PivotTable source, the dialog may offer automatic, custom, or no subtotals. Automatic uses Excel’s default subtotal behavior; custom lets you choose available summary functions; None suppresses the subtotal for that field. If you cannot see the expected choices, make sure you opened settings for the field itself rather than a value cell.

A useful approach is to apply the global setting first if the report should have no subtotals at all. Use field-level settings when the hierarchy matters and only selected summary lines are cluttering the view.

Subtotals and report layout

Subtotals and report layout affect related parts of a PivotTable, but they are different controls. The layout determines how row fields are arranged and labeled; the subtotal setting determines whether Excel inserts summary lines for groups. Changing from Compact Form to Tabular Form, for example, can make fields easier to read but does not by itself mean that subtotals are disabled.

To change the layout in desktop Excel, click inside the PivotTable and use PivotTable Design > Report Layout. The menu includes options such as Compact Form, Outline Form, and Tabular Form. In some layouts, subtotal positions can also be set to appear at the top or bottom of each group. Choose Do Not Show Subtotals in the Subtotals menu when the goal is to suppress them, regardless of the layout.

For instance, a tabular report with separate Region and Product columns may be more readable for copying or printing. If the report still shows a Region Total row after switching layouts, adjust the subtotal setting separately. Layout changes reorganize the presentation; they are not a substitute for turning off subtotals.

Subtotals are not grand totals

A common point of confusion is that the command for subtotals does not necessarily remove the overall grand total. A subtotal belongs to a group, such as a total for each region. A grand total summarizes the entire PivotTable, or the overall total for a row or column, depending on the report.

To control grand totals, click the PivotTable and open PivotTable Design > Grand Totals. Select the option that matches the report, such as turning grand totals off for rows and columns. Keep grand totals enabled if readers still need an overall figure after group subtotals are removed.

For a report with Region, Month, and Revenue, turning off subtotals can remove each region’s intermediate revenue line while leaving the overall revenue grand total visible. If both the group summaries and the overall total should disappear, change the Subtotals and Grand Totals settings independently. This separation lets you create a clean detail report without accidentally hiding a useful overall figure.

Grouped dates and other common cases

Date fields can be grouped into years, quarters, or months, creating a hierarchy that often displays subtotal lines. If those lines appear after grouping dates, use the same global or field-specific subtotal controls. You do not usually need to ungroup the dates just to remove their subtotals; ungrouping would change the date structure, which is a separate decision.

Another common source of confusion is an apparent duplicate total. A report might show a total for each parent group, followed by a grand total at the end. The parent-group figures are subtotals; the final overall figure is the grand total. Decide which level is useful before changing the settings, especially when a PivotTable is used for review or reconciliation.

If the PivotTable uses multiple row fields, inspect the hierarchy from outermost to innermost. Turning off one field’s subtotal may remove only that field’s summary, while summaries belonging to other fields remain. If you select the global Do Not Show Subtotals command, Excel suppresses all the PivotTable’s subtotals at once.

When the setting seems not to work

  • The Subtotals command is missing: click a cell inside the PivotTable and look for the contextual Design tab. The ordinary worksheet ribbon does not show PivotTable layout controls when the selection is outside the report.
  • A total remains at the bottom or side: it may be a grand total, not a subtotal. Use the separate Grand Totals menu to control it.
  • Some subtotal lines remain: check each row field’s Field Settings. A field-level setting can be useful for selective control, while the global command is the clearest choice if none should appear.
  • The report changes after refresh: refresh updates PivotTable results from the source, but the display options are separate from the source values. Recheck the PivotTable’s layout and field settings if its appearance differs from what you expect.
  • The command looks different: ribbon layouts and contextual tab labels can vary by Excel platform and window size. Look for the Subtotals control on the PivotTable Design or Design tab.

Excel for Mac also provides PivotTable design controls after you select the PivotTable; the contextual tab may be labeled Design rather than PivotTable Design. The same Subtotals menu is the setting to look for. Excel for the web has a more limited interface than desktop Excel, and available PivotTable controls can differ; if the command is not exposed in your browser, open the workbook in the desktop app to make the change.

Frequently Asked Questions

Can I turn off subtotals without deleting the PivotTable?
Yes. The Subtotals menu changes the PivotTable’s display, not its source data or structure. Use Do Not Show Subtotals to hide all subtotal rows while keeping the report and its fields.

How do I remove subtotals for only one row field?
Right-click a label for that field, choose Field Settings, select Subtotals & Filters, choose None, and confirm. Other fields can keep their subtotals.

Why is the grand total still showing?
Grand totals are controlled separately from subtotals. Use PivotTable Design > Grand Totals to change the overall total display.

Will refreshing the PivotTable turn subtotals back on?
Refresh updates the PivotTable from its source, but subtotal display is a layout setting. If the setting appears to have changed, check the Subtotals menu and the individual field settings after refreshing.

Can I keep subtotals but move them above each group?
Yes. In desktop Excel, use the Subtotals menu on the PivotTable Design tab to choose a display position, such as showing subtotals at the top or bottom of each group. The exact options depend on the PivotTable configuration.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.