Al Arafat Siddique
The feature in Excel to collapse rows makes them disappear from the display. You may have a lot of rows in your dataset but don’t need to work with them all at once. To hide and unhide rows gives us the flexibility to navigate through the spreadsheet easily and also makes it look clean.
Download Practice Book
Download this practice workbook to exercise while you are reading this article.
6 Methods to Collapse Rows in Excel
This article will explain 6 methods to collapse rows in Excel with suitable examples step by step. Let’s introduce the dataset first, we’ll be working on. We have an order list of a bunch of products of two categories- Fruit and Vegetables. The dataset also provides the customer’s name and price for each of the orders.
1. Hide Rows in Excel Using Context Menu
The first method shows how to hide rows in Excel using the context menu. In our example dataset, there are three orders for Banana. Let’s hide them using the context menu.
- At first, select the rows containing orders for Banana i.e. rows 5,6, and 7.
- Then, right–click the mouse and click the Hide option from the context menu.
- Finally, rows 5, 6, and 7 are collapsed.
Read more: How to Hide Rows in Excel
2. Collapse Rows in Excel by Grouping
This method will show how to use Group and Subtotal features to collapse rows in Excel. Let’s group our dataset first.
2.1 Use of Group Feature
- Select the rows you want to group and collapse. Here, we selected rows 5 to 10 which contain order details for the Fruit category.
- From the Data Tab in Excel Ribbon click the Group button and choose Group Option.
- Select the Rows radio button in the Group window and hit OK.
- The above steps will make the selected rows grouped as indicated on the left side as shown in the screenshot.
- From this point, there are 2 ways we can collapse the grouped rows:
i) Use of the Minus (-) sign to collapse the rows:
- Click the minus sign shown in the screenshot.
- Finally, we can see that rows 5-10 are collapsed.
ii) Click the Boxed Numbers:
After that, the grouping of rows, there are some boxed numbers in the upper left-hand corner of the spreadsheet. They indicate the level of the outline.
- Click the boxed number 1.
- See the final output.
2.2 Use of Subtotal Feature
- Select the whole dataset.
- From the Data Tab select the Subtotal option.
- In the Subtotal window select Price as the criteria to add subtotal to and click OK.
- Finally, we see the output below groups of rows in different levels.
- Now, follow the steps described in the 2.1 section (Click Minus or Boxed Numbers) to hide the rows you want.
Read more: How to Group Rows in Excel
3. Use Filtering to Collapse Rows in Excel
From a large collection of data, we can filter rows to hide them from the view to facilitate the data analysis in Excel. Let’s see an example:
- At first, select the whole dataset.
- Then, from the Excel Ribbon click the Data Tab and choose Filter.
- There we see down-arrows appeared as shown in the screenshot below. Clicking on the down-arrows provides us the option to filter rows based on specified criteria.
- For illustration, click on the down-arrow in the Category column. In the context menu, check the Fruit option only. And hit OK.
- In the output, we can see that our dataset is now filtered for Fruit items only and the rows for Vegetables are collapsed.
Similar Readings
- How to Group Rows by Cell Value in Excel (3 Simple Ways)
- Group Rows in Excel with Expand or Collapse (5 Methods)
- How to Hide Rows Based on Cell Value in Excel (5 Methods)
- Resize All Rows in Excel (6 Different Approaches)
4. Set Row Height to Collapse Rows
Another easy way to hide rows in Excel is to use the Row Height option. Let’s dive in:
- Select the rows( 5-7) that need to be collapsed. Then, right-click the mouse and select the Row Height
- Set 0 as Row Height in the input box and click OK.
- As a result of the above steps, rows 5-7 get collapsed successfully.
5. Use Home Tab to Hide Rows in Excel
The Home Tab of Excel provides the option to hide and unhide columns. In this method, we are going to explore that option.
- At first, select the rows by dragging the mouse. Here, we selected rows 5-10 that contain order details for the Fruit category. Then, from the Home Tab click on Format.
- Now, in the Visibility part hover on the Hide & Unhide option to select the Hide Rows option.
- Here is the expected result, rows 5-10 are now hidden.
Read more: Hide Rows and Columns in Excel: Shortcut & Other Techniques
6. Keyboard Shortcuts to Hide Rows in Excel
Keyboard shortcuts can perform a task easily and quickly. Excel provides keyboard shortcuts to hide rows. Let’s dive in:
- In the first step, we have to select the rows(5-10).
- Now press Alt + H + O + R and see the result.
Things to Remember
Keyboard Shortcuts:
- Use Shift + Space to select the entire column in the dataset.
- In Method 2: Use Shift + Alt + Right Arrow(→) to Group selected rows and Shift + Alt + Left Arrow(←) to Ungroup rows.
Conclusion
Now, we know the methods to hide or collapse rows, it would encourage you to take the benefit of Excel’s hide and unhide feature more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
