Google Sheets and its enhanced user experience are without doubt world-class.

The most commonly used features in Excel can also be found in Google Sheets. However, the grouping feature was initially unavailable, and it was only recently included in 2018.

Often you come up with a situation where a dataset is too large and becomes hard to analyze with meaningful insights.

This is where the need to group the data comes in.

This feature in Google sheets allows you to hide rows you feel are not needed in the current view with one single click. At the same time, just ungroup the cells, and you are through if you want to show the complete data!

This sounds like an incredible tool to have in your arsenal, isn’t it?

I will show you various methods of grouping rows and columns inside Google Sheets throughout this article. Get the example workbook to follow along.

Hold your breath because we’re going to dive deep into the ocean of grouping! 😉

Group Rows in Google Sheets

Consider you have data with a monthly sold quantity. You also have a quarterly summary at the end of each quarter.

This looks a little bit shabby. Especially with that quarterly sum being added at the end of every quarter. The data looks as shown above.

Suppose you want to group this monthly data into quarters.

It is easy to get this grouping. Follow the steps below to achieve this.

Select all the rows that you want to group. In this case, those would be the second, third, and fourth rows. You want to group these rows inside the first quarter.

You can select the rows by clicking on row numbers from the left side of the sheet.

Note: It is impossible to group rows by selecting the cells across columns A and B. You need to select the entire rows.

Once you have selected the rows, use the right mouse click to access the options available for this selection.

From the available options, click on Group rows 2 – 4. This option will dynamically change based on what rows are selected.

Congratulations! You have successfully grouped rows 2 to 4.

You will see a gray box appearing beside the row numbers to the extreme left. That box also has a vertical line spanning across the rows that were selected to group. It is evidence that grouping was successful.

If you click on this box, it will hide the rows grouped rows.

Also, the minus sign inside the box will also be turned into a plus sign. It means that the box contains some additional grouped rows.

Grouping your rows is a better alternative to hiding the rows to temporarily remove them from view as the grouped rows are easier to bring back into view!

Now, this is one grouped block of rows. Follow the exact steps above if you want to group the rows for the second, third, and fourth quarters. It will then look like the one shown above.

Note: You can only group the rows that are adjacent or neighboring. You can’t just select any three or four rows and group them.

If you try to, the Group rows option will not even appear. Above you can see the seventh, eleventh, and fourteenth rows are selected, but no grouping option appears in the right click menu.

Tip: There is also another way of grouping the rows through the Data menu from the ribbon.

With rows selected, click on the Data tab from the menu, and then you can see a series of options associated with these rows. Select the Group rows 2 – 4 option.

This method does the same grouping task.

Keyboard Shortcut to Group Rows or Columns in Google Sheets

Keyboard shortcuts can be a faster way to work. The good news is there is a shortcut to group rows inside Google Sheets.

To group rows with a shortcut, follow the steps below.

Select the rows you want to group. In this case, we will select the second, third, and fourth rows.

With rows selected, hold the Alt and Shift keys on the keyboard and then hit the Right arrow key to group the rows.

As soon as you hit this shortcut, you will see the gray box on the vertical pane beside the row numbers in Google Sheets.

The appearance of that gray box means the grouping has happened. You can then collapse it by clicking on the box, and rows grouped will be hidden.

Group Columns in Google Sheets

Similar to grouping rows, you can also group the columns inside Google Sheets.

Consider a situation like above where you have quantity sold for India and the USA for each month of the year.

This time around, you want to group the region columns. Meaning, you want to group column B and C together.

You can achieve this inside Google Sheets the same way you did it for rows.

Select the columns which you want to group. Here, you want to group B and C, so click and select them together. Select an entire column by clicking on the column heading.

Click the heading to select the whole column B, and then while holding the Ctrl key, click on the heading for column C.

Note: You can also use the Shift and Right keyboard key combination for multiple column selection.

Now, with the columns selected, use the right mouse click to access the list of options associated with thos columns. Click on Group columns B – C to group the columns.

That’s it! You have successfully grouped columns B and C together.

You will now see a gray-colored small square box with a minus sign in it. This box also has a horizontal line spanning up to column C.

It is a checkpoint for you. If you see something like this in your sheet as well, it means you have successfully grouped the columns.

Similar to the grouped rows, if you click on that gray box with a minus sign, you will see that columns B and C are now hidden from the layout.

The box also contains a plus sign, evidence that there are additional grouped columns inside this layout currently hidden from view. You can click on that plus sign to collapse and expand the groupings.

Note: You can use the same keyboard shortcut to group the columns as well. Select the all the columns you want to group. Holding the Alt and Shift keys, press the Right Arrow key on your keyboard to group the columns.

Create Subgroups in Google Sheets

Until now, we have created a single group inside the Google Sheets. Be it with rows or columns. However, we can extend this concept up a notch and create multiple groups across rows or columns.

Those various groups will be called subgroups inside the leading group.

To explain this concept, consider a situation where you have monthly sold quantities for 2019 and 2020.

You have created quarterly groups initially, as in the previous examples. Now, you want to make two groups based on each year. Inside those two groups, you again want to create quarterly subgroups.

The data for this example is as shown above.

First, create the groups for each year.

Select all the rows from second to seventeenth and right-click to group these rows. This is the first group for the year 2019.

As you can see above you can now collapse and expand the entire data for 2019 under the first group.

Now, you need to create the group for 2020.

Select the entire rows from nineteen to thirty-four and right-click to group them.

This is the group for the year 2020. You can then expand and collapse the data as seen above.

Inside these two groups you created, you have to make the quarterly subgroups using the same method.

It might look painful to right-click and select the Group rows option, which is when the keyboard shortcut could be handy.

Use the Alt + Shift + Right Arrow Key to create quarterly subgroups for each year.

Discussed throughout this section is a way of creating subgroups inside rows, but you can replicate this same logic and create subgroups across columns as well.

Note: It might be painful to use the right mouse click every time to group the rows or columns. Make a habit of using the keyboard shortcut Alt + Shift + Right Arrow Key instead.

Tip: It is also a challenge to expand all the collapsed groups if you use the subgroups. It will cause you time as well to expand those manually.

However, you have a smart option. Right-Click on any collapsed group plus icons, and then click on the Expand row group option. It will expand all the rows at once!

Remove Groups in Google Sheets

If you are grouping rows and columns, you will also need to know how to remove groupings inside Google Sheets.

By removing groups, you can bring the data back to its original state. Follow the steps below to remove groupings from your Google Sheets data.

To remove the groupings from data inside Google Sheets, navigate the expand and collapse icon for the grouping. Right-Click on it, and you will see a series of options. Select the Remove group option.

It will remove the grouping.

This is one way of doing things. There are two other ways of doing it, though.

First, select the cells you want to ungroup.

Then right-click on those and select the Ungroup rows 2 – 4 option. It will ungroup the rows 2 to 4.

The third way of ungrouping cells is through the Data menu from the ribbon.

Select the cells you want to remove grouping from.

Then, Click on the Data menu from the top ribbon then select Ungroup from the options. This will ungroup the selected rows.

Tip: You can also use the keyboard shortcut Alt + Shift + Left Arrow Key to ungroup the rows.

Group Data with the QUERY Function

Talking about Google Sheets and the QUERY Function doesn’t appear? It seems to be an impossible event!

Almost anything can be done with this function.

It’s no surprise it can do grouping.

You can group the data based on specific columns inside the QUERY function. You can use the Group By Clause in combination with some aggregators to group the data.

Suppose you have quantity shipped data by country and date, as shown in the screenshot above.

You want to group this data based on the Country column. You can use the QUERY Function to achieve this result.

The GROUP BY clause inside the QUERY Functions only works after aggregate functions such as SUM, MIN, AVG, etc. So you will get the sum of quantity by its country like above.

= QUERY ( A1:C25, "SELECT B, SUM(C) GROUP BY B" )

The formula to achieve this task is as shown above.

This formula first sums the data up by the Quantity column. Then it groups based on the countries and finally selects the Country column inside the output.

Note: It doesn’t look similar to the grouping rows and columns we discussed previously. It doesn’t allow us to expand and collapse the data. Instead, it gives us the grouped summary output based on other rows. This way, we are always keeping our data in its original form.

Group Data with a Pivot Table

Pivot tables are such an important tool for summarizing data in Google Sheets.

They can even be used to group the data based on specific columns, allowing the data to be expanded and collapsed similar to grouping rows and columns.

Most frequently, this option is used to group the data based on date values. You can do the grouping on a yearly, monthly, weekly, or even daily basis.

However, it is not only restricted to dates, and you can also group the pivot table based on other data types.

Consider you have data as shown in the screenshot above. It consists of three columns, namely Date, Shipping Country, and Shipping Quantity.

You want to create a pivot table out of this data and then also want to group it based on the days from the Date column.

The biggest challenge here is that the dates you are working with should be in proper date format.

In this case, they are; however, if you ever come up with a situation where your dates are not in a format recognized by sheets, follow the steps below.

Please select all the cells with date values in them from the given column.

1. With all cells selected, click on the Format menu. It will open up a range of formatting options.
2. Select the Number option.
3. Select Date to convert values into date format.

Now, to the part where we create the pivot

To create a pivot table, click on the Data tab from the horizontal ribbon. Then click on the Pivot table option.

The Create pivot table menu will pop up, as shown above.

You can see the Data range is being already set up for you. However, if it is not the one you want to create a pivot for, feel free to click on the square window-like icon on the right to select the data range.

You have two options for Insert to.

• You can insert the pivot into a New sheet.
• You can use an Existing sheet range to create a pivot table.

I will choose the Existing sheet option. With that selected, select the cell in the existing sheet where you want to add a pivot table.

I have picked cell G1 from the current sheet to add a pivot.

Now, press the Create button to create a pivot table.

The pivot table layout looks like the one in the screenshot above. It also has the Pivot table editor opened up by default.

You can add Rows, Columns, Values, Filters from the available data fields from the right-hand side.

Now, I am creating a layout with the Date filed in the Rows area and Sum of Quantity in the Values area. The structure should look like the one above.

Now, to group data inside this pivot table, right-click on any cell from the Date column and choose the Create pivot date group option. This has several ways to group the pivot table based on the Date.

Click on anything of your choice. For me, I will choose the Day of the week option to see the day-wise grouping of the pivot table.

The day-wise grouped pivot table will look like the one above. You can clearly see the change. The dates are now gone, and weekdays are there instead.

Note: This is not the only way you can group data inside pivot. You can group data based on other data types as well. However, you will not get the variety of options such as from a date field.

Group with Apps Scripts

Another thing that comes up every time we work with Google Sheets is Apps Scripts.

It allows you to automate tasks associated in the Google Workspace through a cloud-based JavaScript platform that works closely with Google Sheets, Docs, Forms, Gmail, etc.

If you are new to Apps Scripts, feel free to go through our full guide to Apps Scripts that will turn you into a scripting pro.

There are different grouping methods inside the Apps Script. You can collapse the rows; you can expand those as well.

Group and Collapse

Open the Tools menu from the ribbon and click on the Script editor option to open the script environment.

Use the following script to group and collapse the rows.

function groupRows() {
  var sht = SpreadsheetApp.getActiveSheet()
  var rng = sht.getRange(2, 1, 3, 2)
  rng.shiftRowGroupDepth(1)
  var grp = sht.getRowGroup(2, 1)
  grp.collapse()
}

Explanation

• You first create a function that can hold our code. Since you want to group the rows, you have named the function as groupRows(). Every function is defined with the keyword function.
• You then use the SpreadsheetApp.getActiveSheet() method to access the currently active sheet. The results are stored inside the variable named sht.
• The following line of code accesses the three-dimensional range using the sht.getRange() method. The numbers used as argument are different, meaning:
  1. The first number specifies the starting row index. The range starts from the second row.
  2. The second number specifies the starting column index. It means the range starts from the first column.
  3. The third argument tells you how many rows from starting point to group. The value mentioned for this argument shows that the range will be going up to three rows.
  4. The fourth and last argument specifies the ending column index. 2 means the second column.

The getRange() method used above will return a range of three rows spanning across two columns.

• Since we are grouping the items, we have to specify the depth. We are using the shiftRowGroupDepth() method, which is being called on the rng variable.
• To group the rows, we are using the getRowGroup() method called on the sht variable. It allows the system to decide which row the grouping needs to be done and at what depth.

When you hit the Run button to run this script, you will immediately see the Execution log being generated.

Since the execution has been successfully completed, the code is glitch-free, and you can navigate to the sheet to see the groupings.

The second, third, and fourth rows are now hidden, and you can see a box with a plus sign beside them. It means that the rows are grouped and collapsed.

Group and Expand

On similar lines, you also have a script that can expand the groupings. Here is the code below.

function expandRows() {
  var sht = SpreadsheetApp.getActiveSheet();
  var rng = sht.getRange(2, 1, 3, 2);
  rng.shiftRowGroupDepth(1);
  var grp = sht.getRowGroup(2, 1);
  grp.expand();
}

It is almost the same as the previous code. The difference is inside the last line, where you call the expand() method rather than the collapse method to expand the grouping and show the rows.

When you Run the above script, you can see above, the collapsed grouped rows are now expanded and can be seen in the table layout.

Note: You should note that Collapse and Expand are two different methods, and if you use them one after another, there will be two groupings shown. They are independent. Even if you use the Expand method, you are still creating a grouping.

Conclusions

Grouping allows you to control what data you are showing inside your Google Sheets.

It will enable you to group data on different levels, and each level can be kept collapsed to show a limited and more compact view to users.

You can group both rows and columns to keep information from a user’s view.

Grouping can also be performed when summarizing your data with the QUERY function or when using pivot tables.

I hope this article brings you some clarity about how you can group your data in Google Sheets. Let me know if the comments if you have discovered any other methods for grouping your data!