In this article we will show you exactly how to create a CRM for your business in google sheets. Most companies are paying for overpriced CRM software when google sheets can be used as an effective and free solution.
Don’t care about the step by step process to create a CRM and just want to use our template? Scroll to the bottom of this article to use our FREE template.
A Google Sheets CRM (customer relationship management) template helps you organize your contacts with relevant information such as their name, company, role, ways to contact them, and their position along the sales process.
A Google Sheets CRM combines the utility of a CRM with the familiarity of Google Sheets. Once you start using a CRM you will wonder how you lived without one.
Therefore a Google Sheets based CRM template will be a good fit for you.
A customer relationship management (CRM) system is used by businesses to manage their interactions with existing and new potential customers.
It contains a wide variety of information about your customer ranging from basic details such as the name, account information, and their position along the sales process.
What are the benefits of a CRM?
Google Sheets remains to be one of the most popular cloud-based spreadsheets today. Its popularity is rooted in the following reasons:
You may be tempted to go straight to the end of this article and simply copy the free template we are sharing with you.
If that’s what you’re planning, I encourage you to wait a minute!
You need to read the following steps so you know how to modify our template to suit your business needs and boost your sales.
First you need to think about what your pipeline management CRM should look like. There are three questions you can ask yourself:
You need the following CRM information:
If you are deploying the Google Sheets CRM spreadsheet as part of a cloud-based system for your business, you should identify the sources of the data you gather. Here are some examples:
The specific details will depend on the nature of your business. While setting up the data pipeline for the CRM can be a little too complicated especially if you are just starting your business, it will give you long-term flexibility once your brand starts picking up leads and paying customers left and right and your sales starts building up.
Finally, you need to determine the key performance indicators (KPIs) to track in your CRM. They can give you the birds-eye view of the success you have in acquiring and converting leads and upselling customers. Some of the KPIs you can calculate are the following:
Depending on your need, the KPIs you include in your Google Sheets CRM may include those that are not directly CRM related. You may as well include sales KPIs to see how your lead management affects your sales and revenue.
The sample template will feature close rate and churn rate for the CRM related KPIs and also include sales-related metrics such as total monthly revenue.
After deciding what data you need to input to your CRM template, it’s time to design the interface.
Here are some tips for designing the interface of your Google Sheets CRM:
For our Google Sheets CRM sample template, we will create two sheets, one for the lead progress and one for the kpi dashboard. The lead progress sheet contains basic contact information, their position along the sales funnel, and the potential and actual sales income from them, if any.
Here are options to import CRM data to Google Sheets CRM:
If the data is from another Google Sheets spreadsheet, you need to use IMPORTRANGE:
1. Identify the spreadsheet URL and the range within the source sheet that contains your data.
2. Use IMPORTRANGE with the following syntax:
=importrange(“spreadsheet_url”,”data_range”)
That’s how simple it is! Click here for our IMPORTRANGE tutorial if you want further explanation!
Check out this guide if you use Google Forms to gather CRM data.
Add-ons add functionality to Google Sheets, and you can use them to import CRM data. Learn more about some of the best add-ons you can utilize to import CRM data to Google Sheets.
We even have several tutorials for importing CRM data from individual eCommerce and marketing platforms to Google Sheets:
You can use the QUERY function to filter the incoming data. Check our guide here!
Once you set up the data import functions, it’s time to format the important CRM data. The formatting you plan to implement should be included in Step 2 during the part designing the Google Sheets CRM.
Dropdown lists are crucial in helping you tag customers and their interactions quickly. Learn how to maximize the power of dropdown lists here.
Conditional formatting is used to highlight cells according to the conditions that their values may fulfill or not. We will use this to highlight the leads according to their position along the sales funnel. Read here to find out the wide variety of applications you can use with conditional formatting.
If you need to check the individual CRM data, you can add tools to filter the CRM data to make the search easier. You can use one of the following:
The wide variety of functions in Google Sheets gives you lots of possibilities in improving the functionality of your Google Sheets CRM. One such functionality is adding a mini-search engine to your sheets. It will help you zoom in on individual leads and discover what you can do to either nudge it closer to the end of the sales funnel or increase your sales through upselling. With the large list of functions available to you, you can implement solutions beyond the usual keyboard shortcut (Ctrl+F) in Google Sheets:
Our Google Sheets CRM sample template contains dummy CRM data loaded with relevant basic information, contact information, potential and actual sales from closed leads. This will help you see the possibilities for your own CRM template.
After formatting your data, it’s time to set up the dashboard! Here are what you can add to your dashboard:
Your dashboard should contain at least one chart!
First, learn the basics of creating charts in Google Sheets here.
Afterwards, learn which chart type to use best here.
For example, it’s best to use a funnel chart if you want to visualize your sales process and the progression of your leads from awareness to sales. You will find our tutorial on funnel charts useful.
If you find charts too big and you need ones the size of an individual cell in your dashboard, then the SPARKLINE function is the solution!
You will still need to display hard CRM and sales data to your dashboard. The Pivot Table comes to the rescue! Learn how to use Pivot Tables here.
Google Sheets sports a wide variety of functions for sophisticated calculations necessary for calculating the KPIs. Some of them are the following:
The Google Sheets CRM sample template has a sheet labeled KPI dashboard. It contains CRM metrics and relevant information such as sales and geographical data. It also sports two charts - a funnel chart and a geochart.
Once you have finished creating the template, you can start adding users to access it. Here are the steps:
Step 1: Click the Share button on the upper-right corner.
Step 2: Add the Google email addresses of users whom you want to share the template.
Step 3: You can modify the access level of each user by clicking the dropdown list on their right:
Step 4: Click Done.
Since the sales and CRM data changes over time, you can generate reports and distribute it to others via email. One way is to save them as a PDF:
Step 1: Click View.
Step 2: Select Show.
Step 3: Untick Gridlines.
The gridlines are now hidden.
Step 4: Click File.
Step 5: Click Download.
Step 6: Select Portable document file (PDF) option.
Step 7: Adjust the page settings for the report.
Step 8: Click Export.
The CRM report will be automatically saved to your computer. You can share the file to other users and stakeholders whenever needed!
You can copy the sheet yourself and modify it to your liking!
Do you feel a bit limited by Google Sheets? We have good news for you!
Enter Lido, the newest cloud-based spreadsheet app. Creating a CRM template only takes a few steps:
Step 1: Load the data from ecommerce and marketing platforms using its built-in import function that can integrate data from several popular e-Commerce, marketing, and sales platforms such as Facebook Ads, Google Ads, Salesforce, and Shopify.
Step 2: Create a dashboard with its impressive set of features such as charts and tables.
You can also use Lido to create other tools for your business, whether it is for marketing, inventory, or for sales.
