Durjoy Paul
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.
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.
- We create two more groups. That will create the following appearance.
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.
- 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.
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.
- Now, go to the Data tab in the ribbon and click on Hide Detail.
- That will eventually collapse rows.
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.
- Click on the Plus (+) icon. It will eventually expand the rows.
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.
- 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
- How to Lock Rows in Excel
- How to Number Rows in Excel
- How to Select Row in Excel If Cell Contains Specific Data
- How to Create Collapsible Rows in Excel
- How to Expand or Collapse Rows with Plus Sign in Excel
- How to Resize All Rows in Excel
- How to Create Rows within a Cell in Excel
- How to Select Every Other Row in Excel
- How to Copy Every Nth Row in Excel
