How to Use the Excel Collapse Rows Feature? — 4 Easy Steps
(Note: This guide on the Excel collapse rows feature is suitable for all Excel versions including Office 365.)
If you ever had to use an Excel sheet with thousands of rows, you will certainly agree that it was a daunting task. It is definitely confusing to keep track and analyze all the information, especially with complex spreadsheets.
Thankfully, Excel has an option to collapse certain rows temporarily and group them together for later use. These collapsed rows can be expanded later to the original view and used as usual. This feature will help you create compact and easy-to-read spreadsheets.
For example, let’s assume you have a large worksheet. You wish to view only certain rows that summarize the data in subtotals or grand total. To do this, you can use the Excel collapse rows feature.
In this guide, I’ll teach you how to collapse rows in Excel the easy way.
You’ll learn:
- How to Use the Excel Collapse Rows Feature?
- How to Use a Nested Collapse?
- How to Expand Collapsed Rows?
- How to Clear Collapsed Rows?
- How to Copy and Paste Visible Rows?
Related:
How to Delete a Pivot Table in Excel? 4 Best Methods
How to Indent in Excel? 3 Easy Methods
How to Use the Format Painter Excel Feature? — 3 Bonus Tips
How to Use the Excel Collapse Rows Feature?
Although it might look complicated at first, the Excel collapse rows feature is quite simple to use.
Just follow these steps:
- Make sure that the excel worksheet is in a structured format with properly organized data.
- Select the rows that you wish to collapse, then click on the Data tab and Groups in the Outline group, and then click on Group Rows.
- You will see a ‘-’ sign on the left of column A. When you click on the ‘-’ sign, the selected rows get collapsed. Now the ‘-’ sign changes to ‘+’ which denotes that the rows are hidden.
- Now, toggle between the + and – signs to collapse and expand the rows.
Also Read:
How to Use Goal Seek in Excel? (3 Simple Examples)
How to Insert Multiple Rows in Excel? The 4 Best Methods
How to Autofit Excel Cells? 3 Best Methods
How to Use a Nested Collapse?
If you want to collapse rows inside already existing collapsed rows, repeat the same process. Select the rows and go to Data > Group Rows.
This will nest them together in groups, as shown below.
You can add more nested groups if required. If you feel that you no longer need the rows to be collapsed, just click on the Ungroup option in the Outline feature under the Data tab.
This will first ungroup the outer levels first.
At the top left of the worksheet, you will find level buttons 1,2,3, and 4. These can also be used to expand and collapse the rows at each level of nesting.
How to Expand Collapsed Rows?
You can click on the ‘+’ sign to show the hidden rows to expand the collapsed rows. Alternatively, you can also double click on the twin lines appearing in the collapsed rows area.
How to Clear Collapsed Rows?
The hidden rows can be brought back by clicking on the Data -> Outline -> Ungroup – > Ungroup Rows. It starts by clearing the collapsed rows at the outer level and proceeds to clear the inner level.
You can also see the level buttons at the top-left corner of the worksheet disappearing.
How to Copy and Paste Visible Rows?
If you wish to copy only the visible rows follow these steps:
- First, select the visible rows you want to copy.
- Then, go to Home -> Editing -> Find & Select -> Go To Special and click on Visible Cells Only.
- Press Ctrl+C or right-click on the selected rows and select Copy.
- Paste the copied cells in a different location.
Suggested Reads:
How to Group Worksheets in Excel? (In 3 Simple Steps)
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)
Closing Thoughts
In this guide we saw how to collapse rows in Excel, make use of nested collapse, expand the rows and clear the collapsed rows.
The Excel collapse rows option is a very useful and lesser-known feature in Excel. Do use it to make your spreadsheets user-friendly.
You can find more high-quality Excel guides in our free resources center.
Simon Sez IT has been teaching critical IT software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses by seasoned professionals.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
