Excel like a finance pro.
Add subtotals to a sorted list automatically
AltA, BTables & dataA subtotal at every change of department, plus the outline to collapse it.
Difficulty
1What it does
Data → Subtotal inserts a total row at every change in a column and wraps the whole thing in a collapsible outline, in one dialog. It only works on SORTED data — it breaks at every change of value, so an unsorted list gets a subtotal every few rows. The totals it inserts use the SUBTOTAL function rather than SUM, which is why the grand total doesn't double-count the subtotals above it: SUBTOTAL deliberately ignores other SUBTOTALs inside its range.
2The shortcut
- 1Sort by the column you want to break on first — this doesn't work without it.
- 2Press
Alt + A,B. Set **At each change in** to that column and **Use function** to Sum. - 3Collapse with the outline buttons. **Remove All** in the same dialog takes it all back out.
3Before → after
Press the shortcut to play it on a sample sheet.
Before — sorted, but flat
| A — Line | B — Amount | |
|---|---|---|
| 1 | Marketing · Globex | 9,800 |
| 2 | Marketing · Initech | 2,750 |
| 3 | Sales · Acme | 4,200 |
| 4 | Sales · Umbrella | 6,300 |
Sort by the break column FIRST. Unsorted, this inserts a subtotal every few rows, because it breaks at every change of value.
4When you use it
- Total expenses by department on a sorted transaction list.
- Break a sales list by region with a count and a sum.
- Produce a quick summary without building a pivot table.
Want these moves already done for you? Wauvel's free tools download as branded, formula-driven Excel models — Tables, named ranges, and all.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.