In Excel for Microsoft 365 on Windows, select a cell in the PivotTable and choose PivotTable Design > Subtotals to show subtotals at the top or bottom of each group, or turn them off. To control one field or choose its calculation, open that field’s Field Settings.
What a PivotTable subtotal means
A PivotTable subtotal is a summary for a group of records, usually shown between the detail rows and the next group. For example, a sales PivotTable arranged by region and then product can display a total for each region as well as the individual product figures. A grand total, by contrast, summarizes the entire PivotTable. Subtotals and grand totals are separate settings.
Excel can calculate a subtotal using the summary method assigned to a value field. A numeric Sales field is commonly summarized by Sum, while a row-count measure may use Count. Depending on the field and source data, other available methods can include Average, Max, Min, and Count. A displayed subtotal is therefore not necessarily a sum: it follows the calculation selected for that value field.
Subtotals are most useful when the PivotTable has a hierarchy, such as Country > City, Department > Employee, or Year > Month. If the report has only one row field, the subtotal may simply repeat the grand total or add a group total that does not help explain the data. Choose the layout and summary level that make the grouping meaningful.
How Excel calculates and places subtotals
PivotTables summarize source records according to the fields placed in Rows, Columns, Values, and Filters. A field in Rows can divide records into groups; a value field supplies the measure that Excel summarizes within each group. With Region above Product in the Rows area and Sales in Values, Excel can calculate product figures within each region and a region subtotal across those products.
The default setting for a row field is usually Automatic. Excel chooses a suitable summary for that field’s subtotal, based on the value fields in the PivotTable. You can change this to a specific calculation, such as Sum or Average, or disable subtotals for that field. When several value fields are present, the subtotal row may show a separate result for each one.
The location setting controls whether subtotal rows appear at the top or bottom of their group. This changes the reading order, not the underlying calculation. The setting applies to the PivotTable’s subtotal layout; it does not move the grand total, which has its own controls.
Show, hide, or move all subtotals
To change subtotal display for the whole PivotTable in Excel for Microsoft 365 on Windows:
- Click anywhere inside the PivotTable. The contextual PivotTable tabs appear on the ribbon.
- Open PivotTable Design.
- Select Subtotals.
- Choose Do Not Show Subtotals, Show All Subtotals at Bottom of Group, or Show All Subtotals at Top of Group.
These options are useful when you want a consistent layout quickly. To restore group totals after hiding them, select one of the top- or bottom-of-group options. If a particular group still has no subtotal, check that field’s own setting as described below; an individual field can be set to None.
On Excel for Mac, click the PivotTable and use the contextual Design tab’s Subtotals menu; ribbon wording or arrangement can vary slightly by build. In Excel for the web, PivotTable features and ribbon options can differ from the desktop app. If the needed subtotal controls are not available in the browser, open the workbook in the Excel desktop application.
Set a subtotal for one row field
Use a field-specific setting when, for example, you want a subtotal for each region but not for each product. Click a label belonging to the field you want to change, right-click, and choose Field Settings. In the dialog, use the Subtotals & Filters tab. Select Automatic to let Excel choose, Custom to select an available summary function, or None to suppress subtotals for that field. Click OK.
You can also select a cell in the relevant row field and choose PivotTable Analyze > Field Settings. Be careful to select a row-field label, not a number in the Values area: value cells have their own settings. If you select Custom, choose a function that fits the data and the question. For instance, Average is a different measure from Sum and should not be chosen just because it produces a smaller total.
When a field is set to None, the field’s groups can remain visible while their subtotal rows disappear. This is often the clearest arrangement for a detailed report with several levels, such as Region, Store, and Product, where only the Region total is useful.
Choose the calculation for the subtotal
The subtotal function is related to, but distinct from, the way the value field is summarized. For example, if Sales is set to Sum in the Values area, its regional subtotal normally sums the underlying sales records for that region. If the value field is set to Average, Excel reports an average rather than adding the group’s displayed figures.
To adjust the value calculation, right-click a value in the PivotTable and choose Summarize Values By, then choose an available option. For more controls, right-click a value and choose Value Field Settings. The available summary options depend on the data and PivotTable source. A numeric field can generally be summed or averaged; a text field is generally counted rather than summed.
Do not assume that a subtotal of averages is the same as the average of all underlying records. A simple average of subgroup averages gives equal weight to each subgroup, even if the groups contain different numbers of records. When the intended statistic matters, verify how the PivotTable is summarizing the source data and consider whether a different data model or calculation is required.
Example: regional sales by product
Suppose a source table has columns Region, Product, and Sales. The records include North, Notebook, 120; North, Pen, 80; South, Notebook, 150; and South, Pen, 50. Put Region and then Product in Rows, and Sales in Values, summarized by Sum. With subtotals enabled for Region, the report shows North sales of 200 and South sales of 200. The grand total is 400.
Those figures are checked directly from the example records: North is 120 + 80 = 200; South is 150 + 50 = 200; and all sales total 200 + 200 = 400. The product rows preserve detail, the region rows provide intermediate summaries, and the grand total summarizes the entire source set. Turning off subtotals for Product avoids an extra level of totals if product subtotals are not useful.
If the question changes to “What was the average sale per record in each region?”, change the value calculation to Average and interpret the result accordingly. Do not add the region averages to obtain a grand average unless the groups have equal record counts. The grand total needs to represent the intended measure across all four original records.
Subtotals, grand totals, and worksheet SUBTOTAL are different
Three Excel features are easy to confuse. A PivotTable subtotal summarizes a group in a PivotTable. A PivotTable grand total summarizes across the whole report and is controlled separately through PivotTable Design > Grand Totals. The worksheet SUBTOTAL function is a formula used in cells; it is not the control that turns PivotTable group subtotals on or off.
The worksheet function is useful for calculating over a range, including situations where filtered or hidden rows affect the result according to the function number used. It does not automatically create PivotTable group rows. Similarly, the ribbon’s PivotTable Subtotals command does not insert formulas into the source table. If your goal is a grouped summary of records, use the PivotTable controls; if your goal is a formula in a worksheet cell, use a worksheet formula appropriate to that task.
Excel also has a separate Data > Subtotal command for adding outline summaries to a sorted list. That command is not the same as PivotTable subtotals and is intended for a regular data range rather than configuring a PivotTable. Do not use these similarly named features interchangeably.
Why subtotals may be missing or unexpected
- The field is set to None. Open Field Settings for the relevant row field and choose Automatic or Custom.
- The PivotTable-wide option hides them. Check PivotTable Design > Subtotals and choose a display option.
- The wrong area or field is selected. Subtotals are associated with row or column grouping fields, not arbitrary detail cells. Confirm the field arrangement in the PivotTable Fields pane.
- The report layout is hard to read. Compact Form nests multiple row fields in one column. Use PivotTable Design > Report Layout to choose Outline Form or Tabular Form if separate columns make groups and totals easier to identify.
- The source data or grouping is not what you expect. Check that the source range includes all records and that dates, labels, and numbers are stored consistently. Refresh the PivotTable after changing its source data.
- The source type limits a setting. Some PivotTable sources, including OLAP or Data Model connections, can have different restrictions from a standard range-based PivotTable.
For a source that has changed, click inside the PivotTable and choose PivotTable Analyze > Refresh. If new rows were added outside the original source range, refreshing alone may not include them. Check PivotTable Analyze > Change Data Source, or use an Excel Table as the source so added table rows can be included when the PivotTable is refreshed. A refresh updates the report; it does not repair inconsistent source labels or change the chosen subtotal function.
Improve the report layout and readability
Subtotal placement is only one part of making the report easy to read. In a compact layout, several row fields share a single column, which saves space but can make labels and group levels less obvious. Outline and Tabular layouts display row fields in separate columns. These formats can help when readers need to scan or export the detail.
Use PivotTable Design > Report Layout to change the layout. In a tabular report, Repeat All Item Labels can repeat outer group labels on detail rows, which may help when copying or reading the report outside Excel. It does not create additional subtotal calculations. Also consider whether you need grand totals for rows, columns, both, or neither; set those separately under Grand Totals.
For a report that will be printed or shared, check that subtotal labels clearly identify the group and that the number format is suitable. Number formatting can be adjusted through the value field’s number format settings so that currency, percentages, or whole numbers display consistently. Formatting changes presentation, not the stored source values or the subtotal calculation.
Frequently Asked Questions
How do I add a subtotal to an Excel PivotTable?
Click in the PivotTable, then choose PivotTable Design > Subtotals > Show All Subtotals at Bottom of Group or the top option. If you want a subtotal for just one row field, open its Field Settings and choose Automatic or Custom instead.
How do I remove subtotals but keep the grand total?
Choose PivotTable Design > Subtotals > Do Not Show Subtotals. This setting does not by itself turn off the grand total. Control grand totals separately from PivotTable Design > Grand Totals.
Can I show a subtotal for only one PivotTable field?
Yes. Open Field Settings for the row field, select Automatic or Custom under Subtotals & Filters, and set other fields to None if you do not want their subtotal rows. The exact options can vary with the PivotTable’s source type.
Why does my PivotTable show Count instead of Sum?
Excel may treat the source values as text or find nonnumeric entries in the field, so it chooses Count. Check the source column for numbers stored as text, blanks, or inconsistent values, correct the source where appropriate, and refresh the PivotTable. Then inspect the value field’s summary setting.
Does a PivotTable subtotal include filtered-out records?
It reflects the data represented in the PivotTable after its report filters and field selections are applied; changing filters can change the displayed subtotal. The precise behavior can depend on the PivotTable source and layout, so verify the report’s filters when reconciling a number with the full source table.
Related
- Excel Max Rows per Sheet: Limits and Workarounds
- Excel Max Value in a Column: Find the Largest Number
- Excel Maximum Rows: Limits and What to Do
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Waterfall Chart Tutorial: Build and Format OneTo make a waterfall chart in Excel for Microsoft 365 on Windows, put category labels in one column…
- How to Rotate a Waterfall Chart in Excel: Axis OptionsTo change the way a waterfall chart reads in Excel, format its category axis: reverse the category order…
- HR Dashboard in Excel: Metrics, Setup, and Best PracticesAn HR dashboard in Excel is a concise, refreshable view of workforce measures such as headcount, turnover, hiring,…
- What Does a Trendline Tell You in Excel? A Clear GuideA trendline in Excel summarizes the general direction or pattern of data in a chart: it can show…
- Duplicate Pivot Charts and Tables in ExcelTo duplicate a PivotTable in Excel for Microsoft 365 on Windows, select the whole table with PivotTable Analyze…
- Excel Pivot Chart Show Zero Values? Fixes That WorkYes. An Excel PivotChart can show zero values, but the right fix depends on whether the PivotTable contains…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.