In Excel, click anywhere in the PivotTable, open Design, choose Subtotals, and select Do Not Show Subtotals, Show all Subtotals at Bottom of Group, or Show all Subtotals at Top of Group. These settings change the report layout; they do not delete source records.
The instructions below apply to Excel for Microsoft 365 (Windows and Mac), Excel 2024, 2021, 2019, and 2016, as documented by Microsoft.
What a PivotTable subtotal means
A subtotal summarizes one subgroup inside a PivotTable. For example, with Region and then Salesperson in the Rows area and Sales in Values, Excel can show a sales subtotal for each region. A grand total is different: it summarizes the entire report, usually in a final row or column.
Neither setting changes the underlying source data. Hiding a subtotal only removes its displayed summary line; the detail rows, grouping fields, and source records remain available.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Do not confuse either PivotTable feature with Excel’s worksheet Data → Outline → Subtotal command. That command inserts SUBTOTAL formulas and outline controls into an ordinary list or range, rather than changing a PivotTable.
Add or remove all PivotTable subtotals
- Click any cell inside the PivotTable.
- Open the Design tab under PivotTable Tools.
- In Layout, select Subtotals.
- Choose the required option:
| Choice | Result |
|---|---|
| Do Not Show Subtotals | Hides the subtotal lines for the PivotTable’s row and column fields. |
| Show all Subtotals at Bottom of Group | Lists each group’s detail first, then its subtotal. |
| Show all Subtotals at Top of Group | Places each group’s subtotal before its detail rows. |
Top placement often suits an executive-style summary because the result appears first. Bottom placement is common for schedules where readers review transactions and then their total. The better choice depends on how the report is read.
Remove a subtotal from only one field
The Design command is global. To keep subtotals for some fields but remove one field’s subtotal:
- Click a label belonging to the target row or column field—for example, a region name. Do not select a numerical value cell.
- Choose PivotTable Analyze → Active Field → Field Settings (the button may appear simply as Field Settings).
- Under Subtotals & Filters, select None.
- Click OK.
The field’s subtotal disappears while other fields can continue to use their own settings. To restore the normal behavior, return to Field Settings and choose Automatic.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesChoose a different subtotal calculation
In the same Field Settings dialog, Excel may offer Automatic, None, and Custom under Subtotals. Automatic uses the field’s normal function—usually Sum for numeric data and Count for nonnumeric data.
When Custom is available, you can select one or more of these functions:
- Sum
- Count
- Average
- Max
- Min
- Product
- Count Numbers
- StDev and StDevp
- Var and Varp
Use Sum for a monetary total, Count for the number of records, Average for a typical value, and Max or Min for extremes. Statistical functions can be useful for analysis but may make an operational report harder to read. Selecting several functions can also add columns or rows and make nested PivotTables crowded.
Custom functions are not available in every PivotTable. Calculated items can prevent the summary function from being changed, and PivotTables based on some OLAP or other external analytical sources expose fewer choices.
Rank #3
Remove the grand total instead
If the unwanted line is the final Grand Total, changing Subtotals is the wrong control.
- Click inside the PivotTable.
- Open Design → Grand Totals.
- Choose the option for turning grand totals off, or the option that applies only to rows or only to columns.
For separate row and column control, use PivotTable Analyze → Options → Totals & Filters, then clear Show grand totals for rows and/or Show grand totals for columns. Subtotal visibility and grand-total visibility are independent.
Why layout changes what you see
Subtotals are most useful when fields are nested in the Rows or Columns areas. In a Region → Salesperson example, Region is the outer group and Salesperson is the inner group. Depending on your needs, you can show region subtotals, suppress salesperson subtotals through Field Settings, or put the region summary above its details.
Excel’s Compact, Outline, and Tabular report layouts arrange labels differently. The top and bottom choices are especially visible for outer row labels in Compact or Outline form, while Tabular form gives each row field its own column. If a setting appears to have little effect, check which fields are actually in Rows or Columns and change the report layout under Design → Report Layout.
When the subtotal control is missing or disabled
You selected a value cell
Field-level settings require a row or column label. Select an item such as a department or region name, then open Field Settings. A number in the Values area does not identify the field whose subtotal you want to change.
There is no grouped row or column field
A PivotTable containing only Values has no visible subgroup to subtotal. Add a field to Rows or Columns if you need group summaries.
The field contains a calculated item
Excel can still let you hide or show a subtotal, but it may not let you change the summary function when a calculated item is present.
The source is OLAP or another external connection
External analytical sources can limit custom subtotal functions and some filtered-total behavior. The available Field Settings options may therefore differ from those of a PivotTable built from a worksheet range.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Best Value
- Used Book in Good Condition
You are not working in a PivotTable
If the object is a normal range, use Data → Outline → Subtotal after sorting the list by the field you want to group. That feature creates worksheet formulas and outline levels; it does not provide PivotTable’s Design tab.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Refreshes and source-data changes
Subtotals are layout settings, not manually typed rows. Refresh the PivotTable after changing its source data. For a PivotTable based on an Excel Table, new and updated table records are included when the PivotTable is refreshed. A refresh can change the items shown and the report’s size; behavior of every display preference can vary with the Excel build and connection type, so check the resulting layout.
Filtered totals are another issue. Options that determine whether filtered items contribute to totals—particularly with OLAP sources—do not control whether subtotal rows are visible.
Excel worksheet Subtotal versus PivotTable subtotal
| Feature | Use it when | How it works |
|---|---|---|
| PivotTable subtotal | You need a summary that follows PivotTable fields, filters, grouping, and refreshes. | Controlled through Design → Subtotals or each field’s Field Settings. |
| Worksheet Subtotal | You have a sorted ordinary list and need formulas in a fixed worksheet layout. | Data → Outline → Subtotal inserts SUBTOTAL formulas and outline controls. It is unavailable while editing an Excel Table unless the table is converted to a normal range. |
Google Sheets and LibreOffice Calc
Google Sheets
Google Sheets does not use Excel’s Design tab. Open the Pivot table editor and manage fields under Rows, Columns, Values, and Filters. Google’s current basic instructions do not document an Excel-equivalent global command for placing all subtotals at the top or bottom. See the Google Pivot table guide.
Free tools Windows power users keep installed
One-click scans. No signup required.
LibreOffice Calc
In Calc, right-click the pivot-table results, open Properties, and use the partial-sum settings. Calc’s layout options can place partial sums at the top or bottom, but its terminology and dialogs differ from Excel. See the LibreOffice Pivot Tables guide.
Further help from Microsoft
For screenshots and edition-specific notes, see Microsoft’s subtotal and total fields in a PivotTable, PivotTable overview, and guide to worksheet subtotals.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
