Grouping allows you to create groups of rows that share common data in one or multiple columns.
Along with sorting and filtering rows, grouping is one of the ways you can sort and organize rows in your workbook. Grouping allows you to create groups of rows that share common data in one or multiple columns. Each group contains a summary row that can display information about the group’s data like sums, averages, counts, minimum and maximum values, and more.
You might use grouping in a Simple CRM workbook to group accounts by region or quarter, or you might use it in an Employee Directory workbook to group employees by department and location. In the example below, we’ll use grouping in a Team Task List to group tasks by their assignee and associated project.
This article features Spreadsheet.com's Team Task List template. Explore the Template Gallery to find ready-to-use templates in over 20 different categories, or learn more about starting a new workbook from a Spreadsheet.com template.
How to Group Rows
Grouping by One Column
To create a new group, click the Group button in the Views toolbar below your workbook title. This will open the Groups dialog, from where you can select a column to group your rows by. Select the column from the dropdown, and then select whether to order the new groups in ascending or descending order.
When creating groups in a sheet with row hierarchies, only the parent rows will be grouped according to the grouping rule. All child rows will be placed in the same group as their parent row.
In the example above, we’re grouping our rows by the “Assigned to” column in descending order. Once you’ve configured your grouping rule, click the blue Apply button in the lower right corner and your sheet will update to reflect it.
As you can see in the image above, our workbook is now grouped by the user each task is assigned to.
Once you've created row groups, you can add new rows directly in a group by clicking the New row button in the bottom left corner of the group or subgroup.
When you create a new row in a group, it will automatically inherit the grouping value from that group. In the example above, our new row in the "Beth Bigidea" group is created with "Beth Bigidea" already inserted in the grouping column.
Groups can only be created in Sheet and Gantt views, and, like Sorting and Filtering, will only be applied to the specific views they're created in.
Grouping by Multiple Columns
By adding additional grouping rules, you can create subgroups within each of your main groups. From the Groups dialog, click the + New group button in the lower left corner to add an additional grouping rule. Define the rule and click the blue Apply button in the lower right corner of the dialog.
The first rule listed will be used to establish the primary groups, and any subsequent rules will be used to establish subgroups.
As you can see in the image above, our workbook is still grouped by the “Assigned to” column as before, but now each “Assigned to” group contains secondary groups organized by “Project”.
You can manage your grouping rules from the Groups dialog. Click and drag the drag handles to the left of each rule to reorder rules, or click the x button to the right of a rule to delete it. When you’re done managing your groups, click the blue Apply button in the lower right corner of the dialog.
Creating Group Summaries
Once you’ve added groups to your workbook, you can display summaries for each group in the groups’ header rows. These summaries contain aggregate data like sums, counts, averages, or minimum and maximum values based on the data contained in the group. Each column can have a different aggregation function applied to it.
To add a summary, click the group header row above the column that you want to summarize and select an aggregation function from the dropdown.
When you create a summary in one group, it will automatically be applied to all other groups in the view, as well as all subgroups.
Depending on the column’s data type, different aggregation functions are available. Columns with text or other non-numerical values can display a count of the values in the group or the number of filled, empty, or unique values. Columns with numbers or other numerical values can display all of those, as well as the maximum and minimum values, sums, and averages.
In the example above, we’ve taken our previously created groups and added summary values showing:
- A count of the number of tasks
- The minimum start date for all tasks
- The maximum end date for all tasks
- A sum of workload icons
More Resources: Organizing Workbook Data
Learn more about Spreadsheet.com features that help you organize your workbook data with these resources:
- “Sorting and Row Order” – Learn how to organize your data by sorting it by values in one or more columns
- “Filtering and Row Visibility” – Learn how to hide rows from your View by applying filtering rules
- "Moving Rows" – Learn how to manually reorder rows in your workbooks
- “Hiding and Moving Columns” – Learn how to hide columns from your current View