October 17th is Spreadsheet Day! Get a $50 credit if you sign up for a paid plan between now and the 17th.

Grouping Rows in a Simple CRM

When CRMs grow to dozens, hundred, or even thousands of rows, sorting through all of the records can be a time intensive and painstaking process without a good organizational structure. With built-in tools like sorting, filtering, and row grouping, Spreadsheet.com gives you a variety of ways to organize and streamline your CRM.

In this guide, we’ll take a look at a modified version of Spreadsheet.com’s Simple CRM template and see how grouping, sorting, and filtering rows can help you effectively manage lots of records. Take a look at our sample workbook and follow along, or create a copy for yourself to experiment.

Getting Started: Choosing a Grouping Method

Before creating row groups, we need to determine how we want our rows to be grouped. Row grouping looks at a column and groups all of the rows that share similar values in that column. If we wanted to organize our sheet by stage, we would group by Column E - Stage. Or, if we wanted to organize our sheet by industry, we would group by Column C - Industry.

By creating additional Sheet views, we can group a single worksheet in multiple ways. Spreadsheet.com lets you create an unlimited number of views for each worksheet. When changes are made to the data in one view, those changes will be reflected in all of the other views that belong to the same worksheet.

Creating a New View

Because we want to create multiple types of groups for our worksheet, we’ll create new Sheet views for each of our grouping rules. To create a new Sheet view, open the Views sidebar by clicking on the name of the current view below the workbook title, click the + Sheet View button, and name your new view.

Create new views from the Views sidebar

Grouping Rows by One Column

With our new “Opportunities by Industry” view, we’ll create groups of rows based on the information in Column C - Industry. Grouping rules are defined from the Groups dialog. Click the Group button in the Views toolbar and select the column by which you want to group your rows from the dropdown.

Create new grouping rules by opening the Groups dialog from the toolbar

Here, because we’re grouping our rows by industry, we’ve selected C - Industry from the column dropdown. Once we apply our grouping rule, all of the rows that belong to the same industry will be placed into groups with one another.

"Opportunities by Industry" Sheet view grouped by Column C - Industry

Now, our previously unsorted data is organized into industry-specific groups. We can use other organization options like filters and sorts in conjunction with our row groups to further organize our sheet.

"Opportunities by Industry" Sheet view with groups, sorts, and filters applied

Take a look at the image above. It’s the same “Opportunities by Industry” view we’ve been looking at but is now filtered by Column E - Stage (excluding deals marked as “Closed Lost” and “Closed Won”) and sorted by Column F - Probability (in ascending order).

Notice how the sorting rule is applied to each group without overriding them; data within each group is sorted by our rule.

Creating Group Summaries

Once we’ve applied our grouping rule, we can use the groups’ header rows to display aggregate information about each group. Creating summary statistics in the group header doesn’t require inputting any functions or formulas; click on the group header row in the column you want to aggregate and select an aggregation function from the dropdown.

Create group summaries by clicking on any group's header row

In our “Opportunities by Industry” view, we’re using group summaries to display…

  • A count of the deals in each industry
  • A sum of the total revenue for each industry
  • An average of the probability for closing a deal in each industry
  • A sum of the forecasted revenue for each industry
  • A sum of the forecasted profit for each industry

Notice how, when a summary statistic is created for one group, it’s automatically applied to all other groups as well. By collapsing each group, only the statistics in the group header rows will be visible making it easy to compare groups.

"Opportunities by Industry" Sheet view with each row group collapsed

With our group summaries, we can see that our deals in the Restaurant industry have the highest probability of closing, but the forecasted profit is only about half that of deals in the Hospitality industry, which has the lowest overall probability of closing.

We can use insights like these to allocate resources more effectively. We’ll probably want to pay more attention to our hospitality deals since there’s more profit to be had, and can move some of our resources off of our restaurant deals since those are already more likely to close.

Grouping Rows by Multiple Columns

In addition to grouping rows by a single column, Spreadsheet.com also lets you group rows by multiple columns. When rows are grouped by multiple columns, each primary group is further grouped into subgroups.

For our workbook, we’ll create a new Sheet view – “Opportunities by Stage” – and group it by Column E - Stage.

"Opportunities by Stage" Sheet view grouped by Column E - Stage

Then, in the grouping dialog, we’ll add a second grouping rule by clicking the + New group button and this time choose Column C - Industry from the dropdown.

Grouping rule with multiple criteria

Now, each “Stage” group is broken down into “Industry” subgroups.

"Opportunities by Stage" Sheet view grouped by Column E - Stage and Column C - Industry

When we create row summaries like we did previously, they’re applied to both the primary groups and the subgroups. And like we saw in our earlier example, these subgroups can be collapsed as well to only show the summary statistics.

"Opportunities by Stage" Sheet view with all row groups and subgroups collapsed

Ready to get started? Browse Spreadsheet.com’s Template Gallery to find ready-to-use CRM and sales templates, as well as templates for construction project management, education, finance, and more. Or, visit our Help Center to learn more about working with row grouping.

Sign up — it's free