Microsoft Excel · Data preparation · Intermediate
Group rows and create subtotal outlines in Excel
Group rows and create subtotal outlines in Excel with a complete practice example, expected results and recovery checks.
Official documentation reviewed; example logic independently checked; native Excel execution not performed · Editorial standards
Quick answer: Sort a plain record range by the grouping field, then choose Data → Subtotal. Excel inserts group summaries and an outline so you can collapse the details without deleting them. The command is not supported directly inside an Excel table.
Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.
Prepare grouped records
Create a blank sheet with this data at A1:C5:
| Region | Item | Amount |
|---|---|---|
| North | Cable | 120 |
| South | Hub | 90 |
| North | Stand | 80 |
| South | Adapter | 110 |
Use an ordinary range, not Home → Format as Table. The four detail amounts total 400, with North 200 and South 200. Save a clean copy before inserting subtotal rows; row addresses will change when those summary rows are inserted.
Select the full A1:C5 block and use Data → Sort to sort Region A to Z, keeping each complete record together. North’s two records must now be contiguous, followed by South’s two. Subtotals operate at changes in the grouping column, so an unsorted North/South/North/South list produces separate groups rather than the intended regional summary.
Insert one level of subtotals
Select a cell in the sorted range and choose Data → Subtotal in the Outline group. Set At each change in to Region, Use function to Sum, and Add subtotal to to Amount only. Leave Summary below data selected, and do not add page breaks for this small example. Confirm with OK.
The result should contain a North subtotal of 200, a South subtotal of 200 and a grand total of 400. Each original detail record must still be present. Excel’s generated SUBTOTAL formulas avoid treating the inserted subgroup results as extra detail amounts in the grand total; do not replace that grand total with a blind SUM over every displayed number.
Collapse and expand the outline
Use the outline level buttons beside the row headings. For this single-level example, the most summarized view shows the grand total; the next shows group totals plus the grand total; the detailed view shows the source records as well. Use the plus and minus controls beside an individual group to expand or collapse that group’s details.
A collapsed row is hidden, not deleted. Expand North and verify both Cable 120 and Stand 80. You should be able to explain the 200 subtotal from the underlying two records before using the summarized view in a report.
Change an amount and reconcile
Locate the North/Cable detail record by its labels, not its original row number. Change its Amount from 120 to 150. With automatic calculation active, North’s subtotal should become 230, South should remain 200, and the grand total should become 430. The outline layout does not have to be rebuilt simply because an amount changed.
Save the summary workbook as regional-subtotals.xlsx, reopen it and expand the groups to verify all four detail records and totals North=230, South=200 and grand total=430. If totals look missing, clear any ordinary filters before diagnosing the outline. If the values do not recalculate, inspect calculation behavior rather than overwriting a subtotal formula with a typed answer; the existing calculation-mode guide explains an older Windows workbook-mode pitfall.
Remove the summaries cleanly
For an optional removal experiment, first save a separate copy as subtotal-removal-test.xlsx. In that copy only, select a cell in the subtotaled list, open Data → Subtotal, and choose Remove All. Expect four detail rows, with the updated 150 still present. This removes inserted summaries; it does not restore the old 120 input. Close the experiment and return to regional-subtotals.xlsx, which should still contain North=230, South=200 and grand total=430 with its outline. If Subtotal is unavailable because the data is an Excel table, prefer a separate plain-range practice copy or a PivotTable rather than casually removing table functionality from a production model.
Official references
Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.