How to create a spreadsheet CRM with Google Sheets

Opening a spreadsheet CRM can feel like accidentally stumbling onto a Gen Alpha’s TikTok account—disorienting, a bit of panic, and the realization that you might need a teenager to explain it to you. Despite the initial confusion, building a CRM in Google Sheets can be a surprisingly effective way to manage customer relationships (and slightly less terrifying than whatever mewing is).

Spreadsheet CRMs are perfect for those just starting out or small business owners looking to upgrade from that old shoebox under their desk. But before you commit, there’s a whole world of free CRM options out there, including Zapier’s simple CRM template that can help you hit the ground running. 

But if you have a small operation and love the idea of tinkering under the hood, I’ve built a free spreadsheet CRM template to start you off. Here, I’ll give you a step-by-step guide to turning a blank canvas into a bustling hub of customer interactions.

Free CRM template for Google Sheets

To follow along with this process, I’ve created a free Google Sheets CRM template you can use. It includes a pre-built Customer Data sheet to store lead/customer info, an Interactions tracker, and a Dashboard with some basic reporting.

To use this template, head to the Customer Data worksheet, and clear the sample data in each cell by selecting the cells and pressing the delete key. Repeat for the Interactions sheet. After you’ve entered your own data, you can use the built-in formulas and formatting—or go rogue and use your own.

Of course, you can also build your own CRM spreadsheet from scratch in Google Sheets if you prefer. Either way, let’s jump into the steps for customizing it to your needs.

How to build a CRM in Google Sheets in 4 steps

1. Create a customer relationship management database

2. Track interactions in your spreadsheet CRM

3. Ensure consistency with data validation

4. Create a simple dashboard

1. Create a customer relationship management database

The core worksheet in your Google Sheets CRM will be the one housing your customer/lead data. This sheet will essentially function as your centralized address book and will allow you to quickly reference any customer details you need.

To set this up, create a new Google Sheet by typing sheet.new in your browser. Double-click on the sheet name at the bottom to rename it to whatever makes sense for your needs. I named mine “Customer Data.”

Add column headers across the top row for the data points you want to track for each customer, such as their name, contact info, company, lead status, dog’s name, astrological sign, etc. As a basic example, my sheet includes columns for:

• Client ID

• Referral date

• Name

• Email

• Phone

• Lead source

• Assigned to

• Status

• Last contact

• Closed date

You might be curious why I included a client ID when each row is already numbered and there’s a column for the customer’s name. Well, my sweet summer child, row numbers don’t stick when filtering or exporting data subsets, and names can be unoriginal. A dedicated ID solves that mess by making sure your customer data remains structured.

You can automatically assign an ID for each entry using a formula:

=IF(C2="","",TEXT(ROW(A1),"C000"))

• IF(C2="", "", ...) checks to see if cell C2 (the “Name” column) is empty.

  • If C2 is empty, the cell will be blank.

  • If C2 is not empty, the formula proceeds to the next part.

• ROW(A1) finds the row number of cell A1. Since A1 is in the first row, this returns “1.”

• TEXT(...,"C000") specifies that the number should be formatted with the pattern “C000.” So, “1” becomes “C001.”

This formula assigns an ID only if the “Name” cell is not empty, preventing unnecessary ID allocation. To easily extend this formula down the entire column, do the following:

1. Click on cell A2 (or whichever cell has the formula).

2. Hold down Shift, and click on the last cell in the row.

3. Press Ctrl + Enter (command + return on Mac).

The next step is to format your column headers to make them stand out. Use a bold font or fill color. It’s also a good idea to freeze this row so it’s always visible, no matter how long your customer list gets. Simply go to View > Freeze, then select “1 row.”

Enter existing data if you have a current list of customers or leads to import into the CRM. You can do this by exporting a CSV file from your old tool and then using File > Import in Google Sheets to import it. Otherwise, leave it blank to start fresh.

2. Track interactions in your spreadsheet CRM

To really make the most of your spreadsheet CRM, you’ll want to record all conversations and touchpoints with your contacts in a separate worksheet. This helps build a complete picture of your relationship and sales process with each lead or customer.

To start, create a new worksheet by clicking the plus sign (+) in the bottom-left corner of your sheet (or navigate to the Interactions worksheet in the template).

At the bare minimum, I recommend including these columns in your Interactions sheet:

• Date: The date the interaction took place

• Name: The person you spoke with

• Interaction type: Whether it was a phone call, meeting, singing telegram, etc.

• Salesperson: The member of your team who handled the interaction

• Notes: A brief summary of what was discussed

I like to use data validation in the Name column to pull names directly from the Customer Data worksheet. That way, I don’t have to manually re-enter or risk misspelling customer names. (Learn how to do this in the next section.)

While data entry may not be the most thrilling activity, having a detailed log helps you stay on top of where each prospect is in the sales cycle. Use this space to quickly review past conversations before an upcoming call or filter and sort entries to analyze patterns. For example, you might look at all prospects who had five or more interactions but didn’t convert to identify areas for improvement. Or you could examine each salesperson’s interaction volume and outcomes.

3. Ensure consistency with data validation

Data validation allows you to control the type of data users can enter into a cell, eliminating the risk of typos or invalid inputs. It allows you to set specific criteria, such as only allowing numbers, dates, or predefined lists.

For our CRM, we’ll use data validation to create dropdown menus with preset options. I find that the simplest way to set this up is to dedicate a separate worksheet just for these dropdown columns.

Create a new worksheet, and give it a name. Mine is called Dropdowns.

Think about which columns in your CRM would benefit from standardized options. Then, create columns for each set of options, and list the values in each column.

This worksheet doesn’t need to be pretty—in fact, I often keep it hidden from view. You can hide the worksheet by right-clicking its name at the bottom and selecting Hide sheet. (If you ever need to unhide the sheet, select Hidden sheets in the View menu.)

Navigate to the worksheets where you want these dropdown menus to go, and select the column(s) you want to add data validation to. For example, in the Interactions worksheet, I’ll be adding dropdown options for the “Interaction type” column.

Go to Data > Data validation. In the Criteria field, select Dropdown (from a range). Enter =Dropdowns!A2:A (assuming your list starts at A2 in the “Dropdowns” worksheet).

Under Advanced options, I like to select the “plain text” display style to remove the menu display from showing up in blank rows.

• Go to the cell where you want the dropdown.

• Click Data > Data validation.

• Choose List from a range as the criteria.

• Select the corresponding column in your Dropdowns sheet.

By having these options predefined, your team can simply select from the dropdown rather than typing freeform text. This prevents inconsistencies like “lead” vs. “Lead” or “James” vs. “Jim Jam” from creeping into your data.

Plus, when you need to update an option—like adding a new sales rep to your roster—you can do it in one central location and it will automatically update everywhere it’s used in your CRM. No more hunting down and fixing discrepancies manually.

4. Create a simple dashboard

The real power of a CRM is being able to quickly assess the state of your business and identify trends. In this last step, we’ll create a CRM dashboard to surface some high-level reporting.

Start by making a new worksheet and calling it something clever like “Dashboard.” I recommend dragging this sheet to the front so it’s the first thing you see when you open up your CRM.

Next, use formulas and functions to automatically calculate key metrics and charts and graphs to visualize your data.

Example formulas (adjust cell references as needed)

• Total customers: =COUNTA(Customer Data!C2:C) (Assumes customer names are in column C, starting from row 2)

• New customers this week: =COUNTIFS('Customer Data'!B:B, ">="&TODAY()-7, 'Customer Data'!B:B, "<"&TODAY(), 'Customer Data'!C:C, "<>") (Adjust TODAY()-7 to TODAY()-30 for new leads this month.)

• Recent activity: =QUERY(Interactions!A:E,"SELECT A, B, C, D ORDER BY A DESC LIMIT 5", 1) (Filters the five most recent interactions in descending order)

• Active Leads: =COUNTIFS('Customer Data'!H:H,"In progress")

Chart and visualization ideas

To make visualizations, you’ll need to select your desired data and use the chart feature in the Insert dropdown or create a pivot table.

• Interactions by type: Create a pivot table using the Interactions sheet as your data source. Then, create a bar chart or pie chart based on the pivot table data.

• Lead status funnel: Create a pie chart to show the distribution of leads by status (in progress, closed, not interested).

• Leads by source: Create a bar chart to compare the number of leads from each source (e.g., website, referral, advertisement).

• Salesperson performance: Create a bar chart to compare the number of closed deals per salesperson.

• Interaction over time: Create a line chart to show the trend of interactions over time (use the Interaction date from the Interactions sheet).

Some of this can be complicated, so follow these guides if you’re not sure how to do it.

With numbers and charts in place, you’ll have an at-a-glance view of what’s working, what’s not, and where to focus your efforts. While your dashboard won’t be as flashy as other business intelligence software, for a small business, a DIY option in Google Sheets can get the job done. And as your needs grow, you can always link your data to more powerful CRM tools.

Connect Google Sheets to your other apps with Zapier

One of the best parts about using Google Sheets as a CRM is the ability to connect it with thousands of other apps via Zapier. This allows you to automate repetitive tasks and streamline your workflows. Here are a few examples:

Read more about how to automate Google Sheets, or get started with one of these pre-made templates. You’ve got nothing to lose but inefficiency.

Graduate to a simple CRM

If Google Sheets isn’t cutting it for your CRM, but you’re not ready to move up to the big leagues of dedicated CRM software, you can try Zapier’s simple CRM template. Using Zapier Interfaces and Zapier Tables, it’s an automation-first CRM for folks who need a streamlined solution. Collect leads from a form, store lead information in a database, and use a visual tracker to manage the status of each lead. Then, you can initiate actions across thousands of other apps.

Related reading:

This article was originally published in July 2016 by Michael Grubbs. The most recent update was in June 2024.