Worksheet often comes up with a lot of detailed information that might be difficult to read and analyze. Fortunately, Microsoft Excel offers smart features to organize data in groups which allows the rows to be collapsible and expandable. In the compact view, one can easily analyze the whole content available in the Excel sheet.
Therefore, in this guidance, you will read about how to group rows in Excel and the best way to expand and collapse rows in Excel.
How to Group Rows in Excel?
Grouping the rows in an Excel sheet is best for a structured worksheet that has a column heading and no blank rows, summary row for each subset of rows. With well- organized data, you can use the following ways to group rows in Excel.
Method 1: Create an Outline to Group Rows Automatically
If your data contains only one level of information then you can quickly let Excel group rows automatically for you. Below are the following steps on how to get it done.
Step 1: To begin, select one cell in any of the rows that you want to group.
Step 2: Next to this, you can go to the Data tab and click on the Outline group.
Step 3: Then, click the arrow under the Group option and select Auto Outline option.
Once you are done performing the above-mentioned steps, the rows will be grouped smoothly and the outline bars representing the different levels of the data have been added to the left before column A.
Note: if summary rows are located above a group of detail rows then before creating an outline, follow the following steps:
Step 1: Go to the Data tab and click the Outline group.
Step 2: Next to this, click the Outline dialog box and clear the Summary rows below detail option.
Step 3: Click on the OK button to successfully apply your actions.
When the outline is created, you can easily hide or show details within a certain group just by clicking the Minus (-) or Plus (+) sign for that group. Moreover, you can expand and collapse rows in Excel 2013. All you are required to click on the level buttons located at the top left corner of the Excel sheet.
Method 2: Group Rows Manually
If your Excel sheet contains two or more than two levels of information then Excel’s Auto Outline may not group the data correctly. In this scenario, you can simply group rows manually so that it does not affect your data and organizes it in a good manner. To do so, you can follow the steps mentioned below.
Note: Before you apply any actions to group rows, make sure there are no hidden rows in your data set otherwise your sheet might not be grouped properly.
Step 1: Create Outer Groups (Level 1)
- Choose one of the larger subsets of data. This should also include all of the intermediate summary rows and their detail rows.
- Now, on the Data tab, you can click on the Group button clicking (in the Outline group).
- After this, you can select Rows and click on the OK button.
This will quickly add a bar on the left of the Worksheet that stretches the selected rows. In this way, you can create as many as outer groups are needed.
Tip: To create new groups fastly, you can also use shortcuts. Press the Shift + Alt + Right Arrow keys together from your Keyboard.
Step 2: Create Nested Groups (Level 2)
- To create nested groups or inner groups, you can select all detail rows above the related summary row.
- Then, you can click the Group button.
- This will successfully create inner groups in your Excel sheet.
Step 3: Add More Grouping levels if Needed
In case more data is added to your Worksheet then you may require more grouping levels for which you need to create more outline levels. To do so, you can follow the steps mentioned below.
- Insert the Grand total row in the table.
- Then, add the outermost outline level.
- To make it happen, you can select all the rows except the Grand total row and click on the Data tab then Group button to select Rows.
How to Create Collapsible Rows in Excel?
In Excel, one of the most useful features of grouping is the ability to show and hide the detail rows for a particular group. In addition, you can collapse and expand the outline to a certain level in a few clicks. In this guide, you can learn how to expand and collapse rows in excel. To know the process of excel collapse rows, look for the following guidance.
Create Rows within a Group
If you want to learn how to collapse rows in excel 2013, you can use the Minus (-) sign located at the below of the group bar.
Another way to collapse rows in Excel sheets is to select any cell in the group. Then, you can click the Hide Detail button on the Data tab, in the Outline group.
This way, the group would be minimized to the summary row and the detail rows would be hidden.
How to Expand Rows in Excel?
To expand rows within a group, you can click any cell in the visible summary rows. Then, you can click the Show Detail button available on the Data tab, in the Outline group. Or, you can also use the Plus (+) sign in order to expand the collapsed group of rows.
How to Ungroup a Certain Group of Rows?
After grouping the rows, if you wish to ungroup them in the future then you can follow the following simple steps.
Step 1: First of all, select the rows that you want to ungroup.
Step 2: Go to the Data tab and click the Outline group.
Step 3: At this point, you can click on the Ungroup button or use Shift + Alt + Left Arrow shortcuts from your Keyboard.
Step 4: Now in the Ungroup dialog box, you can select the Rows option and hit the OK button.
Note: It is not easy to ungroup non-adjacent groups of rows. So, you will have to repeat the aforementioned steps for each group individually.
Conclusion
If you were seeking the easiest process for excel collapse rows, group rows in Excel or expand and collapse rows in excel then this particular guide is surely going to teach you how to group rows in excel with expand collapse in the simplest way.