Today, we are going to learn how to group columns and rows. In our example, we have month by month sales, and their summaries by the first quarter, the second quarter and half a year with the use of the SUBTOTAL function.
Sometimes, we don’t want to show lower values, i.e. values from individual months. We can do it by hiding those columns (Fig. 2).
And sometimes, we want to show those values (Fig. 3).
Those tasks, however, can sometimes be a bit tedious. That’s why we are going learn how to group those columns to make it simpler. First, you have to select the columns you want, then go to the Data tab and click on the Group command. Now, we can see a line above column names, which allows us to hide the columns just by clicking on the minus sign (Fig. 4).
We can also unhide them by clicking on the plus sign (Fig. 5).
Now, the task is quite simple. You can hide or unhide many columns at once. We can group even more columns this way, however, it’s important to have a space of at least one column between those groups so that Excel doesn’t combine those groups in one (Fig. 6).
When we want to ungroup our data, there is a simple solution. You have to go to the Data tab, expand the Ungroup command, and choose the Clear Outline option (Fig. 7). This way, Excel will clear all groups in the sheet. Usually, it’s easier to clear all groups and start creating new groups from the beginning than trying to fix it.
Due to the fact that our values are combined from different columns, we can make Excel group the values for us. You just select any cell, go to the Data tab, expand the Group command and choose the Auto Outline option (Fig. 8).
This way, Excel has created three groups for us. Those groups aren’t on the same level because each lower level group combines three columns, and the upper level group combines all the columns from the lower groups and a few more (Fig. 9).
When we click on the group level number, Excel will expand or collapse all groups in this level (Fig. 10).
Now, let’s group some rows. We don’t have any function that sums up or combines rows, so we have to do it manually. We have to select the rows we want, then go to the Data tab, then the Group option (Fig. 11).
If we select a range, not whole columns or rows and press the Group command, Excel doesn’t know which ones we want to group. Then we have to select the proper radio button (Fig. 12).
We can also select more rows and group them by pressing the Group option to create a group on a higher level. This group will consist of groups from a lower level. We can expand and collapse them the using also level number buttons (Fig. 13).
