Grouping Rows

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.

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.

Grouping-Rows-Button.png

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.

Grouping-by-One-Column.png

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.

Grouping-by-1-Rule-Comparison.png

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.

Create-New-Row-in-Group.png

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.

Grouping-by-Two-Columns.png

The first rule listed will be used to establish the primary groups, and any subsequent rules will be used to establish subgroups.

Grouped-by-2-Rules.png

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”.

Managing Groups

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.

Group-Summary-Dropdown.png

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.

Group-Summary.png

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

Read on through the rest of our articles on Views and View Types to learn more about each of the view types Spreadsheet.com offers, as well as more information about ordering and organizing your data with filtering and sorting rows.