Remember those little phone books with colorful tabs on them for each letter of the alphabet? Those little tabs helped keep your contacts grouped together by alphabet. This kept your phone book organized and made it really easy to find your contact names.
As we know, keeping your worksheets organized is just as important, especially when they are large or complex. Just like the phone books, Google Sheets lets you organize your columns into groups that you can expand and contract.
In this way, besides keeping the worksheet organized, your worksheet is easier to look at, because you see a more condensed form of the data.
At the same time, it gives you the option to expand the column groups you need to see in more detail and then collapse them back once you’re done. This makes your worksheet easier to navigate through.
In this tutorial, I will show you how to group columns in Google Sheets, how to expand and collapse column groups, as well as how to ungroup a grouped set of columns.
We will also show you how you can group columns into bigger groups so you get layers of grouping in your worksheet. This can be great if you like to be super-organized.
How to Group Columns In Google Sheets
To demonstrate how to group columns in Google Sheets, we are going to use the following sample dataset. As you can see we have sales data displayed month-wise.
This dataset could be much more organized and easy to look through if we could group the months into quarters. So, let us see the steps we need to follow in order to do that.
Before starting to group the columns in this example, it would be a good idea to create divider columns for each quarter of the year. This will help you understand what each column group refers to when collapsed.
You can do this by right-clicking on the column before which you want to insert a new column and click on ‘Insert 1 left’.
This is an optional step and I am showing it here to make the process clearer. You don’t have to insert a column to make the column grouping possible.
Your sheet would now look like the image below:
Once your dataset is organized and ready, you can follow the steps below to group your columns:
- Select the columns you want in a single group. In our case, we want to group together columns C to E (for the months Jan, Feb and March). So we make sure to select the entire range of columns C:E by selecting their column headers.
- Right-click on your selection and select the option ‘Group columns C-E’ from the context menu that appears.
- This will group the columns C:E together, and you should see a minus icon (to collapse the row) at the column header to the left of the group.
- Repeat this for columns G:I, K:M, and O:Q
The minus icon that appears on top of each group is accompanied by a line that spans over the columns that are part of its group, so it is easy to see which columns come under the group.
Collapsing and Expanding Groups
Once your columns are grouped together, you can easily expand and collapse each group. When you click on the minus icon that appears on top of a column group, it hides the columns in its group and the icon itself toggles into a plus icon.
When you want to expand the columns again, simply click on the plus icon, and the grouped columns will once again become visible.
If your worksheet has multiple groups of columns, then it might become tiresome to expand or collapse all of them. So, to make it quicker, you can expand all groups in one go by right-clicking on any plus icon for any group and then clicking on ‘Expand all column groups’ from the context menu that appears.
Similarly, you can collapse all groups in one go by right-clicking on any minus icon for any group and then clicking on ‘Collapse all column groups’ from the context menu that appears.
How to Create Multiple Layers of Grouped Columns
In the above example, we have grouped the columns based on just one layer (we grouped months quarters). However, what if we want to further organize the quarters into years? For this, we can further add another layer of grouping, and here is how.
Once again, before starting to group the columns in this example, it would be a good idea to create divider columns for each year as shown in the image below.
Note: Our dataset only has data for one year, so grouping by year, in this case, would not serve much of a purpose. However, for the sake of simplicity and clarity of view, we have decided to go with just one year. This would, however, be more practically useful if we had data for more than one year.
Once your dataset is organized and ready, you can follow the steps below to add another layer of grouping to your columns, this time grouping the groups of quarters into a year:
- Expand all groups that you created before.
- Select all the columns (or groups of columns) that you want to include in the new group. In our example, we selected columns C to R.
- Right-click on any of the selected cells.
- From the context menu that appears, click on the ‘Group columns C-R’ option.
- This will group the columns C:R together, and you will now have two layers of grouping. You can visually see this on the gray stripe on top of all the column headers.
- Repeat this if you want to group other groups of columns too.
Notice the gray area has a new minus icon on top of the previous layer of minus/plus icons:
This icon is also accompanied by a line that spans over the columns that are part of its group, so it is easy to see which columns (or groups of columns) come under the group.
The first (or top-most) layer will be the larger group. When you collapse it, all the quarter’s columns will get hidden. The layers under this will be the sub-groups of columns.
You can further group and subgroup the columns as you need to and you can easily see how all your columns have been grouped in the grey area on top.
How to Ungroup Columns In Google Sheets
If you want to ungroup a grouped set of columns, then all you have to do is:
- Right-click on the plus /minus icon on the group that you want to ungroup.
- Select the ‘Remove group’ option from the context menu that appears.
If you want to ungroup multiple groups of columns (that are contiguous), then you can select the column headers of all the columns (in all your required groups). Then, follow steps 1 and 2.
Why Use Grouped Columns in Google Sheets?
Since there already is an option to hide/unhide columns in Google Sheets, you might think what’s the point of having a separate feature to group/ungroup sheets? Both have more or less similar results. So here’s how grouped columns in Google sheets can be useful:
- You can use it to keep your data organized and easy to read through.
- It lets you group columns at different levels so that you can hide/unhide groups and sub-groups as required.
- It lets you effectively expand and contract parts of your worksheet, so it looks neat.
In this tutorial, we showed you how to group columns in Google Sheets, how to expand and collapse them, and how to create multiple layers/ levels of grouping for an organized worksheet that is easy to navigate. We hope this was helpful to you.
Other Google Sheets tutorials you may also like: