How to Create a Google Sheets CRM in 7 Steps (+ Free Template)

Customer relationship management (CRM) software helps sales teams keep track of leads, contacts, and opportunities. While dedicated CRM software includes added functionality, you can easily set up a Google Sheets CRM to keep track of the most important information if you are just starting out. We’ll show you how in just seven simple steps.

Of course, if you want to do more than track records in Google Sheets, Salesforce Essentials is a CRM featuring an easy-to-use interface, intuitive mobile app and a 360-view of every deal and contact. This small business-optimized CRM is $25 per user, per month, and you can try it free for 14 days.

Visit Salesforce

Free CRM Spreadsheet Template

Setting up the Google Sheets CRM template to keep track of leads, contacts, and sales opportunities doesn’t have to be complicated or scary. In addition to walking you through the steps to set up your own below, we’ve included a free template that you can use to get started managing contacts and deals. We created this template in Google Sheets, but you can easily follow the same steps to create one with similar functionality in Excel.

Download the Free CRM Spreadsheet Template

Free CRM spreadsheet template

To set up your CRM in a spreadsheet, follow these seven steps:

1. Define Your Sales Process

Before you start creating a CRM using a spreadsheet, take some time to define your sales process, including what you want to track, and decide what information is important to include about your deals and contacts. It will save you time and effort later if you have already defined your process and planned out how your contacts will move through your sales pipeline.

A few aspects of the process you should consider include:

Decide How Contacts Should Be Tracked

Create a sheet to track contacts, which can be either leads or customers. Some CRM software provides the ability to keep track each of these separately, which can be helpful in sorting out who is a customer, who is a partner, and who is still in the lead process. For our Google Sheets CRM template, it’s easier to simply use a column to indicate the type of contact than copying and pasting.

Consider How Deals Will Move Through Your Process

Deals are the sales opportunities that you are trying to close. I recommend that you create a separate sheet within your spreadsheet workbook to track the progress of deals. Consider how you move deals through your sales process, and how you identify the different stages. A Google Sheets spreadsheet is helpful in tracking a deal’s progress, but there are also several simple CRM systems that can save you the time and aggravation of setting up a system yourself.

Specify Your Pipeline Process Stages

For both leads and deals, you should consider the stages in your sales process that you will track in your CRM. These are the various steps in your pipeline that help move a lead from initial discovery through a deal’s closing.

For example, you might use the following stages for your leads and deals:

• Incoming inquiry: This is the stage when you first receive an inquiry from a potential customer. Maybe they’ve filled out a form on a website or sent you an email. You don’t yet know if they’re a good fit, but they’ve shown some interest.
• Qualified lead: Use this stage to represent leads that best match your target customer, and are most likely to become customers. You have additional information, either from direct contact or because the customer has moved through your marketing funnel.
• Consultation: This represents the point when you have a sales-focused needs assessment to determine the specific needs that your lead has, and whether or not you’ll be able to meet them with your product or service offering.
• Proposal: This is the stage where you’ve created and presented a sales proposal to your potential customer, based on the needs assessment that you’ve conducted.
• Contract: Use this stage to indicate when your contacts have accepted your proposal and you are negotiating and preparing a contract for the business.
• Won or lost: This is usually the final stage of a sales process, and indicates whether or not a lead has become a customer.

Identify Potential Lead Sources

You should also use your spreadsheet to keep track of where your leads come from so that you can make informed decisions about where to invest your future marketing efforts. Indicating whether a lead or sales opportunity came from a personal referral, online advertisement, or lead generation campaign can help you track and monitor your marketing efforts and evaluate which efforts are most effective at generating new business.

2. Choose Your Spreadsheet Software

One important factor to consider is which spreadsheet software to use. It may not seem like a big deal, but the spreadsheet you choose really matters, especially if you need sharing features or advanced formatting. Two of the most popular spreadsheet offerings are Google Spreadsheets and Microsoft Excel, but there is also Apple’s Numbers for Mac users. You can check out our article on G Suite vs Office 365 for more information on how the two compare.

For this how-to guide, we’ll be using Google Sheets to represent each of the steps since it can be used at no cost by anyone with a Gmail address. If you’re using Excel, the steps are the same, and you can simply adjust them to fit minor Excel differences in nomenclature. In addition, if you’re an Excel user, you can simply click on the template link above and download it as an Excel file.

Download the template for Google Sheets or Microsoft Excel

Google Sheets

Google Sheets is a part of the G Suite collection of browser-based productivity software, and is available free for personal use by anyone with a Gmail account. One of the strongest features of Sheets is real-time collaboration and sharing functionality, allowing multiple users to view and edit the same document at the same time. This is ideal for teams that want to create a central CRM via a shared spreadsheet that is easily accessible for all users on all of their devices.

Microsoft Excel

Excel is by far the most used spreadsheet software by businesses for everything from accounting reports to human resource (HR) management. It’s available as a desktop application for Windows and Mac computers, as well as on mobile devices. It is also available as a browser-based app called Microsoft Excel Online. Using Excel to build your spreadsheet allows you to take advantage of some of the advanced features like data visualization tools and 2D map charts.

Numbers

Another option for Mac users to consider is Numbers from Apple. Numbers is available as a desktop, iPad, and iPhone application as well as browser-based software, and it includes real-time cloud-based sharing features via iCloud. This allows users to keep their spreadsheets in sync across devices and among shared users. Numbers is easy to use and features many of the same advanced calculations as Excel, but it’s only available for MacOS and iOS.

3. Create Your Spreadsheet

The next task is to get your spreadsheet set up and ready to configure for use as a CRM. We’ll walk through the steps that will get you ready to start adding contact information in a minute, but before we do that, there are a few housekeeping items that will make using your spreadsheet easier.

Decide Where You’ll Access Data

One of the first choices you’ll need to make is whether you’ll primarily work with your file on your desktop or if you’ll want to be able to access it anywhere, such as a remote office location or your smartphone. The choice of which is best depends largely on whether or not you’ll be sharing your CRM with other team members or working on it alone.

Excel is offered as a standalone desktop application as well as a mobile and browser-based app via an Office 365 subscription. Google Sheets, on the other hand, is only available via the cloud. If you’ll be sharing access with others, you’ll find that the online version of Excel or Google Sheets is a better choice.

Create Your Sheets

Most spreadsheet software applications allow you to create multiple workbook sheets (not to be confused with Google Sheets, the program) within one file. This is especially helpful for creating a CRM in a spreadsheet since you can utilize different sheets for tracking contacts and deals. We recommend setting up two sheets in your workbook to keep track of your contacts and deals separately.

Use multiple sheets to keep data separate

Sheet Number 1: Contacts

Your “contacts” are the people you interact and communicate with. They may be new leads, existing customers, partners, or even referral sources. You’ll use this sheet to keep track of their information as well as make notes about your interactions and conversations.

Sheet Number 2: Deals

Your “deals” sheet is used to keep track of sales opportunities, which represent specific deals you are trying to close and which are moving through your sales process. Some industries, like cloud-based software as a service (SaaS), may not necessarily track deals, but rather move leads through a lead nurturing process to close the sale when they sign up. Many industries, however, sell either products or services (or both) and need a way to track their sales process.

Specify Sharing Preferences

Depending on whether you’re working on this CRM alone or if you’ll be using it with a team, you’ll want to set preferences for sharing. One of the benefits of Google Sheets and Microsoft Excel Online is that multiple team members can work on the same file in real-time, making collaboration easy and intuitive. This ensures that all of your contact information is up to date, giving your sales team members the most current information to work with.

In Google Sheets, sharing is as simple as clicking the blue “Share” button at the top right of the interface. You can either give specific individuals file access, or, if you are a business G Suite customer, you can allow any individuals in your organization to interact with the file provided they share the same domain on their email and user profile.

Specifying sharing preferences

Set Security & User Permissions

Since you’ll be storing the personal and business information of your contacts, security is an important consideration. Both G Suite and Office 365 use 128-bit automatic encryption to safely store customer data and HTTPS to transmit data to and from the server. Both are also HIPAA compliant when using browser-based or mobile app versions. In addition, the sharing features of both allow you to restrict access to specific individuals.

4. Name Your Column Headings

Now that you’ve set up your spreadsheet file, you need to create headers for each column. You’ll use columns for the different types of information you want to keep track of for each contact and deal. The rows in your spreadsheet will hold individual records. One of the reasons to give thought to the headers you’ll use is that each represents a data point that you’ll be able to use later to sort, search, and find specific records.

Contact Sheet Headers

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. This list will help make sure you are capturing the information you need to connect with your leads and customers and move them through your sales process.

The headers we’ve used in our template are as follows:

• Contact: Each deal should be associated with a person, whether a lead or an existing customer.
• Email: This should be the email for the primary contact.
• Phone: This should be the main, or primary phone number for the contact.
• Company: Use this field for the company or account that the deal is associated with.
• Title: It’s often helpful to know where your contact fits in their overall organization, especially when you need to know whether they’re likely to be a decision-maker or not.
• Address: This is where you’ll keep track of the mailing address of your contact.
• Contact Type: Since you can use this sheet to keep track of several different types of individuals you communicate with, you’ll want to be sure to indicate 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.
• Source: Knowing where your new customers come from helps you make better sales and marketing decisions, so use this field to keep track of where each contact came from.
• 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. This ensures that you’ll always have up-to-date information the next time you, or someone else on your team, communicates with them.
• Owner: Use this field to indicate who is responsible for this deal.

Name your contact sheet headers

Deal Sheet Headers

Similar to the “contact headers” above, we recommend using the following headers for your deal sheet. As before, feel free to add additional headers based on the information you need to move deals through your sales process, but these should make sure you capture and track the most important information.

We’ve used these in the template found above:

• Deal Name: This is what you will call the deal (for example, “Website for Bob’s Cleaning Service”). 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.
• 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. In the example I mentioned, the category might be “website design.”
• 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.
• Contact: Each deal should be associated with a person, whether a lead or an existing customer.
• Email: This should be the email for the primary contact.
• Phone: This should be the phone number for the primary contact.
• Company: Use this field for the company or account that the deal is associated with.
• Source: Knowing where your new customers come from helps you make better sales and marketing decisions, so use this field to keep track of where each deal came from.
• 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.
• Last Contact: Whenever you interact with a contact for a deal, update this field so that you can later see which deals need your attention based on how long it’s been since you’ve been in touch.
• Notes: When you talk with a contact about a deal, make notes here about the conversation, progress, and any action steps needed. This makes sure that you’ll always have up-to-date information the next time you, or someone else on your team, communicates with them.
• Owner: Use this field to indicate who is responsible for this deal.

Name your deal sheet headers

5. Import Your Existing Data

Regardless of what you’ve been using to keep track of your contacts up until now, you’ll want to move all of that contact information over to your new spreadsheet so that you can start using it to close more deals and convert more leads into customers. There are several ways to do this depending on where your contact information is currently stored.

Here are a few of the most common ways to import your information:

Manually Enter Data

Depending on how many contacts you have, one of the simplest ways to populate your new CRM spreadsheet is to enter your contact information into your spreadsheet manually. You’ll simply enter one contact record, or deal, for each row of the spreadsheet. If you only have a few contacts so far, this is a manageable way to get your information into the CRM with a minimal amount of work.

Import From Email

Most likely, up until now, you’ve kept track of all of your contact communications using your email. This works fine when you only have a small number of contacts or deals, but can get overwhelming as that number grows, which is probably why you’re looking for a better solution. Many CRMs offer direct integrations into Outlook or Gmail, but for this guide, we want to talk about how to get that information out of your inbox and into your brand-new spreadsheet.

If you use Gmail, you can simply export your contacts into a CSV file, open that file in Excel or Google Sheets, and then copy the records into your new spreadsheet. For many users, this may be the easiest option, but if you have a large number of contacts, the good news is that there are ways to make the process more automated.

For example, Mailparser allows you to export emails and it will translate them to your spreadsheet. It’s a paid service, but they have a 14-day free trial, during which time you can export a batch of files and send them to your new spreadsheet.

Import From Other Software

If you previously have been using some type of contact management tool like an address book or your 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 if you’re using tools like Mailchimp or Constant Contact and want to move your contacts to a CRM spreadsheet.

Pro tip: If you’re currently using one of these tools, it is sometimes easier to do this step first and export your contacts to a CSV. Then open that file in Google Sheets or Excel and modify the resulting spreadsheet to match the format described above. That way, you’ll have the contact information already in your CRM and you can then add and move columns as needed.

6. Create Automations & Integrations

One of the powerful benefits of spreadsheet software is that you can use it to not only store information, but also to interact with your data. This transforms your CRM from a list of names into a tool that you can use to stay on top of your sales process and win more new business.

Here are a few of the ways you can set up your CRM spreadsheet to make it work for you:

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.

Adding conditional formatting to your sheets

However, if you want to know how to do it on your own, follow these simple steps:

1. Select the cell, row, or column you want to format. For your CRM, you’ll most likely want to select a column, since all the information in each column is a specific type of information.

2. Right-click on your selection, or click on the “Format” menu and select “Conditional Formatting.”

3. You’ll then be given a list of the rules applied to your selection, as well as the option to “Add New Rule.”

4. Next, in the field called “apply to range,” you should see your selection. If not, simply add the range of cells to which you want to add formatting.

5. In the “Format Cells if…” field, choose the condition that needs to be met for formatting to apply. This could be related to a date or other value. You can even add a condition for a field that is blank or if it contains a certain type of text.

6. Finally, choose the formatting that should apply if the condition you applied is met. You might change the color, font, or style in a cell.

Use Data Validation Lists

Another helpful type of automation is to add data validation, which means that 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. The setup process is similar to adding conditional formatting and helps keep your CRM data clean and organized, so you can more easily sort and act on your contact information.

Data validation in Google Sheets

To add data validation in Google Sheets, simply follow these steps:

1. Select the cell, row, or column to which you want to add data validation. Again, you’ll most likely want to select a column, since all the information in each column is a specific type of information.

2. Right-click on your selection or click on the “Data” menu, and select “Data Validation.”

3. The selection you’ve chosen should be indicated in the “Cell Range” field. If not, you can add it manually.

4. Choose the type of validation you want to use. We’ve chosen a “list of items.”

5. Enter the list you want to use for your drop-down menu, with each term separated by a comma.

6. To make sure that only your list of terms can be used, select the “Reject Input” option in the “On Invalid Data” field.

7. Save your changes.

Integrate With Other Tools

Once you have your CRM set up, you can use third-party automation tools to help you keep your CRM data up to date as you generate new leads. For example, if you use a lead generation tool like Mailchimp, Zapier will send new subscribers who fill out your subscription form on your website directly to your spreadsheet as new rows. Zapier actually makes this easy, allowing you to map fields directly from the form to the appropriate fields in your CRM spreadsheet.

Mailchimp to Google Sheets integration with Zapier

7. Generate Reports

Using a spreadsheet for a CRM allows you to keep your information organized and lets you create basic reporting using the features mentioned already as well as other functionality within Google Sheets and Excel. Now we’ll talk about a few of the ways you can use your data to make the sales process easier by giving you reporting and insights into your contacts.

Sort by Columns

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

Using filter views in Google Sheets

Create Filters

Filtering is an extremely powerful tool that allows you to create individual views 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%. By using filtered views, you can generate reports that give you insight into your contacts that you can act on.

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.

Frequently Asked Questions (FAQs)

Which spreadsheet tool should I use?

The specific spreadsheet that is best for your business depends on several factors. If sharing and real-time collaboration are most important, Google Sheets is the best choice. If PC compatibility and advanced productivity features matter most, Microsoft Excel is the best choice.

When should I move to a CRM?

The decision to take the plunge and sign up for a full-featured CRM depends on a lot of factors. However, generally speaking, if you are managing more than 50 contacts or more than five deals at a time, it probably makes sense to start looking at a CRM-specific software. If you’re on a tight budget, several CRMs offer forever-free plans.

What’s the difference between a spreadsheet & database software?

A spreadsheet is a collection of records organized in rows and columns of cells that can be sorted and filtered. In addition, spreadsheets allow calculations and other formatting to be applied to either a single cell or a range of cells. Database software also maintains a collection of information, but the primary difference is that they are relational—meaning that you can connect different types of data to establish a relationship between them.

As an example, you could have a contact in your database and connect that record to a sales opportunity or an account. The database keeps track of the relationship and keeps the data connected behind the scenes. Spreadsheets allow some ability to connect cells and sheets, but are limited in their ability to establish relationships between different types of records.

Bottom Line: Google Sheets as a CRM

For businesses looking to take control of their contacts and close more business, creating a CRM in a spreadsheet can help streamline their sales communication efforts. While spreadsheet software like Google Sheets lack the advanced features found in dedicated CRM software for small business, it can be a low-cost, no-frills way to move away from the inbox and toward a more productive sales process.

For businesses ready to make the leap to dedicated CRM software, we recommend Salesforce Essentials, as it offers contact and deal management, email tracking, and customer support features for just $25 per user, per month. Visit their website today to start your 14-day trial.

Visit Salesforce