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.
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.
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.
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.
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.
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.
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.
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.
In our “Opportunities by Industry” view, we’re using group summaries to display…
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.
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.
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.
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.
Now, each “Stage” group is broken down into “Industry” subgroups.
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.
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.