In this article:

Create a Free Google Sheets CRM Template (The Easy Way)

June 6, 2024

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
  • Cloud-based
  • Massive library of add-ons
  • Easy collaboration

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 pipeline management 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.

Google Sheets CRM template, blank CRM data sheet

Step 3: Integrate CRM data sources to Google Sheets template

Here are options to import CRM data to Google Sheets CRM:

IMPORTRANGE

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.

Google Sheets Spreadsheet URL in the address bar

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!

Google Forms

Check out this guide if you use Google Forms to gather CRM data.

Add-ons

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. 

Platform-specific tutorials

We even have several tutorials for importing CRM data from individual eCommerce and marketing platforms to Google Sheets:

Google Analytics

Firestore

Firebase

Klaviyo

Mailchimp

MongoDB Atlas

MySQL Database

PostgreSQL

Salesforce

Shopify

Snowflake

Don’t forget to filter the data!

You can use the QUERY function to filter the incoming data. Check our guide here!

Step 4: Format the important CRM 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. 

Dropdown lists

Dropdown lists are crucial in helping you tag customers and their interactions quickly. Learn how to maximize the power of dropdown lists here.

CRM Google Sheets template, dropdown list for lead status

Conditional formatting

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. 

CRM Google Sheets template, conditional formatting for highlighting lead status

Data filters

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:

Mini-search engine

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. 

Google Sheets CRM template, data sheet loaded with CRM and sales data

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:

Charts and SPARKLINE

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!

Pivot Tables

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.

Some functions to calculate metrics

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.

Google Sheets CRM template, CRM dashboard with KPIs and charts

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

Google Sheets CRM template, share settings

Step 3: You can modify the access level of each user by clicking the dropdown list on their right:

Google Sheets CRM template, access permissions

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:

Step 1: Click View.

Step 2: Select Show.

Step 3: Untick Gridlines.

CRM Google Sheets template, untick gridlines

The gridlines are now hidden.

CRM Google Sheets template, gridlines hidden

Step 4: Click File.

Step 5: Click Download.

Step 6: Select Portable document file (PDF) option. 

Google Sheets CRM template, export in pdf option selected

Step 7: Adjust the page settings for the report.

Google Sheets CRM template, print settings 

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!

The sample Google Sheets spreadsheet CRM template 

You can copy the sheet yourself and modify it to your liking!

A better alternative to Google Sheets CRM

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. 

Lido built-in data import function

Step 2: Create a dashboard with its impressive set of features such as charts and tables. 

Lido dashboard with data loaded

You can also use Lido to create other tools for your business, whether it is for marketing, inventory, or for sales. 

Click here to get started today!

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->