How to Use Google Sheets as a CRM (+ Template)

Customer relationship management (CRM) software helps automate sales processes and 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. Our guide includes instructions on how to set up your Google Sheets CRM, create process tracking sheets, and configure formula and automations.

Free Google Sheets CRM 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 includes four spreadsheets that make a workbook you can use as basic CRM. These spreadsheets include contact information, deal status, sales overview, and graphs and charts summarizing your sales figures.

Send it to me

Setting Up Your Google Sheets CRM

Creating Google Sheets for CRM 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. We also included a list of top CRM options if you think using formal CRM software is more suitable for your business.

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 deciding what types of data you want to track. You also need to figure out 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 that standard CRMs offer.

We recommend tracking no more than three separate sales process data sets as you get used to working with Google Sheets for CRM. Since you’ll be manually adding most of the data at the onset, don’t give yourself more work than necessary. Our fourth tab includes graphs, which are just visual representations of data from the sales overview worksheet, and do not entail usage of any sales process.

In our example, we used the spreadsheet to track 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 four separate tabs in the Google CRM workbook, including contacts, deals, sales overview, and the graphs.

Tracking sheets in four separate tabs in the Google CRM workbook.

Our guide includes four simple tabs that let you easily toggle with various business data.

Sheet 1: Contacts

The contacts tab is where all essential 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 be contacted on.

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

Predetermine contact types in Google Sheets.

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, it’s an excellent idea 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 it can automatically 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 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.

Sheet 4: Graphs

The graphs tab includes visual representations of your data in the sales overview tab. These graphs are connected to the other tabs and will automatically update when you make changes to the sales figures in either the deals or sales overview tabs. You can simply lift these graphs and put them into reports or presentations that provide an overview of your sales performance.

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.

Naming CRM spreadsheet column headings efficiently.

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, such as job title or social media pages. 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.

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, to 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 whomever 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 represent the value of a contract with recurring revenue.
Deal stage: Use this field to identify where each sales opportunity is in the sales process, including interest, proposal, won, or lost.

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 out 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 so that’s it’s easy to spot trends and track your monthly sales progress.
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 revenue for each month and deal stage.

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 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 that you need to reach out to that lead. Google has instructions on conditional formatting in Google Sheets.

Adding conditional formatting in Google Sheets.

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

Use Data Validation Lists

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.

Data validation lists helps keep your CRM data clean and organized

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

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

Reference Data From Other Sheets

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.

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

5. Import Existing Data

The easiest way to populate your new sales CRM spreadsheet is to enter data into your sheet 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 minimal 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 Spreadsheet CRM

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

Generate Reports

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.

Google Sheets allowa you to generate actionable reports and sort your list.

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.

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

Pre-populating 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 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, set its sharing preferences accordingly. 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 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: Google Sheets offers a streamlined, simple way to keep all your contact information and track deal updates.
Solo entrepreneurs keeping track of various revenue streams: Solopreneurs who want a simple tracking system of their actual and projected revenue from multiple sources can efficiently do so using Google Sheets.
Small business 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 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 duplications due to the large volume of manually inputted data.
Users who employ many third-party apps: If your company uses many third-party apps, such as social media management, project management, invoicing, and sales automation, you’re better off using a dedicated CRM platform that seamlessly integrates with other apps.
Larger sales teams that want to monitor sales reps’ performance: If you want to monitor the targets and quotas of sales reps, Google Sheets is not an ideal option because you can only do basic automation functions on the platform.

Google Worksheet CRM Alternatives

While creating your own Google Worksheet CRM is an effective CRM alternative because it provides a simple way to track your sales and deals, actual CRM software offers much more advanced features. Below we list down some of the top CRM tools to consider:

HubSpot CRM

HubSpot CRM is a feature-packed CRM software with sales, marketing, and service tools. It has a lead management feature that allows you to monitor the progress of your active leads and record the potential revenue each one brings. Its sales automation functions automatically calculate your projected revenues, wins and losses, as well as detect inactive deals.

HubSpot Sales Hub lead management feature.

Leads recorded into the system can be converted to data-drive and insightful sales reports. (Source: HubSpot Sales Hub)

Apart from lead management and sales reports, HubSpot CRM also offers tools for email marketing to help you expand your lead outreach activities and strengthen your relationship with existing customers. HubSpot CRM is a solid option if you want a free, but robust system for your first CRM. To learn more about this provider, read our in-depth HubSpot CRM Review.

Freshsales

Freshsales is one of the most popular CRM tools among small businesses because of its intuitive interface and affordable pricing plans—with a free version for unlimited users available. In addition, it has sales automation tools that help sales teams attract quality sales leads, engage with prospects, and nurture customer relationships. It also features artificial intelligence (AI)-powered deal insights you can use for revenue and sales forecasting.

Freshsales presents sales numbers in a simple and easy-to-understand format that makes it easy for users to monitor their overall business performance. (Source: Freshsales)

One of Freshsales’ standout features is its feature-rich, in-app phone, allowing users to call leads and customers straight from their desktop app. This makes it easy for agents to follow up on their leads and record their history conversations in the notes section of the app. Interested in learning more? Read our comprehensive Freshsales review.

Salesforce

Salesforce is one of the most sophisticated CRM software options on the market. Its sales automation functions let users manage and monitor the progress of their leads and opportunities, as well as forecast the incoming revenues based on the active deals in the sales pipeline. Its advanced lead scoring feature enables users to evaluate the likelihood to close a deal and pursue those who are most likely to convert into paying customers.

Salesforce’s dashboard provides many helpful visual representations to help users make sense of their sales data. (Source: Salesforce)

Apart from its advanced sales automation tools that enable users to manage all their leads, deals, and opportunities, Salesforce also offers a highly customizable dashboard. This allows users to tailor-fit their dashboard to suit their own sales processes. Learn more about its rich feature set in our Salesforce review.

Bottom Line

A Google Sheets CRM provides a no-frills way to manage your contacts and leads, as well as monitor your sales progress and incoming revenue. However, it’s essential to keep in mind it is only ideal for handling small amounts of data and you need to manage it manually. You can also simply use it as a Google Sheets customer database. Organizations that handle large volumes of customer and business data should consider using a dedicated CRM instead.