In the last few years, Google Sheets added the functionality to group rows similar to the one available in Excel.
This gives the user an added option to show only the summary or aggregated numbers like the ones you have in an Excel pivot. It helps make your sheet concise and clutter-free, with focus only on the relevant figures.
The hidden rows can, of course, be shown again should the user desire to. In this article, we will look at how to group rows in Google Sheets.
Let us look at an example.
A Real Example of Grouping Rows in Google Sheets
Consider the example below where you have monthly revenue and margins for a retail store selling groceries:
The numbers are aggregated at the end of every quarter for a summary view, and the retailer’s senior management is interested in only the quarterly numbers. Therefore, the analyst working on sales reporting will need to show only relevant numbers in the quarterly review meeting. This is where grouping rows will come to his aid.
Once he has grouped the rows, only the relevant quarterly numbers will be shown, with an option to drill down to the detailed figures. If you notice, the ‘+‘ symbol beside the row number gives an option to view the rows that were grouped. Try grouping rows yourself to get clean and concise summary sheets. You may copy the spreadsheet using the link below:
Great! Let’s look in detail at how to group rows in Google Sheets.
How to Group Rows in Google Sheets
- For the last eight months this year, I have tracked the total calories I consumed daily along with my weight. I want to pause and learn from the data collected so far, to try and emulate the routine in the months that have given me the best results. But since I have about 240 days’ worth of data, it is not easy to scroll through every time. The objective, therefore, is to summarize the data concisely so that I can pick and choose which months to look at in detail.
- First, I will add a row after every month, aggregating the numbers for a summary view. In the above screenshot, e.g., I will insert a new row below row 32, adding up the calories for January.
- Similarly, add a row after every month summarizing the month’s numbers.
- Now, select all the rows for days in January, in this case, rows 2 to 32, right-click on the row number, and select the option ’Group Rows 2-32’.
- Once you’re done, you will notice that a ‘–‘ symbol has appeared adjacent to row number 2. Go ahead and click on it. If you’ve done it correctly, you should have the following output.
- Notice that all the January rows have been grouped, and only the summary row remains. You may click the ‘+‘ if you wish to expand the rows again to view the daily numbers. Proceed to group the rows for other months in a similar fashion.
- What you should have at the end of the exercise is a clean and concise summary as given below:
A Faster Way to Group Rows in Google Sheets
If the entire process of selecting the rows, right-clicking to get the options, and clicking on the desired option sounds too much work to you, there is a faster way of doing the same operation. Select the rows in your data that you want to be grouped. You don’t need to select the row number, in this case. For Windows users, press the Alt and Shift keys together and press the right Arrow key. An option will pop on your screen to group the selected rows, like below:
For Mac users, the key combination for this is Option + Shift + Right Arrow. The keyboard shortcuts are particularly useful when you are working with a larger set of data, and you need to perform multiple groupings.
Multiple Layers to Grouping Rows in Google Sheets
Google Sheets further provides you with the option to create more than one layer of groupings. For example, If I continue tracking my calories for the next year as well, I can further group this data at a year’s level. The outer layer of grouping will be a year level, and one level inside it will be a monthly grouping. Let me further group the eight months we have for illustration purposes. Select the eight rows, right-click, and group, as we did earlier. You will notice that the second layer of grouping appears adjacent to the ‘+‘ we already have for every month:
This outer layer denotes the group that has one row for each month. The ‘+‘ signs on the inside denote the groups for every month (with daily data). This kind of grouping is very convenient when working with time series data where every month, quarter, and year may be grouped into a clean and concise summary.
That is pretty much all you need to know to get started with grouping rows in Google Sheets. Take a shot at it, and try it alongside the numerous Google Sheets formulas available, to better summarize your data.