When you need group subtotals in Excel, the usual approach is sorting, then manually writing SUM formulas for each group, then repeating when the grouping changes. The Subtotal command on the Data tab automates all of this. Sort your data by the grouping column first, then run the command. Excel inserts subtotal rows at every group break, creates a collapsible outline with level buttons, and applies any aggregate function — Sum, Average, Count, Max, Min — across all groups simultaneously. This guide covers the Data tab Subtotal command, the SUBTOTAL worksheet function that ignores filtered rows, copying only subtotal rows, and every common issue.
Data Tab Subtotal vs the SUBTOTAL Function
These are two completely different tools that share a name. Confusing them is the most common source of Subtotal-related errors.
- Data > Outline > Subtotal (command) — inserts physical subtotal rows into your data layout and creates an outline structure with collapsible level buttons. It modifies the worksheet structure. Use it for reports, printed summaries, and grouped views where you need to collapse and expand data visually.
- SUBTOTAL(function_num, ref) (function) — a worksheet formula that calculates an aggregate while ignoring hidden or filtered rows. It does not modify the worksheet structure. Use it for totals rows in filtered tables, dashboards, and any aggregate that must reflect the current filter state automatically.
Step-by-Step Setup
Follow these five steps in order. Skipping step 1 is the most common mistake — always sort first.
Using the Outline Level Buttons
After applying subtotals, outline level buttons appear at the top-left of the sheet. They control how much detail is visible. Clicking a lower number collapses more rows. Clicking a higher number expands more detail.
Example 1: Regional Sales — Sum by Region with Collapsible Outline
After sorting by Region A–Z, apply Subtotal with "At each change in: Region" and function Sum, with Revenue checked under "Add subtotal to". Excel inserts a subtotal row after each region group. A grand total appears at the bottom. Clicking level button [2] collapses the entire view to a clean region-level summary showing only the subtotal rows and grand total — a one-click management view from a detailed dataset.
Example 2: Multi-Level Subtotals — Region Then Department
You can apply subtotals at two levels by running the command twice. First, sort by Region and apply subtotals by Region. Then sort by Department within Region — ensuring "Replace current subtotals" is unchecked — and apply subtotals by Department. This creates a four-level outline: level 1 shows only the grand total, level 2 shows region subtotals, level 3 shows department subtotals within each region, and level 4 shows all individual rows.
Example 3: SUBTOTAL Function — Totals That Respect AutoFilter
A regular SUM formula always totals all rows in a range — filtered or not. The SUBTOTAL worksheet function, by contrast, ignores hidden rows and returns only the aggregate of visible cells. This makes it the correct function for a totals row in a filtered table. Use function code 9 for SUM, 1 for AVERAGE, 2 for COUNT. Function codes 101–109 additionally ignore rows hidden by Hide Rows (not just AutoFilter).
Example 4: Copy Only Subtotal Rows — Extract a Management Summary
After collapsing to level 2, you can copy only the visible subtotal rows to a new location — for example, to a separate summary sheet or for pasting into a presentation. The critical step is selecting Visible Cells Only before copying. Without this step, the copy operation includes all hidden detail rows, not just the visible subtotals. Go to Ctrl+G > Special > Visible Cells Only to restrict the copy to what is currently visible on screen.
Example 5: Remove Subtotals — Return to Flat Data
Go to Data > Subtotal and click Remove All. Excel removes all subtotal rows, collapses the outline structure, and returns the data to its original flat state. Always run Remove All before loading subtotalled data into Power Query or a PivotTable — both tools struggle with the outline structure and the subtotal rows that Subtotal inserts. Remove All is also the correct first step before changing the grouping column or re-running a different Subtotal configuration.
Example 6: Print the Collapsed View — Management Report
The collapsed level 2 view prints as a clean management summary. Click [2] to collapse to subtotal rows only. Go to File > Print — the print preview shows only the visible rows. Non-visible detail rows are excluded from the print output automatically. Set the print area to the subtotals section only via Page Layout > Print Area > Set Print Area for the cleanest output. This approach produces a board-ready summary from a detailed dataset without creating a separate report sheet.
Troubleshooting Subtotal
Both issues below are caused by the same root mistake: forgetting to sort the data before running the Subtotal command. The third issue is less common but has a specific fix.
Subtotals appear after every single row
The data was not sorted by the grouping column before applying subtotals. The command inserts a subtotal row every time the grouping column value changes. If unsorted, every row has a different group and therefore gets its own subtotal row. Fix this by going to Data > Subtotal > Remove All, then sorting by the grouping column A–Z, and reapplying Subtotal from scratch.
The outline level buttons are missing from the left margin
Go to File > Options > Advanced and scroll to "Display options for this workbook". Ensure "Show outline symbols if an outline is applied" is checked. If the option is already checked, the outline may have been manually cleared — reapply it via Data > Group > Auto Outline, or by removing all subtotals and reapplying the Subtotal command from the beginning.
Copying the subtotal rows also pastes the hidden detail rows
This happens when the Visible Cells Only step is skipped before copying. After collapsing to level 2, select the rows, then press Ctrl+G (Go To) > Special > Visible Cells Only > OK. Only then press Ctrl+C to copy. Without the Visible Cells Only step, Excel copies all rows in the selection range — including the collapsed rows that are hidden from view — and pastes all of them at the destination.
Frequently Asked Questions
- How do I add subtotals in Excel without formulas?Sort your data by the grouping column first. Click inside the data range. Go to Data > Outline > Subtotal. Set "At each change in" to the grouping column, "Use function" to Sum, and check the columns to subtotal under "Add subtotal to". Click OK. Excel inserts subtotal rows after each group and creates a collapsible outline with level buttons on the left margin — all automatically, without writing any formulas.
- What is the difference between Data Subtotal and the SUBTOTAL function?Data > Subtotal is an interface command that physically inserts subtotal rows into your data layout and creates an outline structure. The SUBTOTAL worksheet function is a formula that calculates an aggregate in a single cell while ignoring hidden or filtered rows — it does not modify the worksheet structure at all. Use the Data tab command for reports and printed summaries. Use the SUBTOTAL function for totals rows in filtered tables and dashboards where the aggregate must change when the filter changes.
- How do I copy only the subtotal rows to another sheet?Click the level 2 outline button to collapse to subtotal rows only. Select the visible rows. Press Ctrl+G > Special > Visible Cells Only > OK. Press Ctrl+C to copy. Navigate to the destination sheet and press Ctrl+V. Only the visible subtotal rows are pasted — the hidden detail rows are completely excluded from the paste operation, even though they were within the selected range.
- How do I remove subtotals and return to the original flat data?Go to Data > Outline > Subtotal. In the Subtotal dialog, click Remove All. Excel immediately removes all subtotal rows, collapses the outline structure, and returns the data to its pre-subtotal state. All original data rows are preserved exactly as they were before the Subtotal command was run. Run Remove All before loading the data into Power Query or a PivotTable, as these tools cannot correctly process data that contains subtotal rows in the layout.