To remove every subtotal from an Excel PivotTable, select a cell in the table and choose PivotTable Design > Layout > Subtotals > Do Not Show Subtotals. To remove subtotals for just one row or column field, open that field’s settings and choose None on the Subtotals & Filters tab.
What PivotTable subtotals show
A subtotal is a summary calculated for a group within a PivotTable. For example, if a report lists sales by region and then by product, Excel can show a subtotal for each region in addition to the individual product figures. The subtotal is usually a sum, but it can use another summary calculation, such as count or average, depending on the field’s settings.
Subtotals can help readers compare groups without calculating them outside the report. They can also make a PivotTable look crowded, repeat information in a compact report, or complicate copying the displayed results into another document. Removing them changes what the PivotTable displays; it does not delete source records or alter the underlying sales values.
Excel treats subtotals and grand totals as separate display options. A subtotal summarizes one group, while a grand total summarizes the whole row or column of values. The standard command for hiding subtotals does not, by itself, turn off grand totals.
Remove all subtotals from a PivotTable
In Excel for Microsoft 365 on Windows, use the contextual design tab for the selected PivotTable:
- Click any cell inside the PivotTable. The PivotTable tabs appear on the ribbon.
- Select PivotTable Design.
- In the Layout group, select Subtotals.
- Choose Do Not Show Subtotals.
Excel removes the displayed subtotals for the PivotTable’s row and column fields. The report’s detail rows remain, and any enabled grand totals remain. If the ribbon is narrow, the Layout commands may be compressed into a menu; expand the ribbon or widen the Excel window to find Subtotals.
This is generally the fastest choice when the report should contain no group-level summary lines. It applies to the PivotTable as a whole, rather than requiring you to edit every field. If a workbook has more than one PivotTable, select each table and check its setting separately; changing one PivotTable does not necessarily change the others.
Remove subtotals for just one field
Sometimes one grouping should remain summarized while another should not. For example, a sales report may need a subtotal for each region but no subtotal for product categories within each region. Use the field-level setting for that situation:
- Click a label belonging to the row or column field you want to change.
- Right-click and choose Field Settings. Depending on the selected cell and ribbon layout, you can also use the Field Settings command on the PivotTable Analyze tab.
- In the dialog, open Subtotals & Filters.
- Select None, then select OK.
Repeat these steps for any other individual fields whose subtotals you want to suppress. This setting is useful when the report needs a deliberate hierarchy: perhaps a subtotal for each department, but not for each job title inside it. It also gives you more control than the all-fields command.
If you select Automatic or Custom instead, Excel can show a subtotal for that field. The exact choices available depend on the field and the PivotTable’s data source. If the Subtotals & Filters controls are unavailable or limited, check whether the PivotTable is based on an external source or the Data Model; some source types restrict particular field settings.
Change where subtotals appear instead of hiding them
Removing subtotals is not the only way to make a report easier to read. If the summary is useful but its position is awkward, select a cell in the PivotTable and choose PivotTable Design > Layout > Subtotals. The menu includes options to show subtotals at the top or bottom of the group. Choosing a position keeps the summary while moving it relative to the detail rows.
Report layout also affects how the hierarchy looks. On the same PivotTable Design tab, the Report Layout menu offers Compact, Outline, and Tabular forms. Compact form places multiple row fields in a single column; Outline and Tabular forms display fields in separate columns. Switching layouts can make group labels and subtotal lines easier to distinguish, but it is not the same as turning subtotals off.
For instance, a compact report with Region and Product in the Rows area may show nested labels in one column. A tabular report places those fields in separate columns, which can be easier to copy or scan. If the main concern is confusing indentation, try a different report layout before removing summaries that readers still need.
Keep or remove grand totals separately
A common question is whether you can remove subtotals but keep the overall total. Yes. Choose Do Not Show Subtotals from the Subtotals menu, and leave the grand total settings alone. Excel’s grand total controls are separate: select PivotTable Design > Layout > Grand Totals and choose the option that suits the report, such as showing totals for rows and columns or turning them off.
Consider a PivotTable with regional sales figures and a final overall total. Turning off subtotals removes the intermediate regional summary rows, but the overall total can remain at the bottom. This is often a clean format for a short report where the detail matters and readers still need one final figure.
Conversely, if a report should show regional subtotals but not a grand total, leave field subtotals enabled and change only the Grand Totals menu. Be precise about which total you mean when explaining a change to a colleague: the words “subtotal” and “grand total” refer to different levels of the summary.
Why a subtotal may still appear
If a summary line remains after changing the setting, identify what is producing it before changing the source data. A line that says Total or Grand Total may be the grand total, not a subtotal. Use PivotTable Design > Layout > Grand Totals to control that separately.
- The wrong PivotTable is selected: Click inside the specific report you want to edit, then confirm its field labels before changing the menu.
- Only one field was changed: Choosing None in Field Settings affects that field. Use the PivotTable Design Subtotals menu if you want to suppress them across the entire table.
- The menu choice was a placement option: Showing subtotals at the top or bottom moves them; it does not remove them. Choose Do Not Show Subtotals to hide them.
- The visible line is not a PivotTable subtotal: A manually typed total in the source data, a worksheet formula outside the PivotTable, or a separate report row will not disappear when you change PivotTable settings.
- The PivotTable was refreshed or rebuilt: Refreshing usually updates the data rather than changing the intended layout, but a copied, replaced, or newly created PivotTable may have different settings. Recheck the selected table and its field options.
When diagnosing a stubborn line, click the relevant cell and inspect the formula bar and surrounding labels. PivotTable-generated labels and worksheet entries can look similar, but only the former respond to PivotTable layout commands.
Excel on Mac and Excel for the web
In current desktop versions of Excel for Mac, the same general approach applies: select the PivotTable, open its contextual design tab, and use the Subtotals command to choose Do Not Show Subtotals. Ribbon names and placement can vary slightly by release and window size. For one field, use its Field Settings dialog and look for the subtotals option.
Excel for the web has PivotTable editing controls, but the ribbon can differ from the Windows desktop interface and some options may depend on the workbook or feature available in that version. If you cannot find the all-subtotals control or need field-specific settings, open the workbook in the desktop Excel app and follow the Windows steps above. Keep the workbook saved so the change is reflected when you return to the browser.
Restore subtotals when you need them again
To bring summaries back for the whole PivotTable, select a cell in it and choose PivotTable Design > Layout > Subtotals, then select an option to show subtotals at the top or bottom of groups. To restore them for only one field, open Field Settings > Subtotals & Filters and select Automatic or a custom calculation where available.
Before sharing a report, check the result from top to bottom. Confirm that the desired detail rows remain, the group summaries appear at the intended level, and grand totals are either present or absent as expected. If other people use the workbook, a short note about the report’s layout can prevent them from mistaking a deliberately hidden subtotal for missing data.
Frequently Asked Questions
Can I remove subtotals but keep the grand total?
Yes. Choose Do Not Show Subtotals from the Subtotals menu, and leave the Grand Totals setting unchanged. These are independent controls.
Can I remove a subtotal for only one row field?
Yes. Open that field’s Field Settings, select Subtotals & Filters, and choose None. Other fields can keep their own subtotal settings.
Does removing PivotTable subtotals delete or change my source data?
No. It changes the PivotTable’s displayed summaries, not the records in the source range or the underlying values.
Why does a total still show after I hide subtotals?
It may be a grand total, which has a separate control, or a total typed or calculated elsewhere on the worksheet. Check the label and whether the cell belongs to the PivotTable.
How do I add PivotTable subtotals back?
Use the Subtotals menu to show them at the top or bottom of groups. For a single field, return to Field Settings and select Automatic or another available subtotal option.
Related
- Excel Data Validation Restrictions and Their Limits
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Combo Chart Example: Build a Clear Two-Axis ChartAn Excel combo chart combines two chart types—such as columns and a line—in one visual. For a practical…
- Excel Histogram: Create and Format One From Your DataTo create a histogram in Microsoft Excel for Microsoft 365 on Windows, put your numerical observations in one…
- Excel Line Chart Tutorial: Create and Format OneTo make a line chart in Excel for Microsoft 365 on Windows, arrange your categories in one column…
- Excel Pivot Table Row Limit: What You Need to KnowIn Excel for Microsoft 365 on Windows, a PivotTable displayed on a worksheet is limited by the worksheet…
- Bubble Chart in Excel: Make Each Bubble a Different SizeYes. To make bubbles different sizes in an Excel bubble chart, give each data point its own size…
- Radar Chart Excel Tutorial: Create and Format OneTo create a radar chart in Excel for Microsoft 365 on Windows, select a table with category labels…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.