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 easy to find your contact names. You can group rows in Google Sheets for a similar effect. The same applies for columns.
In this way, besides keeping the worksheet organized, your worksheet is easier to look through because you see a more condensed form of the data.
At the same time, it allows you 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.
In this tutorial, I will show you how to group rows and columns in Google Sheets, expand and collapse groups, and ungroup a grouped set of columns or rows.
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.
Read on to learn more.
This Article Covers:
In Short: How to Group Rows and Columns In Google Sheets
While you cannot group rows and columns at the same time, you can do them individually with the same method, which is:
- Highlight the columns or rows you wish to group
- Navigate to View > Group and click on Group
- Select whether you want to group the rows or columns from the pop-up menu
Using a Keyboard Shortcut to Group Rows and Columns in Google Sheets
To group rows or columns with a keyboard shortcut, you can follow these steps:
- Select the range you want to group
- Press Alt + Shift + -> (right arrow key)
- Choose how you want to group in the small pop-up menu
This method works the same on all alternate browsers including Safari and Chrome.
Extended Guide: How to Group in Google Sheets
This guide primarily covers grouping columns, but the method of grouping rows is the same as grouping columns in Google sheets!
To demonstrate how to group in Google Sheets, we are going to use the following sample dataset. As you can see, we have sales data displayed by month.
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 learning how to group columns in Google sheets, 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.
Remember you can apply the same steps when grouping rows in Google sheets too.
Collapsing and Expanding Groups
Once your columns are grouped together, Google sheets can collapse rows and columns with ease. Here’s how to collapse columns in Google sheets:
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 or Rows
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 and Rows 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.
Frequently Asked Questions
Can a Google Spreadsheet Collapse Rows? / Can Google Sheets Group Columns?
Yes, to perform Google Sheets grouping, simply highlight the range you want to collapse and head to View > Group.
Why Use Grouped Rows and Columns in Google Sheets?
Since there already is an option to hide or 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 grouping in Google sheets can be useful:
- You can use it to keep your data organized and easy to read through.
- When you group cells in Google sheets at different levels you can hide/unhide groups and sub-groups as required. Even after you accidentally close and have to reopen a tab.
- It lets you effectively expand and contract parts of your worksheet, so it looks neat.
Wrapping up How to Group Rows in Google Sheets
In this tutorial, we showed you how to group rows in Google Sheets as well as rows. We also covered how to expand and collapse them and create multiple layers/ levels of grouping for an organized worksheet that is easy to navigate. We hope this was helpful to you. Let us know in the comments if you have any questions.
Read More:
