Group & Outline
Fold a big worksheet into its story — a self-study lesson you can turn around and teach.
How this deck works
Driving it
Click (or → / Space) — build the current slide one step at a time, then move on
← — undo one step, then one slide
E — edit any text in place (Ctrl+S saves a copy)
Rail (left) — jump between the 7 sections
The colour key — memorise this
Prerequisite knowledge
✔ SUM and cell ranges
=SUM(C2:C4)
This lesson assumes you can write a basic SUM over a range of cells, and read a reference like C2:C4 — "column C, rows 2 to 4".
✔ Sorting with Data ▸ Sort
Data ▸ Sort & Filter ▸ A→Z
Subtotals only work on data sorted by the grouping column. You'll need to know how to sort a table by one column for this part — if that's unfamiliar, pause here, look it up, and come back.
Meet the two datasets
NT Cyber Pulse 2026 — a door-to-door survey of 3,895 Territorians: what device they carry, and how they look after their accounts. One skill, two worlds — if you can fold both sheets, you actually understand it. (All numbers are synthetic, built for practice.)
Full sheet also counts Android / iPhone / no-device owners and low cyber-awareness scores.
The old way: build every total by hand
Before outlines, a "summary" meant this: insert a blank row after each council block, label it, and type a SUM for every column. Watch how much typing that is — for just 9 rows of data.
The five pains of the hand-built summary
Every one of these is familiar to anyone who has wrangled a long sheet. None of them are your fault — and every one has a fix waiting in this deck.
Buried summary
The totals live somewhere in a sea of detail rows. Scrolling and squinting is the interface.
→ Collapse fixes thisHand-rolled ranges
Every =SUM(C2:C4) was counted by eye — and goes stale the moment rows move.
→ The Subtotal dialog fixes thisDouble counting
Sum a whole column that already contains total rows and everything is counted twice.
→ SUBTOTAL fixes thisInvisible hiding
Hiding rows by hand leaves no visible trace of what's hidden, or where, or how to bring it back.
→ Outline symbols fix thisCopy grabs everything
Copy your tidy collapsed "summary" and the hidden detail rows ride along into the paste.
→ Go To Special fixes thisAnatomy of an outline
An outline is Excel folding your rows into levels, like a paper map: level 1 is the whole Territory in one line, level 2 shows a line per council, level 3 opens the full detail. The controls live in a strip down the left edge of the grid — here's every part of it.
1 Level buttons
1 = grand total only · 2 = council totals · 3 = every row. One click sets the whole sheet's zoom level.
2 Brackets
Each vertical bar spans one group's detail rows — you can see what belongs together, and what's currently folded.
3 Fold buttons
The − sits beside each group's summary row. Click it to fold that group; it becomes a + that unfolds it.
4 A folded view
Level 2: three council lines and a grand total. The 9 detail rows are hidden, not deleted — the row numbers still jump 2 → 6.
Group, one action at a time
Grouping draws one bracket. You pick the detail rows — not their summary row, and not the header — and tell Excel they belong together. We've already added the summary rows here; the smart way to fill them comes next section.
Fold, unfold, and the level buttons
All three councils are grouped now. This is where the outline pays rent: the same sheet can be a one-line answer, a three-line report, or the full detail — and switching between them is one click, not ten minutes of hiding rows.
SUBTOTAL, one argument at a time
The summary rows need formulas, and there's a purpose-built function for the job. It takes just two things: what to do, and which rows to do it to.
Why not SUM? The grand-total trick
The grand total has a problem: the range under it now holds detail rows and three council totals. Point a formula at the whole column and here's what each function does with it.
function_num — what to do at each cut
The first argument picks the calculation. Same formula shape every time — only the number changes. These are the ones worth memorising:
Before the shortcut: sort first
Everything so far, Excel can do for you — one dialog inserts every summary row, every formula and the whole outline. But it cuts a new subtotal every time the grouping value changes, so the same council scattered through the sheet gets several totals. One rule protects you.
The Subtotal dialog, one field at a time
Data ▸ Outline ▸ Subtotal opens one small dialog. Three questions — where to cut, what to calculate, which columns — and it does everything from the last three sections in one hit.
Press 2. Read the Territory's story.
Collapsed to level 2, the 2,995-person survey becomes four lines — and the folded sheet says something the full detail never could say this clearly.
Same move, new world: Alice Springs
The Alice sheet is shaped differently — each suburb has three age-band rows. The dialog doesn't care. Sort by Suburb, cut at each change in Suburb, and the outline appears. Then look inside the blocks.
Layer it, replace it, remove it
Three checkboxes and one button control the dialog's whole life cycle. Run it more than once and this is what decides what happens to the subtotals you already have.
1 Layer another level
Run Subtotal again with Replace current subtotals unticked — e.g. cut Alice by Suburb, then again by Age band. The new totals nest inside the old ones: a four-level outline, with level buttons 1–4.
2 Start over
Leave Replace current subtotals ticked and the new pass swaps out the old one — handy when you picked the wrong column or function.
3 Take it all away
Remove All deletes the inserted subtotal rows and clears the outline. Your original data comes back untouched — the whole thing was always just a layer on top.
Columns fold too
Rows aren't special. Here's a different cut of the survey world: phishing reports logged in each regional hub, month by month, with a quarterly total column after each block of months. Group the month columns and the bracket appears above the letters.
Auto Outline: one click, whole structure
If your summary rows already contain formulas — SUBTOTAL or SUM pointing at the detail above them — Excel can read that structure. It follows the formulas to work out which rows belong to which total, and draws every bracket for you.
Copy only what you see
The folded view looks like four rows — but a normal copy takes the hidden detail rows along with it, and your tidy summary arrives with 9 stowaways. One old dialog fixes it.
Taking it apart again
Three exits, from gentlest to most thorough. The thing to hold onto: an outline is a view laid over your data — none of these delete a single survey answer.
1 Ungroup one bracket
Select the grouped rows ▸ Data ▸ Outline ▸ Ungroup (or Alt+Shift+←). Removes that bracket only — totals and other groups stay.
2 Clear Outline
Ungroup ▾ ▸ Clear Outline wipes every bracket and level button at once. Careful: clear while groups are folded and those rows stay hidden — fix with Home ▸ Format ▸ Hide & Unhide ▸ Unhide Rows.
3 Subtotal ▸ Remove All
The deepest reset: clears the outline and deletes the subtotal rows the dialog inserted. The sheet returns to the raw 9 rows you started with.
The cheat sheet — screenshot this one 📸
SUBTOTAL(n, range)
| 9 | SUM — the workhorse |
| 1 | AVERAGE |
| 2 | COUNT |
| 4·5 | MAX · MIN |
| +100 | also skip hand-hidden rows |
Keyboard
| Alt+Shift+→ | Group selection |
| Alt+Shift+← | Ungroup selection |
| Alt+Shift+= | Show a group's detail |
| Alt+Shift+− | Hide a group's detail |
Remember
- Sort before the Subtotal dialog
- Inside grouped data: SUBTOTAL, never SUM
- The − button sits at the summary row
- Level 1 grand · 2 groups · 3 detail
- Remove All undoes everything, safely
- Folding hides rows — never deletes them
When it goes wrong — the six usual suspects
Subtotal is greyed out
Your data is formatted as an Excel Table — the Subtotal command refuses to run on one.
Fix: Table Design ▸ Convert to Range, then try again.
"Darwin Total" ×2
The data wasn't sorted, so the same council appears in separate runs — each run gets its own total.
Fix: Remove All, sort by the grouping column, re-apply.
Grand total is double
A SUM was pointed at a range that already contains subtotal rows — everything counted twice.
Fix: use =SUBTOTAL(9, …) — it skips other SUBTOTALs automatically.
Rows vanished
The outline was ungrouped or cleared while groups were folded — the detail rows stayed hidden.
Fix: select across the gap ▸ Home ▸ Format ▸ Hide & Unhide ▸ Unhide Rows.
No + − symbols
The outline exists but the symbols are switched off for this worksheet.
Fix: File ▸ Options ▸ Advanced ▸ tick "Show outline symbols if an outline is applied".
Golden rule
Almost every mess traces back to the workflow order: sort → Subtotal → fold → copy visible. Re-run it in that order and the sheet heals.
Now teach it
Your six talking points
- An outline folds rows into levels — hidden, never deleted. Level 1 grand · 2 groups · 3 detail.
- Two ways in: Group draws the brackets yourself; the Subtotal dialog draws them and writes the totals.
- Sort first — the dialog cuts at every change in the grouping column.
- Totals inside grouped data are SUBTOTAL(9,…), never SUM — they skip each other, so nothing double-counts.
- The − button lives at the summary row; level buttons fold the whole sheet at once.
- Copy a folded view with Go To Special ▸ Visible cells only.
Three challenges — click to reveal each answer
If you can fold 2,995 rows into the four that matter, you own it.