How to Expand and Collapse Rows in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

When you deal with a big dataset, sometimes you need to collapse and expand rows to have a better and more compact version of your dataset. It not only helps to arrange data but also shows a proper view. This article will give you a useful overview of how to expand and collapse rows in Excel. I hope you enjoy this and gather more knowledge about Excel.


How to Expand and Collapse Rows in Excel: Easy Steps

Here we will learn how to Expand and Collapse Rows in Excel.

Collapse Rows in Excel

To collapse rows in Excel, you have to first group your dataset. Here, we are grouping our dataset manually. To group rows in Excel, we can either use an auto outline or group manually. There is a basic difference there. You must have some subtotal rows to apply auto grouping whereas you can apply manual grouping in any case. As our dataset provides a subtotal of three countries’ sales then we can easily apply auto grouping. You can see our dataset here.

Group Rows in Excel

Now, follow these steps to group your dataset:

Steps

  • Select the range of cells C5:C7.

  • Now, go to the Data tab, and in the Outline group, select the Group option.

  • In the Group option, select Group.

  • A Group dialog box will appear where you can select grouping in the rows or in the columns. Click on ‘OK’.

  • It will create a group from cell C5 to cell C7.

Expand and Collapse Rows in Excel

  • We created two more groups. That will create the following appearance.

Expand and Collapse Rows in Excel

You can manually group rows when you have two or more levels of information. One thing you must remember is that there must be no hidden rows. It can eventually group your rows inaccurately.

When we apply grouping in our dataset, you must notice there is a Minus (-) icon at the bottom of every group’s bar. This button will help to collapse rows in Excel or you can use the Hide Detail command.


1. Clicking Minus Icon to Collapse Rows

Steps

  • Create a group of rows before collapsing rows. We will see a Minus (-) icon at the bottom of every group’s bar.

Expand and Collapse Rows in Excel

  • Click on the first Minus (-) icon, it will collapse all the products, amounts from cell C5 to C7 of the United States. At the same time, it will convert the Minus (-) icon to the Plus (+) icon.

Collapse Rows in Excel

Read More: How to Create Collapsible Rows in Excel 


2. Collapse Rows Using Hide Detail Command

Steps

  • You can also collapse rows using the Hide Detail command. To do this select the group of rows that you want to collapse.

Collapse Rows in Excel

  • Now, go to the Data tab in the ribbon and click on Hide Detail.

  • That will eventually collapse rows.

Collapse Rows in Excel


Expand Rows in Excel

To expand rows in Excel, We can also apply two methods.


1. Clicking Plus Icon to Expand Rows

Steps

  • To expand the rows, we need to have a group of rows. When you collapse your group a Plus (+) icon will appear.

Expand Rows in Excel

  • Click on the Plus (+) icon. It will eventually expand the rows.

Expand Rows in Excel

Read More: How to Expand or Collapse Rows with Plus Sign in Excel


2. Expand Rows Using Show Detail Command

Steps

  • Select cell C8.

  • Now, go to the Data tab in the ribbon and select Show Detail from the Outline group.

Expand Rows in Excel

  • It will expand the rows of that group.


Download Practice Workbook


Conclusion

Here, we have discussed how to create a group of rows and we have shown the process of how to expand and collapse rows effectively in Excel. I think you enjoy this article and learn new things. If you have any questions, feel free to ask in the comment box.


Related Articles


<< Go Back to Rows in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo