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.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


How to Group Rows in Excel

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.

Use Shortcut Key to Group Rows in Excel with Expand Or Collapse

Soon after that, you will see that the rows are grouped with expand or collapse option.

Use Shortcut Key to Group Rows in Excel with Expand Or Collapse

Now follows the same procedures for the other regions to group with expand or collapse option.

Use Shortcut Key to Group Rows in Excel with Expand Or Collapse

Read More: How to Group Rows in Excel (5 Easy Ways)


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

Use Group Command to Group Rows in Excel with Expand Or Collapse

Then follow the same steps for the other regions and after that, you will get expand or collapse option for all the regions.

Use Group Command to Group Rows in Excel with Expand Or Collapse

Read More: How to Group Rows by Cell Value in Excel (3 Simple Ways)


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

Use Auto Outline Command to Group Rows in Excel with Expand Or Collapse

Now have a look that we have made groups for different regions simultaneously.

Use Auto Outline Command to Group Rows in Excel with Expand Or Collapse

Read More: Hide Rows and Columns in Excel: Shortcut & Other Techniques


Similar Readings:


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.

Create Nested Groups in Excel with Expand Or Collapse

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.

Create Nested Groups in Excel with Expand Or Collapse

Related Content: Shortcut to Unhide Rows in Excel (3 Different Methods)


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.

Create Group with Automatic Subtotals in Excel

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.

Create Group with Automatic Subtotals in Excel

Now have a look, we have created the groups and subtotals based on regions simultaneously.

Create Group with Automatic Subtotals in Excel

Related Content: Excel Alternating Row Color with Conditional Formatting [Video]


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.

Expand Or Collapse Rows in Excel

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.

Expand Or Collapse Rows in Excel

Now see that the group with Canada region is collapsed and it is showing a plus sign.

Expand Or Collapse Rows in Excel

If you want to expand that group now, nothing just click the plus sign.

Expand Or Collapse Rows in Excel

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.

Collapse Or Expand the Entire Outline to a Certain Level

Output after pressing 1.

Collapse Or Expand the Entire Outline to a Certain Level

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.

Remove Outline and Ungroup Rows

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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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

Leave a reply

ExcelDemy
Logo