# How to Group Rows in Excel with Expand or Collapse (5 Methods)

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.

## 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.

## 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.

## Related Articles

<< Go Back to Group Cells in Excel | Outline in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF