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

Get FREE Advanced Excel Exercises with Solutions!

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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF