While making a report for a large dataset the report reader can want to see the summary instead of detailed rows and can want to expand specific groups if needed. There are some amazing ways to do it in Excel. This article will provide you with some quick ways to group rows in Excel with expand or collapse option.
How to Group Rows in Excel: 5 Quick Methods
To explore the procedures, we’ll use the following dataset that represents some salespersons’ sales and profits in different regions. I have calculated the regions’ total sales and profits using the SUM function. First, we’ll learn how to group rows in Excel, let’s start.
Method 1: Use Shortcut Key to Group Rows in Excel with Expand or Collapse
In our very first method, we’ll learn how to group rows using a shortcut key.
If you want to group Canada regions first then select the rows that contain Canada.
Later, just press Shift+Alt+Right Arrow Key.
Soon after that, you will see that the rows are grouped with expand or collapse option.
Now follows the same procedures for the other regions to group with expand or collapse option.
Read More: How to Group Rows in Excel
Method 2: Use Group Command to Group Rows in Excel with Expand or Collapse
Now we’ll use the Group command from the Data tab to group rows in excel with expand or collapse.
Select the rows with Canada regions.
Then click as follows: Data > Outline > Group
Then follow the same steps for the other regions and after that, you will get expand or collapse option for all the regions.
Read More: How to Group Rows by Cell Value in Excel
Method 3: Use Auto Outline Command to Group Rows in Excel with Expand or Collapse
In the previous methods, we had to make groups separately for different regions. But using this method we’ll be able to group all rows based regions at a time.
Select any data from the dataset.
Later, click as follows: Data > Outline > Group > Auto Outline
Now have a look that we have made groups for different regions simultaneously.
Method 4: Create Nested Groups in Excel with Expand or Collapse
A nested group means we can make subgroups within a group. To show that I have added a new column to show the selling items. Have a look that there are printers and laptops selling items in Canada. Now we’ll make a group for printer items within the Canada region.
So select the rows that contain the Printer within the previously created group for the Canadian region.
Then just press Shift+Alt+Right Arrow Key or click Data > Outline > Group.
Now the nested group or subgroup is created successfully. If you have a large dataset then you can make subgroups within a group like this way.
Read More: How to Group Rows in Excel by Name
Method 5: Create Group with Automatic Subtotals in Excel
For the dataset, I have calculated the sum of sales and profits using the SUM function. But there is a way by which you do not need to calculate it, the command will calculate the sum based on regions and make groups for rows at a time. Let’s see how to do it.
Click any data on the dataset.
After that, click as follows: Data > Outline > Subtotal.
And soon after you will get a dialog box named Subtotal.
Now select Region from At each change in section, Sum from Use function section and mark Sales and Profit from Add subtotal to section.
Finally, just press OK.
Now have a look, we have created the groups and subtotals based on regions simultaneously.
How to Expand or Collapse Rows in Excel
Hope you have learned to group rows properly from the previous section. Now we’ll learn how to expand or collapse groups. I think you have noticed already that there is a Minus sign at the lower part of each group. Just click on it and consequently, the group will be collapsed. I have clicked for the Canadian region.
Or you can do it using a command.
Select any data from the group that you want to collapse.
Then click as follows: Data > Outline > Hide Detail.
Now see that the group with Canada region is collapsed and it is showing a plus sign.
If you want to expand that group now, nothing just click the plus sign.
Or click as follows: Data > Outline > Show Detail.
The group is expanded again-
Collapse or Expand the Entire Outline to a Certain Level
If your dataset is so large then it would be feasible if you could collapse or expand the entire outline at a time. No worries, Excel can do it.
Look that there are some numbers above the expand/collapse option. That is showing the group level.
- First level of the group like the group for regions.
- Second level of the group like the group for items within the region.
- No group, shows all the rows.
Press on 1 and you will see that all the groups for regions are collapsed at a time.
Output after pressing 1.
To expand the entire outline, press 3.
All the groups are expanded.
How to Remove Outline and Ungroup Rows
After making an outline or group you may want to learn how to remove outline or ungroup rows. That’s quite easy. First, let me show how to clear outline.
Select any cell of your dataset then click as follows: Data > Outline > Ungroup > Clear Outline.
Then you will spot that Excel has removed the entire outline from the dataset.
To ungroup select the rows of the group then click as follows: Data > Outline > Ungroup.
The rows are now ungrouped.
Remember that, you will have to do it for each group individually.
Read More: How to Group and Ungroup Columns or Rows in Excel
Things to Remember
- Make sure you have press the right shortcut key- Shift + ALT + Right Arrow Key.
- The subtotal command can be applied for sorted data.
- The Auto Outline command will group all the rows above the subtotal row.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
Conclusion
I hope the procedures described above will be good enough to group rows in Excel with expand or collapse. Feel free to ask any question in the comment section and please give me feedback.
