Financial models, quarterly reports, and large project plans all share one problem: they are too long to navigate comfortably. Excel's Grouping feature solves this by creating collapsible outline levels — similar to the expand/collapse sections in a modern code editor or project management tool. Users can hide detail rows with one click and expose them again instantly, without deleting or hiding data permanently.
Part 1: Grouping Rows and Columns
To group rows: 1. Select the rows you want to collapse (click the row numbers on the left). 2. Go to Data tab > Outline group > Group > Group... (or just click Group — Excel auto-detects rows vs. columns based on your selection). 3. A bracket and +/- button appear on the left margin.
To group columns: 1. Select the columns you want to collapse (click the column letters at the top). 2. Go to Data tab > Outline group > Group. 3. A bracket and +/- button appear above the column headers.
You can nest multiple group levels (up to 8 levels deep) to create a hierarchical outline.
Part 2: Expand and Collapse Controls
Once groups are applied, outline controls appear on the left (for row groups) or top (for column groups):
| Control | Action |
|---|---|
– button |
Collapses that group (hides the rows/columns) |
+ button |
Expands that group (shows the rows/columns) |
Level number 1 |
Collapse all groups to the outermost level |
Level number 2 |
Expand to the second level of nesting |
Level number 3 |
Show all detail (innermost level) |
Click the level buttons in the top-left corner of the row/column header area to control all groups simultaneously.
Part 3: Auto Outline
If your worksheet contains summary formulas (like SUM rows below detail rows), Excel can detect the outline structure automatically:
Data tab > Outline group > Group dropdown > Auto Outline
Excel places the +/- buttons above or below (and left or right of) detail rows based on where it finds the summary formulas. The result is a complete, multi-level outline without manually selecting each group.
Part 4: Ungroup and Clear Outline
To ungroup a specific group: 1. Select the same rows or columns that form the group. 2. Data tab > Outline > Ungroup.
To remove ALL grouping from the sheet: Data tab > Outline group > Ungroup dropdown > Clear Outline
This removes all outline levels and +/- buttons without deleting any data.
Part 5: Grouping vs. Hiding — Key Difference
| Feature | Grouping | Hiding |
|---|---|---|
| Toggle method | +/– buttons or level numbers | Right-click > Hide / Unhide |
| Visual indicator | Outline bracket + level buttons | Skipped row/column numbers |
| User can find hidden rows? | Yes — easily, via + button | Only if they know to look |
| SUBTOTAL behavior | Collapsed rows are excluded from SUBTOTAL(101–111) |
Hidden rows excluded from SUBTOTAL(101–111) |
| Printing | Collapsed groups are not printed | Hidden rows not printed |
| Best for | Structured reports with drill-down | One-time hiding of sensitive data |