Group Rows in Excel: Collapse and Expand Data
Outline groups (also referred to as grouping) in Microsoft Excel can turn messy worksheets and workbooks into tidy dashboards. With a click you hide odetails, show grand total lines, and keep large datasets readable.
Here you will find a step-by-step guide how to group and ungroup rows, use cases, and advanced tips and tricks based on practical experience.
Why Use Grouping in Excel?
Grouping lets you:
View only summary rows while presenting.
Drill into grouped data during data analysis.
Print or export different levels without copying rows.
Keep a clean header section on every page.
For finance professionals, grouping is non-negotiable. Data analysis is only the first step. Clear, transparent presentation is just as critical. When you are able to visually simplify a complex analysis or model, you stand out from people stuck in messy workbooks.
Understanding Grouping and Outline in Excel
When you group selected rows or columns Excel adds outline symbols – small plus and minus buttons in the sheet margin – and level buttons 1 2 3 above the grid. You can nest up to eight levels. Press Ctrl 8 to hide or show the symbols.
A profit and loss statement with rows collapsed through Excel grouping, proving that large workbooks can remain orderly. Read our in-depth guide on how to build a P&L in Excel if interested how this was set up.
Collapsing changes only what you see; formulas, references, subtotal totals, Pivot Table refreshes, and recalculation all work exactly the same. That includes any Excel formulas like VLOOKUP, SUM, or SUBTOTAL that you placed in the last row of a section.
How to Group Rows in Excel
This tutorial shows how to group rows in Excel.
Select the detail rows while leaving subtotals and the final total row unselected.
On the Data tab click the Group button to open the Group dialog.
Choose Rows or Columns and click OK to insert brackets. Alternatively, use keyboard shortcut: Alt + Shift + Right Arrow.
Click the minus icon (-) to collapse the group; click the plus icon (+) to expand it.
To group columns, repeat the same steps and choose Columns in the Group dialog.
For nested groups start with the lowest level, for example months, then group higher levels such as quarters and years.
How to Ungroup Rows in Excel
This tutorial shows how to ungroup rows in Excel.
Select the grouped rows, including the summary row if it sits inside the bracket.
On the Data tab click Ungroup. Alternatively, use keyboard shortcut: Alt + Shift + Left Arrow.
To remove every group on the sheet, choose Clear Outline.
Note: Do not forget to expand the grouping before ungrouping or those previously grouped rows will be hidden. In which case, you will need to manually unhide those.
Must-Know Keyboard Shortcuts to Group in Excel
| Task | Windows Excel Shortcuts | Mac Excel Shortcuts |
|---|---|---|
| Group | Alt + Shift + Right Arrow | Command + Shift + K |
| Ungroup | Alt + Shift + Left Arrow | Command + Shift + J |
| Collapse | Alt + A + H | Control + Option + J |
| Expand | Alt + A + J | Control + Option + K |
Use Cases for Excel Grouping
Financial Dashboards
Group rows for each expense category. Keep the top and bottom rows visible.
Collapse details during meetings. Expand only the block partners ask about.
Sort subtotal rows to rank departments by spend. Excel automatically keeps each detail block with its subtotal while it sorts.
Data Analysis Reports
Insert a subtotal for each region or department and group these rows.
This creates a drill-down report where each region can be expanded or collapsed on demand.
Outline for Printing or PDF Reports
Use grouping to create different levels of detail.
Collapse everything to the highest summary level for a compact printout.
Then expand a specific level if you want a more detailed version.
Combine Excel Groups With Indentation
After grouping, add visual structure. For example:
Select every level two row grouping. Increase indent once.
Select every level three row grouping. Increase indent twice.
This manual step is optional but greatly improves readability in reports.
Tips and Tricks on Excel Groupings
Start collapsing from the highest number (lowest outline hiearchy): This way when you expand an item, it will only expand the item you wanted to expand without showing the details of allk the others groups.
Copying Only Visible Cells: When rows are collapsed, Excel still copies hidden cells by default.
To copy only what is visible, select the range, press Ctrl + G, click Special, choose Visible cells only, and then copy. Or use Alt + ; to select visible cells right away.
Grouping vs. Hiding: Regular hiding simply removes rows or columns from view, and users might not realize data is hidden.
Grouping shows plus/minus controls, which makes it easier for others to expand the data if needed.
We consider hiding rows (or sheets) a bad practice. You hide them, you forget them. Then you share the file and others find sensitive data in hidden rows by chance. The only exception is a simple sheet where people just enter their numbers and would be confused by the technical parts of the file. For every other job, use grouping instead of hiding.
Sort subtotal rows: You can sort by the subtotal rows. Excel automatically keeps each detail block with its subtotal while it sorts.
Avoid Overlapping Groups: If you receive errors or strange results when creating nested groups, it might be because the ranges overlap.
It is often easiest to Clear Outline and start over if this occurs.
Streamline Grouping with Accelerate Excel
Excel's grouping is great, but large or complex outlines can be time-consuming to set up or adjust.
The Accelerate Excel add-in offers quick time-saving utilities that integrate with Excel's existing outline features, including:
Group Rows Based on Indentation: If your rows are visually indented to show a hierarchy, the add-in can create formal Excel groups automatically from those indentations.
Apply Indentation Based on Groups: If you already have groups but your rows are not indented, the add-in can indent them according to their outline level.
Auto-Group Using Subtotals: The add-in can detect SUM or SUBTOTAL formulas and instantly create groups for them.
Add or Remove Subtotal Lines: Insert or remove subtle borders above subtotal rows, which makes your report look neater and helps those lines hide when you collapse the group.
Cleaning Up for Presentation: Collapse or expand all groups across the entire workbook, move the cursor to cell A1, and set the zoom level on all sheets. This is very helpful before printing or sharing a file.
These add-in features save time for anyone who deals with extensive outlines. They use Excel's own grouping under the hood, so you can still use normal Excel tools afterward.
Group Rows Based on Indentation
Apply Indent Based on Grouping
Group and Indent Based on SUM Formulas
Add Collapsible Visual Lines
Conclusion
Excel’s grouping feature is a great way to keep worksheets tidy and easy to navigate. It keeps your models lightweight, gives your peers clear reports, and lets your own productivity shine.
FAQ
What is the difference between grouping and hiding rows in a worksheet?
Grouping shows plus and minus controls (outline) so anyone can expand the data. Hiding removes the rows entirely from view and users may not notice there is more data.
Can I create groups inside a Pivot Table?
Yes. Right-click any field item and choose Group. The outline behaves the same and collapses or expands whole sections of the Pivot Table.
How do I copy only visible cells in a grouped range?
Press Ctrl G then click Special and choose Visible cells only. A faster shortcut is Alt ; before you copy.
Why does Auto Outline fail with overlapping groups?
If ranges overlap Excel cannot decide where one group ends and the next starts. Clear Outline, remove extra blank rows, and run Auto Outline again.
Does grouping slow down large workbooks with complex Excel formulas?
No. Grouping affects only the view layer. Calculation time is unchanged whether a section is collapsed or expanded.
How can I apply conditional formatting that targets only subtotal rows? Use a rule based on a formula such as
=ISTEXT($A1)or=SUBTOTAL(103,$A1)so the highlight triggers only when the row contains the subtotal label.Is there a way to group by indentation automatically without an add-in?
Out of the box Excel does not read indentation for grouping. Accelerate Excel or a VBA macro is required.
Will groups created in Excel carry over to Power BI visuals?
Not directly. Export the grouped data through Power Query first; Power BI will respect the data structure and you can still recreate drilldown visuals there.