See all How-To Articles

How to Group Cells (Rows / Columns) in Excel & Google Sheets

This tutorial will demonstrate how to Group Cells (Rows / Columns) in Excel & Google Sheets.

Grouping intro

Grouping or Outlinining data in Excel allows us to hide or show rows or columns depending on how much detail we wish to see on the screen.   Different levels can be displayed according to your needs.  Excel allows up to 8 levels of grouping. To use the group function in Excel, the data really needs to be organised in your worksheet in a specific way that works with the grouping functionality.

Manually Grouping or Ungrouping Rows

To group a number of rows together, first, highlight the rows you wish to group.

Grouping selectrows

In the Ribbon, select Data > Outline > Group >Group.

Group Ribbon

A small minus sign will be added into the outline bar on the left of the screen.   Clicking on the minus sign allows us to collapse the group and the minus siign will then change to a plus sign indicating that there is hidden data within the outline.

Group Row Group

We can repeat manual grouping to add more groups to the worksheet as required.

Group Multiple Row Groups

As well as using the plus and minus outline symbols, we  can also use the 1 or 2 in the top left hand corner of the screen to expand or collapse all the groups.

Group Row 1or2

Clicking on the 1 will collapse all the groups while clicking on 2 will expand all the groups.

Manually Grouping or Ungrouping Columns

To group a number of columns together, first, highlight the columns you wish to group.  This can be done if you have rows already grouped or not.

Grouping Select Columns

 

In the Ribbon, select Data > Outline > Group >Group to group the columns together.  Repeat this until you have created all the groups that you require.

Grouping Group columns

Once we have grouped our rows and / or columns, we can add a new level but grouping once again.  This will add a third level of grouping to the outline symbols in the top left hand corner of the screen.

Grouping Group 3 level

Once again, we can repeat this across our worksheet, and add up to 8 levels of grouping.

Grouping grouping all levels

Ungrouping Rows or Columns

Highlight the group you wish to ungroup and then, in the Ribbon, select Data > Outline > Ungroup > Ungroup.

Grouping Ungroup

This will only remove the one group.  To remove all the grouping in the worksheet, click anywhere in the data and then, in the Ribbon, select Data > Outline > Ungroup > Clear Outline.

Grouping ClearOutline

Auto-Outline

We  can use the Auto Outline function of Excel as long as our data is logically organised so that Excel recognises the groups within the rows and columns.  There must not be any blank rows in the data and the data must be summed or subtotalled at each level.

Click anywhere in the data where you wish the outline to be created and then, in the Ribbon, select Data > Outline > Group > Auto Outline.

Grouping AutoOutline

Excel will create as many grouping levels as the logical layout of the data allows.

Grouping AutoOutline Groups

 

How to Group Cells in Google Sheets

Google Sheets only allows us to manually group or ungroups rows and columns of data.

Select the rows you wish to group and then, in the Menu, select View > Group > Group rows (the number of rows selected will be shown).

Grouping GS Group

Similarly, select the columns you wish to group and then in the Menu, select View > Group > Group columns (the number of columns selected will be shown).

Grouping GS Group Columns

To ungroup rows or columns, click in the group you wish to ungroup and then in the Menu, select View > Group > Ungroup (rows or columns).

Grouping GS UnGroup Columns