How to Group Rows in Excel using the Expand or Collapse features – 5 Methods

This is the sample dataset.

Method 1 – Using a Shortcut Key to Group Rows in Excel with the Expand or Collapse feature

To group data for Canada:

• Select the rows that contain Canada.
• Press Shift+Alt+Right Arrow Key.

Rows are grouped with the expand or collapse option.

Apply the same procedure for the other regions.

Read More: How to Group Rows in Excel

Method 2 – Use the Group Command to Group Rows in Excel with the Expand or Collapse feature

• Select the rows containing Canada.
• Click: Data > Outline > Group

Follow the same steps for the other regions.

Read More: How to Group Rows by Cell Value in Excel

Method 3 – Use the Auto Outline Command to Group Rows in Excel with the Expand or Collapse feature

• Select any data in the dataset.
• Click: Data > Outline > Group > Auto Outline

Groups were created for different regions:

Method 4 – Create Nested Groups in Excel with the Expand or Collapse feature

A new column was added to show the selling items.

Group the printer items within Canada.

• Select the rows that contain Printer within the Canada group.

Press Shift+Alt+Right Arrow Key or click Data > Outline > Group.

The nested group or subgroup is created.

Read More: How to Group Rows in Excel by Name

Method 5 – Create a Group with Automatic Subtotals in Excel

• Click any data in the dataset.
• Click: Data > Outline > Subtotal.

• Select Region in At each change in.
• Choose Sum in Use function.
• Check Sales and Profit in Add subtotal to.
• Click OK.

Groups and subtotals were created based on regions.

How to Expand or Collapse Rows in Excel

To collapse groups, click the Minus sign at the lower part of each group.

You can  also do it using a command:

• Select any data in the group you want to collapse.
• Click: Data > Outline > Hide Detail.

The group with Canada region is collapsed and is showing a plus sign.

To expand the group, click the plus sign.

Or click: Data > Outline > Show Detail.

The group is expanded.

Collapse or Expand the Entire Outline 

If your dataset is very large, you can collapse or expand the entire outline at a time.

There are numbers above the expand/collapse option showing the group level.

• The first level is the regions group .
• The second level is the items group within a region.

Click 1 and all groups for regions are collapsed.

This is the output.

To expand the entire outline, click 3.

All the groups are expanded.

How to Remove an Outline and Ungroup Rows

• Select any cell in the dataset and click: Data > Outline > Ungroup > Clear Outline.

Excel removed the entire outline.

To ungroup it, select the rows in the group and click as follows: Data > Outline > Ungroup.

The rows are ungrouped.

Do it for each group individually.

Read More: How to Group and Ungroup Columns or Rows in Excel

Things to Remember

• Press the right shortcut key: Shift + ALT + Right Arrow Key.
• The subtotal command is applied to sorted data.
• The Auto Outline command will group all the rows above the subtotal row.

Download Practice Workbook

Download the free Excel template.

Related Articles

• How to Group Rows with Same Value in Excel
• How to Group Columns Next to Each Other in Excel
• Group Rows with Plus Sign on Top in Excel

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