How to collapse rows in excel
Tutor 5 (70 Reviews)
Excel Tutor
Still stuck with a Excel question
Ask this expertAnswer
To collapse rows in Excel, use the Group feature found in the Data tab on the ribbon. Select the rows you want to collapse, click the Data tab, then click Group in the Outline section, and choose Rows from the dialog box.
What Are Collapsed Rows in Excel
Collapsed rows are hidden sections of data that remain part of your spreadsheet but display as a single summary line with an expand/collapse button. These rows maintain their data and formulas while providing a cleaner view of your worksheet. The feature creates an outline structure with numbered buttons (1, 2, 3) on the left margin that control visibility levels.
Methods to Collapse Rows
Using the Group Feature
- Select the rows you want to collapse by clicking and dragging on the row numbers
- Navigate to the Data tab on the ribbon
- Click the Group button in the Outline section
- Select "Rows" in the Group dialog box
- Click OK to create the collapsible group
The grouped rows display with a minus (-) button on the left margin. Click this button to collapse the rows into a single line. A plus (+) button appears when rows are collapsed, allowing you to expand them again.
Using Keyboard Shortcuts
For Windows:
- Alt + Shift + Left Arrow: Collapse selected grouped rows
- Alt + Shift + Right Arrow: Expand selected grouped rows
- Alt + A + H: Hide detail in outline
- Shift + Alt + Plus: Open the Group dialog
For Mac:
- Command + Option + Left Arrow: Collapse selected grouped rows
- Command + Option + Right Arrow: Expand selected grouped rows
- Control + Shift + K: Open the Group dialog
Creating Multiple Outline Levels
- Create your first group of rows using the standard grouping method
- Select a subset of rows within the existing group
- Click Group again to create a nested level
- Repeat for up to eight outline levels
Each level receives its own outline button (1, 2, 3, etc.) in the left margin. Click these numbers to collapse or expand all groups at that level simultaneously.
How to Collapse Multiple Row Groups at Once
To collapse multiple row groups simultaneously, click the outline level buttons (1, 2, 3) located above the row numbers on the left side of the worksheet. Level 1 collapses all groups, showing only the highest summary level. Level 2 expands the first level while keeping sub-groups collapsed. The highest numbered button expands all groups completely.
Removing Collapsed Rows
Ungroup Rows
- Select the grouped rows by clicking the row numbers
- Navigate to the Data tab
- Click Ungroup in the Outline section
- Choose "Rows" in the Ungroup dialog box
- Click OK to remove the grouping
Clear Entire Outline
- Click any cell within the outlined data
- Go to the Data tab
- Click the arrow below Ungroup
- Select "Clear Outline" to remove all grouping from the worksheet
Auto Outline Feature
Excel can automatically create collapsible rows based on your data structure when formulas reference other rows:
- Place your cursor anywhere in the data range
- Click the Data tab
- Click the arrow below Group
- Select "Auto Outline"
Excel analyzes formulas and creates groups where summary formulas reference detail rows. The feature works best with consistent formula patterns and structured data layouts.
Customizing Outline Settings
- Click the Data tab
- Click the dialog launcher in the Outline group (small arrow in the corner)
- Adjust these settings in the Settings dialog:
- Summary rows below detail: Check or uncheck based on data structure
- Summary columns to the right of detail: Adjust for column grouping
- Automatic styles: Apply formatting to outline levels
Troubleshooting Common Issues
Group Button Grayed Out
The worksheet is protected. Unprotect it through the Review tab > Unprotect Sheet before grouping rows.
Outline Buttons Not Visible
- Go to File > Options (Windows) or Excel > Preferences (Mac)
- Select Advanced
- Navigate to "Display options for this worksheet"
- Check "Show outline symbols if an outline is applied"
Cannot Create More Than Eight Levels
Excel limits outline levels to eight. Restructure your data or use filtering instead for complex hierarchies.
Grouped Rows Not Printing Correctly
Expand all groups before printing, or adjust print settings to include hidden rows through Page Layout > Print Area > Set Print Area after expanding groups.
. Was this Helpful?Get Online Tutoring or Questions answered by Experts.
You can post a question for a tutor or set up a tutoring session
Answers · 1
How to add secondary axis in excel
Answers · 1
How to switch columns to rows in excel
Answers · 1
How to subtract on excel
Answers · 1
How to combine two cells of text in excel
Answers · 1