Create a Free Google Sheets CRM Template (The Easy Way)
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.
Why create CRM in Google Sheets?
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.
If you are just starting your business, you will find that most of the existing solutions on the market are expensive and hard to justify.
You are using Google Drive to set up what you need for your business, and want to maximize it’s capabilities.
Therefore a Google Sheets based CRM template will be a good fit for you.
What is CRM?
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?
Birds-eye view of your sales process
Highlights which leads are worth investing in
See points for improvement
Gives you easy access to relevant sales information
Helps you find which of the closed leads you can upsell, increasing sales
Makes collaboration between the marketing and sales teams easier
Why Google Sheets?
Google Sheets remains to be one of the most popular cloud-based spreadsheets today. Its popularity is rooted in the following reasons:
Intuitive and easy-to-use
Full features for free
Massive library of add-ons
How to create a spreadsheet CRM template in Google Sheets
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.
Step 1: Define what you need from your CRM template
First you need to think about what your CRM should look like. There are three questions you can ask yourself:
What should you include in your CRM?
You need the following CRM information:
Basic details such as the name, account information, and the contact information of the customer
Descriptive details such as affiliation, industry, and interests
Interaction details such as the instances of interaction and customer satisfaction
Position along the sales process
Where can you get CRM information from?
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 basic sales information can be gathered through an online form such as a newsletter or from your outreach team.
The descriptive details and their position along the sales process can be filled up by your sales or marketing team as they interact with the customer
The customer can be flagged as reaching the end of the sales process once they make a purchase to your online shop, and the transaction details recorded as well
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.
What are the KPIs to track?
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:
Close rate: percentage of lead that has been converted to customers
Churn rate: percentage of leads dropping out
Customer retention rate: percentage of returning customers compared to new customers
Net promoter score: willingness of your customers to promote your business
Upsell rate: percentage of customers who upgraded their purchases and/or services compared to existing customers
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.
Step 2: Design the CRM template interface
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:
It should contain basic contact information and a summary sheet showing their stage along the sales funnel.
Use color schemes that make it easy for you to see the CRM data.
When designing the KPI dashboard, choose the most relevant CRM data and metrics for your decision-making.
For the CRM dashboard, you can use a combination of pivot tables and data visualization tools such as charts.
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.
Step 3: Integrate CRM data sources to Google Sheets template
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.
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.
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.
Step 5: Set up the CRM dashboard
After formatting your data, it’s time to set up the dashboard! Here are what you can add to your dashboard:
Google Sheets sports a wide variety of functions for sophisticated calculations necessary for calculating the KPIs. Some of them are the following:
AVERAGEIF: calculates the average of numbers within a range that meet a given set of criteria
SUMIF: sum data from cells only if they meet a certain condition
STDEV: calculates the standard deviation of the dataset, useful for flagging unusual values
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.
Step 6: Share the template
Once you have finished creating the template, you can start adding users to access it. Here are the steps:
Step 1: ClicktheShare 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.
Step 7: Generate CRM reports from your template
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:
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.