How to Insert Subtotals by Group in Excel
Sometimes you do not want a summary — you want the actual rows, with a total where each group ends. Excel can do it, and then wraps the result in a grouping outline that falls apart the moment anyone sorts, filters or copies the sheet.
Last updated: October 2026
| Region | Rep | Amount |
|---|---|---|
| North | Ana | 100 |
| North | Ben | 200 |
| North Total | 300 | |
| South | Cara | 50 |
| South Total | 50 | |
| Grand Total | 350 |
Subtotals or a summary?
- Subtotals keep every detail row and add totals between them. For a report someone reads line by line.
- A summary — a pivot table, or Group & Summarize — gives one row per group and discards the detail. For a figure someone acts on.
The 1-click way (Tellsheet)
Insert Subtotals by Group
Pick the column to group by and run Insert Subtotals by Group. Every detail row is kept, a total row goes in wherever that column's value changes, and a grand total closes the table — written to a new sheet, as ordinary rows with no outline to break. Choose sum, count, average, minimum or maximum, total every numeric column or just the ones you name, label the rows "North Total" or plain "Total", and optionally separate the groups with a blank row.
On sorting
Totals follow runs of equal values in row order, which is exactly what Excel's own Subtotal does. If the table is not sorted by your grouping column, a group that appears in three separate places gets three subtotals — right for a table ordered by date, wrong for one you assumed was sorted. The summary says so when it happens, rather than re-ordering your rows uninvited: that would be the more destructive of the two mistakes.
Frequently asked questions
How is this different from a pivot table?
A pivot replaces the detail; this keeps it and adds totals between the groups.
Must I sort first?
Only if you want one subtotal per group — otherwise each run gets its own, and you are told.
Which columns are totalled?
Every numeric one, or those you name.
Is my original sheet changed?
No — the result is written to a new sheet.
Related Excel guides
Totals where the groups end
Insert Subtotals by Group keeps your detail and adds the totals — with a preview before anything is written.
Get Tellsheet free See pricing