Group and Outline Rows and Columns
Grouping is hiding with the door left visible: the rows fold away, but a + button and a numbered level bar stay in the margin to say that detail exists underneath. That is the whole reason to prefer it in anything another person will open, since hidden rows leave nothing behind but a skip in the numbering and readers miss it every time. The word covers a second, unrelated feature as well — grouping sheets means Ctrl+clicking tabs so one edit lands on all of them at once, and that one is responsible for a good share of the accidents in this topic.
The formula
fold rows away, visibly Data > Group; the margin keeps a + and a level bar
fold them away invisibly Home > Format > Hide, which leaves no trace at all
build the outline automatically Data > Group > Auto Outline, from existing SUBTOTAL formulas
totals and outline in one step Data > Subtotal (sort by the grouping column first)
remove every group on the sheet Data > Ungroup > Clear Outline
the margin symbols have vanished Ctrl+8 toggles them; the groups themselves are untouched
one edit landing on every tab the SHEETS are grouped: right-click a tab > Ungroup SheetsA worked example
A budget workbook goes to twelve people, and only four quarterly totals should be on screen when it opens — but nobody should have to be told that the monthly detail is in there.
Group each quarter's months, then click the level 1 button above the margin before saving.
The file opens showing four totals, each with a + beside it, and any reader clicks a + to see the months. Hiding those rows instead would have produced the identical screen with none of the signalling: the row numbers would skip, no button would appear, and a reader with no reason to suspect anything is missing would quote the summary as the whole picture.
Which one do I need?
| If you want to… | Use |
|---|---|
| Detail should fold away but stay findable | Data > Group, and the margin keeps a + button and a level bar advertising it |
| Detail should be genuinely out of the way and out of mind | Hiding leaves no button and no level bar, so nobody can tell anything is missing |
| The sheet already has SUBTOTAL formulas in it | Data > Group > Auto Outline reads them and builds the entire outline in one command |
| You want the subtotals and the outline together | Sort by the grouping column first, then Data > Subtotal, which inserts both in one pass |
| Every group on the sheet should go | Data > Ungroup > Clear Outline strips the outline and leaves every row expanded |
| Typing on one tab is changing all your tabs | The sheet tabs are grouped, not the rows. Right-click any tab and choose Ungroup Sheets |
| The + and - buttons have disappeared from the margin | Ctrl+8 toggles the outline symbols; the groups are still there |
| You want the data summarised rather than folded | A pivot table expands and collapses too, and aggregates while it does it |
Frequently asked questions
Why does typing on one sheet change every sheet at once?
The sheet tabs are grouped — usually from Ctrl+clicking or Shift+clicking a second tab and forgetting about it. Excel shows [Group] beside the file name in the title bar while this is in effect, and every edit, including formatting and deletions, lands on all selected tabs simultaneously. Right-click any tab and choose Ungroup Sheets, or simply click a tab that is not part of the group.
Why is the collapse button at the wrong end of my group?
Excel puts the button beside the summary row and assumes summaries sit below their detail. If your totals sit above instead, open the dialog launcher on the Data tab's Outline group and untick "Summary rows below detail" — there is a matching "Summary columns to right of detail" for column groups. Existing groups do not move, so clear the outline and rebuild it after changing the setting.
Where did the + and - buttons in the margin go?
Ctrl+8 toggles the outline symbols on and off and is easy to hit by accident; the groups themselves survive untouched and only the margin is suppressed. If Ctrl+8 does not bring them back, check File > Options > Advanced and the per-worksheet setting "Show outline symbols if an outline is applied". A sheet with no groups at all naturally has no margin to show.
How do I get rid of subtotals and the outline they created?
Go back through the same door: Data > Subtotal, then the Remove All button. Deleting the subtotal rows by hand leaves the outline brackets behind and usually leaves a few stray totals as well. For an outline you built yourself rather than one Subtotal generated, Data > Ungroup > Clear Outline removes every group on the sheet in one command.
New guides and tools, once a month
DE + EN · double opt-in · no spam