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-8, 10-12, 14-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.