You can now Group Rows and Columns in Google Sheets as it’s now officially available. It’s one of the useful features Google Sheets was missing.
If you are familiar with Excel, you may be badly looking for this feature in Google Sheets. Now it’s officially available.
Google is rolling out this new update fo all Google Sheets users globally (13/04/2018).
What’s Grouping of Rows and Columns in Google Sheets
Grouping is not making a group-wise summary of data by using a Query or Pivot Table.
With the said function/command what we are doing is making a group-wise summary of data in a new range in the same sheet or in a new sheet.
It’s actually called the grouping of data not the grouping of Rows or Columns.
As a side note, you can group data in different ways. Here are a few links that explain the most seeking data grouping in Google Sheets.
- How to Group Data by Month and Year in Google Sheets.
- Group and Average Unique Column in Google Sheets.
- Group and Sum Time Duration Using Google Sheets Query.
- Grouping and Subtotal in Google Sheets and Excel.
- Drill Down Detail in Pivot Table in Google Sheets [Date Grouping].
Now let’s back to the grouping of rows and columns in Google Sheets.
Grouping of rows or columns in Google Sheets means grouping by outlining similar rows or columns.
It’s not necessary that the data in such rows must be similar. It’s up to you.
In single words, the grouping is outlining the rows or columns with a “+” or “-” symbol to expand or hide/collapse the rows grouped.
How to Group Rows and Columns in Google Sheets
First, let’s see how to group rows in Google Sheets. Then we can move to the column section. Actually, both the steps are almost the same.
How to Group Rows in Google Sheets
Steps:
- Select the rows that you want to group.
- Right-click and select “Group Rows”.
This way you can group rows in Google Sheets. To ungroup rows, just follow the same steps above.
Example to Grouping of Rows:
How to Group Columns in Google Sheets
Steps:
You can just follow the above same steps to group columns in Google Sheets. That means;
- First, select the Columns that you want to group.
- Then right-click and select “Group Columns”.
The same steps are applicable to the ungrouping of columns.
Example to the Practical Use of Grouping of Rows
See one real-life example to row grouping.
In the above sample data, the following rows are grouped together.
- Row # 3 and 4. Please note that I have excluded row # 2 as I want that row to visible when collapsing the grouping.
- Row # 7 to 9. Excluded row # 6.
- and Row # 12 to 14. Excluded row 11.
Note: If you group a group of items, don’t include the first row in each group in the row grouping.
The collapsed view of all these three groups of rows will be as follows.
Parent and Sub-Grouping of Rows in Google Sheets
Assume you have grand total row at the bottom of the above data and want to just see that total by collapsing a group.
In that case, you can think about creating a parent group for the above three sub-groups.
How to create a parent group as above in Google Sheets using Row Grouping?
Steps:
We have already created three subgroups, right? Right-click on the collapse/expand button of any of the row groups.
It will popup a short cut menu. Choose “Move +/- button to the bottom” (it’s not a necessary step though).
It will move all the +/- signs to the bottom row of each group. Then select the rows A2:A15. Right-click and select ‘Group rows 2-15″.
That’s all about how to group rows and columns in Google Sheets. Enjoy!
If there are protected cells in the column, those without access to edit protected cells cannot expand/collapse grouped rows, am I missing something?
If I have a chart sitting on top of a portion of my grouped rows, is there any way to have that chart disappear when I close that group of rows?
Hi, Mike,
I think that’s not possible.
I am having trouble creating multiple groups. When trying to create 2 separate groups, it just creates one large group.
Hi, Schuyler Bell,
Leave a row between groups.
Once groups are set, it doesn’t appear that you can collapse all and expand all like you can in excel. Is that right?
Hi, Kevin,
You can do that but not like in Excel. Right-click anywhere on the left side of the row numbers and from the shortcut menu that appears choose either of the below options.
1. Expand all row groups.
2. Collapse all row groups.
3. Move +/- button to the bottom.
to alter the position of the (+) sign, right click on the plus sign
I’ve given users comment/view access only. Why can’t they un-group rows to view the full sheet? There is a lot of data and it’s easier to navigate using the grouping feature.
Could you tell how to alter the position of (+) sign – below or above the group of rows, or left or right the group of columns? Grateful in advance
Hi Olegh Bond,
Row grouping is simply hiding/unhiding a group of rows as per your choice.
The only benefit is you can collapse (hide) and expand (unhide) the grouped rows with one click.
Here is the logic.
Here to get the below grouping, you should select the rows 3 and 4 first and group it. Then select the rows 6 and 7 and group it.
Hope this helps.
You can change if the +/- icon should appear above or below by right clicking the icon and choosing the option to move it. The result is mostly only visual, and the grouping will still behave the same.