How to Collapse Rows in Excel (6 Methods)

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.

Collapse Rows in Excel

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.

Collapse Rows in Excel

  • Then, rightclick the mouse and click the Hide option from the context menu.

Collapse Rows in Excel

  • 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.

Collapse Rows in Excel

  • From the Data Tab in Excel Ribbon click the Group button and choose Group Option.

Collapse Rows in Excel

  •  Select the Rows radio button in the Group window and hit OK.

Collapse Rows in Excel

  • The above steps will make the selected rows grouped as indicated on the left side as shown in the screenshot.

Collapse Rows in Excel

  • 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.

Collapse Rows in Excel

  • 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.

Collapse Rows in Excel

  • See the final output.

Collapse Rows in Excel


2.2 Use of Subtotal Feature

  • Select the whole dataset.

  • From the Data Tab select the Subtotal option.

Collapse Rows in Excel

  • In the Subtotal window select Price as the criteria to add subtotal to and click OK.

Collapse Rows in Excel

  • Finally, we see the output below groups of rows in different levels. 

Collapse Rows in Excel

  •   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.

Collapse Rows in Excel

  • 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.

Collapse Rows in Excel

  • For illustration, click on the down-arrow in the Category column. In the context menu, check the Fruit option only. And hit OK.

Collapse Rows in Excel

  • 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


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

Collapse Rows in Excel

  • Set 0 as Row Height in the input box and click OK.

Collapse Rows in Excel

  • 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.  

Collapse Rows in Excel

  • Now, in the Visibility part hover on the Hide & Unhide option to select the Hide Rows option.

Collapse Rows in Excel

  • 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).

Collapse Rows in Excel

  • 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.


Further Readings

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo