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.
Download Practice Workbook
Download this practice workbook.
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:
- 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
- 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.
Read More: Group Rows with Plus Sign on Top in Excel
2. Collapse Rows Using Hide Detail Command
- 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.
Related Content: How to Move Rows Down in Excel (6 Ways)
Expand Rows in Excel
To expand rows in excel, We can also apply two methods.
1. Clicking Plus Icon to Expand Rows
- 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.
Related Content: Shortcut to Unhide Rows in Excel (3 Different Methods)
- How to Color Alternate Rows in Excel (8 Ways)
- How to Group Rows in Excel Pivot Table (3 Ways)
- Hide Rows and Columns in Excel: Shortcut & Other Techniques
- VBA to Hide Rows in Excel (14 Methods)
- Unhide All Rows Not Working in Excel (5 Issues & Solutions)
2. Expand Rows Using Show Detail Command
- 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.
Related Content: How to Group Rows by Cell Value in Excel (3 Simple Ways)
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.