A Complete Guide to Using Google Sheets as a CRM (+ Template)

Customer relationship management (CRM) software helps sales teams track leads, contacts, and opportunities. While dedicated CRM solutions offer advanced functionalities and automation tools for processing business and sales data accurately, it is possible to set up your own CRM using Google Sheets. Follow our step-by-step guide on how to use Google Sheets as your CRM and start managing and organizing customer records, interactions, and sales.

Free CRM Spreadsheet Template

The quickest way to set up a Google Sheets CRM to keep track of leads, contacts, and sales opportunities is by downloading our free template. It consists of three spreadsheets that make a workbook you can use as basic CRM. These spreadsheets include contact information, deal status, and a sales overview.

A snapshot of the contacts tab where all important information for all your contacts and leads must be stored.

Download Your Free CRM Spreadsheet

Setting Up Your Google Sheets CRM

Creating a CRM using Google spreadsheets is simple—especially if you’re familiar with using Google Sheets or Microsoft Excel. Follow these five steps to get started, and check out our bonus tips at the end of this article on how to maximize your new Google Sheets CRM.

5 Steps To Create CRM Using Google Sheets

1. Identify Information & Data to Be Processed in the CRM

The first step in setting up your CRM in Google Sheets is to decide what types of customer data you want to track. You also need to decide on what information you should extract from your deals and contacts. Identifying this information upfront saves time and effort later. It’s more difficult to make workflow changes down the road without the advanced data management tools standard CRMs offer.

We recommend tracking no more than three separate sales process data sets as you get used to working with Google Sheets as CRM. Since you’ll be manually adding most of the data at the onset, you don’t want to give yourself more work than necessary.

In our example, we’ll track only contacts, deals, and sales status. Then, we’ll drill down into the specific data each sheet captures by creating a column for each snippet of information.

2. Create Process Tracking Sheets in Different Tabs

Once you’ve determined what data you want to track, create three separate tabs in the Google CRM workbook, including contacts, deals, and your sales overview.

Our guide includes three simple tabs that lets you easily toggle among various business data.

Sheet 1: Contacts

The contacts tab is where all the important information about your contacts is saved. These include company name, contact details, contact type, source, and other helpful notes, such as a preferred time to call or a certain communication channel to contact them through.

There’s no easy way in Google Sheets to segment your contacts by type, such as lead, referral, or other categories. Hence, your best option is to create a drop-down menu for that column of data. We’ll discuss more on how you can do this in the fourth step.

Predetermine contact types in your spreadsheet so users can simply choose from the drop-down menu when adding a new contact.

Sheet 2: Deals

These are the sales opportunities you’re trying to close, so carefully track their progress through each deal stage. Without the benefit of a formalized CRM visual pipeline to view information at a glance, your best bet is to create a second sheet in your Sheets workbook dedicated to deals.

Under this sheet, include the deal name, company, deal value, deal status, and likelihood to close. This is where you can indicate whether a deal is at the interest stage, proposal stage, or whether it has been won or lost, so that it can auto-calculate your revenue in the sales overview tab.

Sheet 3: Sales Overview

The sales overview tab is a quick snapshot of your expected sales in a specified time period. It shows the value of all deal stages—from interest and proposal stages to won and lost deals. This gives you an idea of where you are in terms of incoming revenue and areas with lost opportunities that need more attention from your team in the coming months.

3. Name Your Column Headings Efficiently

Once you’ve set up your CRM spreadsheet file, create headers in the first row of your spreadsheet. These columns will hold the different types of information you want to keep track of for each contact and deal. Choose these headers carefully because each represents a data point you’ll use later to sort, search, and find specific records.

We recommend using the following headers for your contact sheet, though you can modify it to add any additional types of information you need to track. If you discover you’re tracking more data than needed, it’s easy to delete those columns. However, it’s more difficult to add data retroactively.

Set up The Column Headings of your CRM spreadsheet

Contact Sheet Headings

This sheet should include basic contact information and a few other data points.

Contact details: Each deal should be associated with a person, whether a lead or an existing customer. In addition to a column for each contact’s name, capture columns for their email address, phone number, title, company, and address.
Contact type: This indicates whether they are a lead, referral, customer, or another type of contact.
Lead source: It’s worth tracking where your leads originate, no matter the outcome, so you can make informed decisions about where to invest your future marketing efforts.
Last contact: Keep track of when you last communicated with a contact. Use this field to update the date each time you email or speak with them.
Notes: Make notes here about conversations or other communications you have with the contact, as well as any action steps needed.
Owner: Use this field to indicate whoever in your team is responsible for reaching out to a specific contact.

Deals Sheet Headings

This tracks your deals and tells you important details about each one at a glance.

Deal name: This is what you call the deal (for example, “Adcom Group Spring 2021 Project”). Use names that you’ll be able to quickly find and associate with specific sales opportunities.
Deal description: In addition to the name, include a brief description of the deal so you and your team can reference the scope of the sales opportunity.
Likelihood of closing: To give yourself a picture of which deals are most likely to close, use this field to track the percentage likelihood each sales opportunity will close.
Deal value: This represents the estimated, confirmed, or proposed value of the deal. Depending on the types of products or services you sell, this might be a one-time cost, or it may represent the value of a contract spread out over time.
Deal stage: Use this field to identify where each sales opportunity is in the sales process.

Pro tip: To make it easy to compute the total projection for your deal value, group deals based on the stage they are in. If a deal’s status is updated, cut and paste the whole row to the deal stage where it currently belongs. Refer to our template for more information.

Sales Overview Headings

This provides a quick overview of your sales projection for a specified time frame.

Deal stage: List the deal stages you’ve identified in your deals tab, so you can see the revenue projections for each one accurately.
Timeline: Identify the duration you want to measure your sales status—the most ideal duration is a month-to-month basis.
Revenue projection: Based on the total amount in your deals tab, this column auto-calculates the amount for each deal stage using the data referencing formula. It shows you a quick summary of the status of your sales outreach and projected revenues for each month and deal stages.

4. Configure Simple Sales Formulas & Automations

After naming the columns in your various tabs, it’s time to add automation functions to make your data work together to create a working CRM system. Below are some samples of simple functions that you can add to your spreadsheets:

Add Conditional Formatting

Conditional formatting lets you set rules that allow you to pinpoint items that need attention.

Conditional formatting allows you to create rules based on the information in a cell. For example, you can set up a rule that turns the cell background red if the date in the “Last Contact” column is more than four weeks ago, alerting you need to reach out to that lead. Google has instructions on conditional formatting in Google Sheets.

Use Data Validation Lists

Data validation lists restrict users from adding customized data in certain columns, which helps in better data segmentation.

Consider using data validation to ensure only certain types of data are added to a cell or range. In the sample template, we’ve used this feature in the “Contact Type” and “Stage” columns to limit the information to specific lists. This helps keep your CRM data clean and organized for easier sorting and reporting.

Learn how to create your own in-cell drop-down lists with this Google instructional on data validation.

Reference Data From Other Sheets

Referencing data from other sheets automatically mirrors its value, increasing accuracy and reducing manual data entry.

You can reference data from other cells to bring together the most important figures in a condensed format. In the sample template, we summarized the values of the various deal stages into a table in the sales overview tab to display the values of the sales outreach. Learn how to reference data from other sheets.

5. Import Existing Data

The easiest way to populate your new CRM spreadsheet is to enter data into your spreadsheet manually: one contact record, or deal, for each row of the spreadsheet. If you only have a few contacts, this is a manageable way to get your information into the CRM with a minimal amount of work.

Alternatively, if you’ve previously used a digital contact management tool like an address book, such as Mailchimp or Constant Contact, you can usually export your contacts as a CSV file.

Once this step is complete, you’re ready to start using your Google Sheets spreadsheet workbook as a CRM.

Making the Most of Your Google Sheets CRM

One of the benefits of using Google Sheets as your CRM is its ability to transform simple spreadsheets into an automated tool that is capable of processing business data. Below are other suggestions you can do to maximize the use of your new CRM.

Generate Reports

Google Sheets lets you easily alphabetize your contacts so it’s easier to sort through your list, especially if there are hundreds of contacts stored.

Your CRM data can easily be sorted by column, allowing you to generate actionable reports according to any characteristic or type of contact information. For example, you can keep your deals and contacts in alphabetical order, in order by deal value, or in the order of most recent contacts.

To sort data, move your cursor over the top row and click on the selection arrow that appears. Scroll down the menu and select whether to sort the column in ascending or descending order.

Prepopulating columns with predetermined filters will make it easy for you to keep your CRM organized as users won’t be able to input random information and values.

Filtering is another powerful tool that helps you create reports based on specific conditions or values. As an example, you can create a view that shows the leads that you haven’t communicated with in the last month. You may also filter rows showing only deals over $5,000 that have a probability of closing of at least 80%.

Share & Centralize the Use of Your CRM

If you intend to use your CRM with a team, be sure to set its sharing preferences accordingly. It is ideal to work with only one document, so everyone has access to updated information about your contacts, leads, and sales progress. In Google Sheets, click the blue “Share” button at the top right of the interface to give specific individuals or your whole team access to the CRM workbook.

Who a Google Sheets CRM Is Good For

Google Sheets is easy to turn into a simple CRM system to keep track of your most important customer-related information, but it’s not for everyone. Below are some of the users who can benefit best from it:

Users looking for a simple system to organize their contacts and deals: If you’re looking for a way to keep all your contacts’ information and basic deal updates organized in one place, storing them in Google Sheets comes in handy.
Solo entrepreneurs keeping track of various revenue streams: Solopreneurs who want a simple tracking system of their actual and projected revenues from multiple sources can efficiently do so using Google Sheets.
Small businesses owners with only one or two sales staff: If you’re working with only a couple of salespeople, use Google Sheets to monitor the status of their lead and deal outreach so that everything is properly accounted for.

Who a Google Sheets CRM Is Not a Good Fit For

Below are users who will benefit from dedicated CRM software.

Businesses that handle large volumes of contacts and customer data: If you’re dealing with hundreds of contacts, using Google Sheets as your CRM may result in data duplication due to large volumes of manually inputted data.
Users who employ many third-party apps: If your company uses a lot of other third-party apps, such as social media management, project management, invoicing, and sales automation, you’re better off using a dedicated CRM software that seamlessly integrates with other apps.
Larger sales teams that want to monitor their sales reps’ performance: If you want to monitor the targets and quotas of sales reps, Google Sheets is not an ideal option as you can only do very basic automation functions in the platform.

Fortunately, there are many free and excellent CRM tools available, such as Freshsales. It has a free plan with unlimited user allocation, is easy to learn, and comes with essential CRM functionalities, such as contact and account management, deal management, in-app telephony, and a mobile app. Paid plans are also available when you’re ready to grow and upgrade.

Visit Freshsales

Bottom Line

Google Sheets CRM provides a straightforward way to manage your contacts and leads, as well as monitor your sales progress and incoming revenues. However, it’s essential to keep in mind it is only ideal for handling and processing small amounts of data. Those who handle large volumes of customer and business data should consider using a dedicated CRM instead.