Group Rows and Columns

Grouping and then collapsing data in Excel can improve readability. It is especially handy when you deal with lots of data.

Take a look at the following example:

It represents sales of our products. Let’s say that we don’t want to see sales for each month, only a summary for each quarter of a year.

Grouping rows

We will group cells for each quarter in order to improve our data visually and make it more concise.

In order to do this, click and drag row numbers, first, for rows 2 to 4.

Choose DATA >> Outline and press the Group button.

Do the same for the remaining rows (6-810-1214-16).

This will give us the following result.

TIP

Instead of clicking the group button in the ribbon each time you want to create a new group, you can use the Shift + Alt + Right Arrow keyboard shortcut.

Collapsing rows

If you want to collapse rows, you can do it both ways:

1. Individually (by clicking the “–” button).

2. All at once (by clicking the “1” button).

Expanding grouped rows

Similarly, you can expand grouped rows using one of the two methods:

1. Individually (by clicking the “+” button).

2. All at once (by clicking the “2” button).

Ungrouping rows

To ungroup rows, first select rows to ungroup, then choose DATA >> Outline >> Ungroup or use The Shift + Alt + Left Arrow keyboard shortcut.

Collapsing nested rows

You can group cells inside other cells. Look at this example.

First, select and group rows from 2 to 11. When you have the first group create two other groups (2-5 and 7-10).

After you created groups, you have three buttons.

“1” – will collapse all groups

“2” – will collapse subgroups

“3” – will expand all groups.

Collapse columns

You can groups and collapse columns the same way as you did with rows. Just select columns and use the group feature.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.