How to Create a CRM Using Google Sheets (+ Template)

Customer relationship management (CRM) software helps sales teams keep track of leads, contacts, and opportunities. While dedicated CRM solutions come with added functionality, you can easily set up a Google Sheets CRM to keep track of the most important information if you need a no-frills solution. Keep in mind that CRM spreadsheets can quickly become unwieldy without tools to provide quality, clean data that’s free of duplications and preventable inaccuracies.

For the best of both worlds, consider a dedicated solution like Freshworks CRM. It’s free for unlimited users, easy to learn, and comes with the tools you need built-in. When you’re ready to grow, paid plans offer more features at affordable prices. Visit Freshworks CRM to learn more about this small business-optimized CRM and sign up for a free account today.

Visit Freshworks CRM

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 together make a workbook you can use as a basic CRM. We’ll walk you through the setup here and you can follow along by opening our template in a Sheets document using your Google account. Or, if you prefer, the same steps also work in Microsoft Excel and Apple Numbers.

Download Your Free CRM Spreadsheet

Setting Up Your Google Sheets CRM

Creating a CRM using Google spreadsheets doesn’t have to be daunting. Follow these four steps to get started, and check out the bonus tips at the end to add even more capabilities to your new CRM.

1. Define Your Sales Processes

The first step to setting up your CRM in Google Sheets is to make some decisions about what kind of customer data you want to track and what information is important to include about your deals and contacts. Defining your sales process upfront saves you time and effort later because it’s much more difficult to make major 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 as a CRM. Since you’ll be manually adding most of the data at the outset, you don’t want to give yourself more work than you need.

In our example, we’ll track only contacts and deals. Then, we’ll drill down into the specific data each sheet will capture by creating a column for each snippet of information, like contact address or deal stage.

2. Create Process Tracking Sheets

Now that we’ve determined what data we want to track, we’ll create two separate tabs, or sheets, in your Google CRM workbook.

Sheet 1: Contacts

There’s no easy way in Google Sheets to segment your contacts by leads, customers, partners, or other lists. Your best option is to manually enter the contact type on your spreadsheet, or create a drop-down menu for that column of data. This is the first sheet, or tab, of your CRM.

Sheet 2: Deals

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

3. Name Your Column Headings

Now that you’ve set up your CRM spreadsheet file, you need to create headers in row one of your spreadsheet for each column. 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 be able to 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 that you need to track. If you later discover you’re tracking more data than you need, it’s easy to delete those columns. However, it’s much 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, be sure to also capture columns for their email address, phone number, title, company, and address.
• Contact type: This indicates whether they are a lead, customer, or another type of contact.
• Stage: Depending on how you acquire new leads, you can use this field to keep track of where a contact is in your lead nurturing process.
• 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: You’ll want to keep track of when you last communicated with a contact, so use this field to update the date each time you email or speak with a contact.
• Notes: Make notes here about conversations or other communication you have with the contact, as well as any action steps needed.
• Owner: Use this field to indicate who is responsible for this deal.

Deal Sheet Headings

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

• Deal Name: This is what you will call the deal (for example, “Mitchell SEO Audit”). Use names that you’ll be able to quickly find and associate with specific sales opportunities.
• Deal Description: In addition to the name, it can be helpful to include a brief description of the deal so that you and your team will be able to reference the scope of the sales opportunity.
• Likelihood of Closing: If you want to give yourself a picture of which deals are most likely to close, use this field to track the percentage likelihood that each sales opportunity will close. This allows you to calculate the current value of each deal.
• Deal Value: This represents the estimated, or proposed, value of the deal. Depending on the types of products or services you sell, this might be the one-time cost, or it might represent the value of a contract spread out over time.
• Deal Category: If you sell multiple types of products or services, you can use this to easily identify the type of sales opportunity. For example, “website design” or “SEO audit.”
• Deal Stage: Using the stages you defined earlier, you can use this field to easily indicate and identify where each sales opportunity is in the sales process.

4. Import Existing Data

Depending on how many contacts you have, the easiest way to populate your new CRM spreadsheet may be to enter the data into your spreadsheet manually, with 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.

If you’ve previously used a digital contact management tool like an address book, Mailchimp, Constant Contact, or an Outlook contact list, you can usually export your contacts as a CSV file that can be opened in Excel or Google Sheets. This is also true of Gmail, which allows you to export your contacts into a CSV file, open that file in Excel or Google Sheets, and then copy the records into your new spreadsheet.

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 CRM

One of the benefits of spreadsheet software is how it allows you to interact with your data to transform your CRM from a list of names into a tool that you can use to stay on top of your sales processes. These automation tools can help you get the most out of your new CRM.

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. In fact, we’ve set up the sample template to do just that. If you want to add your own rules, Google has instructions on conditional formatting in Google Sheets.

Use Data Validation Lists

Consider using data validation to ensure only certain types of data can be 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.

Integrate With Other Tools

Once you have your CRM set up, you can use automation tools to help you keep your CRM data up to date as you generate new leads. For example, if you use an email marketing tool like Mailchimp, you can set up rules using a go-between automation tool like Zapier to automatically add new subscribers to your spreadsheet as new rows.

Generate Reports

Your CRM data can easily be sorted by column, which allows 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.

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

Set up filters by selecting the Data menu > Filter Views > Create a new filter view. You can then select the columns you want to filter as well as how you want to show your data. Filtering will remove from view any records that don’t match the conditions you’ve set.