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 only 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 (with 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 make 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 manually 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 create 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


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


Similar Readings:


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

Download this 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, and don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul
Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo