Excel Pivot Table Totals at Top? Show Subtotals

Yes. To put category subtotals above their detail rows in Excel for Microsoft 365 on Windows, click inside the PivotTable, then choose PivotTable Design > Subtotals > Show all Subtotals at Top of Group. This moves subtotals to the top of each group; it does not move the overall grand total to the top of the entire PivotTable.

What “totals at the top” means in a PivotTable

People often use “totals” to mean two different things in a PivotTable. A subtotal summarizes one group of records, such as total sales for each region. A grand total summarizes all the records included in the PivotTable. Excel has a built-in setting for placing group subtotals above or below the items in each group. Grand totals have separate display controls and normally appear at the bottom of the report, or at the right for column totals.

For example, imagine a sales report with Region and City in the Rows area and Sum of Sales in Values. With Region subtotals at the bottom, the cities appear first and a regional total follows them. With subtotals at the top, the region’s total appears before its city rows. The figures are the same; only the position of each group summary changes.

That distinction matters when someone asks for “the total at the top.” If they want each group’s sum above its details, use the subtotal-position command. If they want one overall figure above the report, Excel does not provide a standard PivotTable command to relocate the grand-total row there.

Show subtotals at the top of each group

In desktop Excel for Windows, use these steps:

Click any cell inside the PivotTable. The contextual PivotTable tabs appear on the ribbon.
Open PivotTable Design.
In the Layout group, select Subtotals.
Choose Show all Subtotals at Top of Group.

Excel applies the position to row-field subtotals that are currently enabled. A subtotal row will appear above the detail items belonging to its group. If nothing seems to change, check that the relevant row field has subtotals turned on and that the field hierarchy has more than one level. A single row field with no nested detail may not make the placement obvious.

The command is available only when the selection is in a PivotTable, so clicking an ordinary worksheet cell first will not show the contextual design tab. Excel for Mac also has PivotTable design controls, though ribbon organization and labels can differ by release. In Excel for the web, available PivotTable layout commands can vary; if the subtotal placement option is missing, open the workbook in the desktop app.

Choose which fields have subtotals

The top-or-bottom command controls placement, not whether every field should have a subtotal. In a report with several row fields, you may want totals for Region but not for City. To control an individual field, right-click one of its labels in the PivotTable and choose Field Settings. In the dialog, use Subtotals & Filters to select Automatic, a specific calculation, or None. The available choices can depend on the field and the PivotTable’s source.

For example, a hierarchy of Region, Salesperson, and Order ID is usually easier to read with subtotals for Region, perhaps for Salesperson, and none for Order ID. Subtotaling every level can produce a crowded report with repeated summary rows. Conversely, turning off subtotals for Region means there is no regional summary to place at the top, even if the overall top-subtotals setting is selected.

Automatic generally uses the value-field calculation, such as Sum or Count, for the subtotal. You can change a value calculation separately: right-click a number in the Values area, choose Summarize Values By, and select a supported calculation. That changes what the number represents, not where the subtotal row appears.

How row order and report layout affect the result

Subtotal placement works together with the order of fields in the Rows area. The first row field is the outer group; the next field is nested inside it. If Region is above City, Excel can show a regional subtotal above that region’s cities. If you drag City above Region in the PivotTable Fields pane, the hierarchy changes, and so does what each subtotal summarizes. Review the field order before assuming the subtotal command has failed.

Report layout changes how the hierarchy is presented, but does not turn a subtotal into a grand total. On PivotTable Design > Report Layout, Excel offers Compact Form, Outline Form, and Tabular Form. Compact Form puts multiple row fields into one column. Outline and Tabular Forms give row fields separate columns, which can make labels and group boundaries easier to scan. The same top-of-group setting can be useful in each layout, though the visual result differs.

A practical setup for a readable regional report is:

Place Region before City in the Rows area.
Keep Sum of Sales in the Values area.
Enable a Region subtotal and set subtotals to the top of the group.
Choose Tabular Form if separate Region and City columns help readers follow the hierarchy.

Try the layout with a small sample first. A subtotal above its group is useful when readers need the summary before scanning the underlying items, but it can be less intuitive if the group label is visually separated from the total.

Why the grand total does not move to the top

The command named “Show all Subtotals at Top of Group” applies to subtotals for row groups. It does not reposition the grand total. To show or hide grand totals, click inside the PivotTable and choose PivotTable Design > Grand Totals. The menu provides options for turning grand totals on or off for rows and columns. Those choices govern whether totals are displayed, not a top-versus-bottom position setting.

If a report needs a single overall total above the PivotTable, one straightforward option is to place a separate summary cell above it. It can refer to a suitable total in the PivotTable, or calculate from the source data, but it should be clearly labeled and checked after refreshes or layout changes. Copying a displayed grand-total number into a cell creates a static value that will not update automatically. A linked or formula-based summary is generally safer, provided the reference continues to point to the correct total.

Another option is to design a separate summary area or dashboard above the PivotTable and leave the PivotTable’s own grand total in its normal position. This keeps the source report intact and avoids trying to make a grand-total row look like a group subtotal. Do not confuse a manually added summary with a built-in PivotTable total: the former may need its own refresh and validation checks.

Common problems and quick checks

If the subtotal appears in the wrong place, or the command has no visible effect, check the structure and field settings before rebuilding the PivotTable:

No subtotal row appears: Open Field Settings for the row field and confirm that its subtotals are not set to None.
The wrong items are grouped together: Inspect the order of fields in the Rows area. The subtotal belongs to the outer field’s group, not necessarily to the label you were expecting.
There are too many totals: Disable subtotals on lower-level fields, such as individual transaction or product rows.
The total is present but hard to identify: Check the row-field labels and report layout. A subtotal can be mistaken for a detail item if the group name is not apparent.
Values look stale: Refresh the PivotTable from PivotTable Analyze > Refresh after changing source data. Refresh updates the report from its source; it does not change the subtotal-position setting.

Also verify the source data and the value calculation. A subtotal of Count answers a different question from a subtotal of Sum, and a blank or text value in the source can affect what a count represents. If the PivotTable is based on an external or specialized data model, some field options may be limited by that source. In that case, check the available Field Settings rather than assuming every regular-range PivotTable option applies.

When top subtotals are useful

Top-positioned subtotals suit reports where the reader wants an immediate group-level result before reviewing its detail. A manager scanning regions can see a regional sales figure first, then inspect the cities that make it up. It can also help when groups are long: readers do not have to reach the end of a large block to find its subtotal.

Bottom-positioned subtotals may be more natural when the summary is meant to confirm the detail just reviewed. For instance, someone checking a list of monthly expenses might prefer to read the entries and then see the category total. Neither position changes the underlying data or the arithmetic; it changes the reading order. Choose based on how people use the report, and keep the convention consistent across related PivotTables.

Consider printing and exporting as well as on-screen reading. A large group may split across printed pages, leaving a top subtotal on one page and some detail on another. Page breaks, repeated labels, and column widths can affect readability even when the PivotTable itself is configured correctly. Preview the final report in the format readers will use.

Frequently Asked Questions

Can I put the PivotTable grand total at the top?
Not with the standard Grand Totals controls. Excel lets you show or hide grand totals for rows and columns, while the top-of-group command positions category subtotals. For a top-level summary, add a clearly labeled linked summary or a separate dashboard cell above the PivotTable.

How do I put subtotals at the bottom again?
Click the PivotTable and choose PivotTable Design > Subtotals > Show all Subtotals at Bottom of Group. This changes subtotal position; it does not disable subtotals.

Why is the Subtotals option greyed out or missing?
First click inside the PivotTable and check that the contextual PivotTable Design tab is visible. Also check whether the selected report or its data source supports the layout operation. If the option remains unavailable, try opening the workbook in desktop Excel and inspect the individual row field’s Field Settings.

Can I show a subtotal for only one row field?
Yes. Right-click a label for that field, select Field Settings, and choose the subtotal behavior under Subtotals & Filters. Set other fields to None if you do not want their group summaries. The overall top-or-bottom setting controls the position of subtotals that are enabled.

Does putting subtotals at the top change the total?
No. It changes where the subtotal is displayed, not the source records or calculation. If a number changes, check the selected value calculation, filters, grouped items, source data, and refresh status.

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.