Expand / Collapse Rows or Columns in Excel & Google Sheets

In this tutorial, you will learn how to expand and collapse rows or columns by grouping them in Excel and Google Sheets.

 

excel expand collapse rows final data

 

Excel allows us to group and ungroup data, which enables us to expand or collapse rows and columns to better organize our spreadsheets. This is possible by grouping data manually or using the Auto Outline option.

Group and Ungroup Rows Manually

If we want to group rows in Excel, we need to have data organized in a way that’s compatible with Excel’s grouping functionality. This means that we need several levels of information sorted correctly and subtotals for each level of information that we want to group. Also, data must not have any blank rows or spaces.

In the following example, we have total sales per month by product. Therefore, data are sorted by month and product and we have subtotals for each month, as we want to group data by month.

 

group ungorup excel initial data 1

 

To group data for Jan-21 and Feb-21:

1. (1) Select data in the column that we want to group. In our case that is Jan-21, so we’ll select C2:C8. Then, in the Ribbon, (2) go to the Data tab, and in the Outline section, (3) click on the Group icon. (Note that you could also use a keyboard shortcut instead: ALT + SHIFT + right arrow).

 

group rows excel 1

 

3. In the new window, leave Rows selected since we want to group rows and click OK.

 

group rows excel 2a

 

As a result, we get the outline bar on the left side with Jan-21 grouped.

4. If we want to collapse this group of data, we just need to click on the minus sign in the outline bar.

 

excel rows group 1

 

As shown in the picture below, all rows with Jan-21 in Column C are collapsed now and only the subtotal for this period remains visible.

 

excel rows ungroup 1

 

5. As in Step 4, we can expand the group (displaying rows) again, by clicking the plus sign. Following the exact same steps, we can also group data for Feb-21.

Group and Ungroup Multiple Levels of Data

Say we want to add another level of data grouping: Product. First, add a subtotal for all products.

 

group multi levels of data 1

 

Currently, we have two groups for Month level and subtotals for months and products. Now, we want to add groups for products. We can do this in the exact same way as we did for months. In this case, we’ll add six groups of data, selecting – separately – Keyboard (D2:D4), Mouse (D6:D7), etc. As a result, our data and outline bars look like the picture below.

 

group multi levels excel 1

 

We now have two outline bars and the second one represents groups of products. Therefore, we can collapse all product groups, and have data organized in a way that displays only subtotals per month per product.

 

multi level data collapsed 1

 

Group and Ungroup Rows Using Auto Outline

Instead of creating groups manually, we can also let Excel auto outline our data. This means that, if we have well-structured data, Excel will recognize groups and group data automatically.

To let Excel outline the data automatically, (1) click anywhere in the data, then in the Ribbon, (2) go to the Data tab, click on the arrow below the Group icon, and (3) choose Auto Outline.

 

auto outline data excel 1

 

We get almost the same outline bars as in the manual example because Excel can recognize data groups. The only difference is that the Auto Outline option creates one group more for Grand Total, which can collapse all the data except the Total.

 

auto outline data excel 2a

 

To remove outline bars created by Auto Outline, (1) click anywhere in the data then in the Ribbon, (2) go to the Data tab, click on the arrow below the Ungroup icon, and choose (3) Clear Outline.

 

excel clear auto outline1

 

This will remove all outline bars and ungroup all data.

 

group ungorup excel initial data 1

 

Expand and Collapse Entire Outline

Say we want to collapse the entire outline (for example, Month). In the outline bar, at the top, click on the outline bar number we want to collapse (in our case, outline level 2).

 

collapse entire outline 1

 

As a result, all rows with Jan-21 and Feb-21 are collapsed and only the totals are displayed.

If we want to expand the entire outline again, (1) click on Grand Total, then in the Ribbon, (2) go to the Data tab, and in the Outline section, (3) click on Show Detail.

 

expand entire outline 1

 

Now all data is visible again, and the Month outline is expanded.

 

auto outline data excel 2a

 

Group and Ungroup Columns Manually

Similarly, we can also group columns in Excel. Say we want to display only SKU and the corresponding Total Sales.

1. Select all column headings that we want to group (in our case C1:F1).

 

group columns excel 1a

 

2. In the Ribbon, go to the Data tab, and in the Outline section, choose Group (or use the keyboard shortcut ALT + SHIFT + right arrow).

 

group columns excel 2

 

2. In the pop-up screen, (1) select Columns and (2) click OK.

 

group columns excel

 

As a result, we will get a new outline bar, but this time for the columns.

3. To collapse the group of columns, click on the minus sign at the end of the outline bar.

 

collapse columns groups 1

 

As a result, Columns C:F are collapsed.

 

collapse columns groups 2

 

Group and Ungroup Rows in Google Sheets

In Google Sheets, we can only group rows manually, so let’s use the same example and see how to group data into the same categories. To group by month:

1. (1) Select all rows with Jan-21, then in the menu, (2) go to Data, and click on (3) Group.

 

google sheets group rows 1

 

2. In the new window beside the selection, click on Group rows 2 – 11.

 

google sheets group rows 2a

 

Jan-21 (Rows 2–11) are now grouped, and we can see the outline bar on the left side. The difference compared to Excel, is that the minus/plus sign for collapse/expand is a the top of each group.

3. To collapse Jan-21, click the minus sign at the top of the outline bar for months.

 

google sheets collapse rows 1

 

Now, data for the month are collapsed, and we can see only the Jan-21 Total row.

4. We now get the plus sign, so we can expand the group again.

 

google sheets expand rows 1

 

5. Following these steps, we can also group Feb-21 and create a new outline for the Product data level. When we’re done, the data and outline bars should look like this:

 

google sheets outline bars 1

 

Ungrouping data in Google Sheets works just like grouping.

1. (1) Select the data we want to ungroup (Keyboard in Jan-21– cells D2:D4), then in the menu, (2) go to Data, and (3) click on Ungroup.

 

google sheets ungroup rows 1

 

2. In the new window beside the selection, click on Ungroup rows 2 – 4.

 

google sheets ungroup rows 2a

 

Those three rows are now ungrouped and removed from the outline bar.

 

google sheets ungroup rows 3a

 

Group and Ungroup Columns in Google Sheets

Grouping columns can be done in a similar way to grouping rows.

Select Columns C:F, then in the menu, go to Data, and click on Group columns C – F.

 

google sheets group columns 1

 

Now, we get the outline bar for column grouping.

 

google sheets columns outline bar 1

 

To ungroup columns, select Columns C:F, go to Data, and click on Ungroup columns C – F.