Organizing data not only makes it easy to find the specific information we need, but it also helps us make decisions by preventing information overload in different situations. However, organizing data is greatly dependent on the program we are working on, and hence, it is important to choose the right program. When it comes to organizing data, one important requirement is to group or ungroup the fields in spreadsheet programs, which are often used for data organization.
For example, if you have data from multiple branch offices under your supervision, you can group the records based on the office location in just one sheet and share the same. That way, the branch offices can expand the group comprising data regarding that specific office. Similarly, there are several situations when it can be useful to group records based on a specific criterion. That said, today I will talk about how you can group records on Google Sheets so that others can expand or collapse the groups easily to see the records based on their requirement.
Let’s get started with how you can group records based on a criterion, on Google Sheets.
Grouping continuous records on Google Sheets
Going by the above example, where the sales figures of different branches in an organization are recorded, we will have to group the records by branches.
So, the first group will have all the records from branch A, the second group from branch B, and so on.
To group the records, we have to select the rows containing the records. Hence right-click on the selection, hover over to ‘View more row actions’, and hence click on ‘Group rows 2 – 5’. Based on your selection, these numbers can be different too.
In the same way, group the other records too, and the results are as below.
Now, you can simply click on the ‘–’ to collapse the groups or click on ‘+’ to expand an existing group.
If you again want to create another group with the existing groups, you can do that, as well. Say, for example, we want to create a new group with the groups A and B, and another group with the subgroups C and D, you can do that too.
To do that, we again need to create a new row that will calculate the sum of the values in branches A and B, and C and D, as we have already done earlier, as in the screenshots above.
Now, select the rows containing data of branches A and B, hence hover over to ‘View more row actions’, and finally click on ‘Group rows 2 – 9’.
Repeat the same with the rows containing details of branches C and D.
Just like before, you can simply use the ‘+’ to expand the groups, or ‘–’ to collapse the groups.
If for any reason, you want to exclude any row from the group, right-click on the selected row, and hence click on ‘Ungroup row’, in ‘View more row actions’.
While it is easy to create groups in Google Sheets using the above method, you must leave at least one record free from any group between two subsequent groups. That is the reason, we have an independent row that calculates the total of the values in the above rows.
However, if you don’t have any numerical data, where the total cannot be calculated, you can simply keep one row vacant, or use it in a different way based on the data in the sheet.
Another thing that you should keep in mind is, that the rows that are supposed to be grouped must be present in a continuous pattern. So, if in the above example, we are creating groups based on the branches, and the data of each branch is haphazardly arranged in the sheet, it is not possible to create a group.
So, that’s how to create groups in Google Sheets based on a specific criterion. Do you have any further questions regarding the topic? Feel free to comment on the same below.