How to use Google Spreadsheet CRM templates

A customer relationship management (CRM) platform gives you a lot of extra power in your business. Thanks to automation, data tracking and collaboration, you can track everything from current customer data to sales opportunities.

But if you’re a smaller business, it doesn’t always make sense to pull the trigger on a tool or app for your business. You don’t want a complex, enterprise-level CRM system that’s impossible to use without hiring a consultant. That’s why it’s not unusual for solopreneurs and freelancers to create CRM templates of their own using Google Sheets.

Of course, Google Sheets is designed to be a spreadsheet tool, just like Excel spreadsheets—not a CRM. You won’t be able to track everything with this method, but you can definitely monitor a handful of leads, contacts and opportunities.

Curious how businesses use Google Sheets as CRM templates? While Google Sheets might not have enough firepower for many situations, you can use it in a pinch while your business is growing. Learn which businesses get the most value out of Google Sheets and 5 steps to quickly set up a Google CRM template.

Can you imagine a gigantic corporation like Target using Google Sheets to track their customers? They have way too much data; it’s just not going to happen.

Google Sheets is a very limited solution that will only work for businesses with a small number of contact records. It’s ideal for small businesses, freelancers and solopreneurs who need to track information but don’t want to go all-in on a CRM solution just yet.

A Google Sheet CRM or CRM spreadsheet might be a good fit for you if:

• You need simple, basic data tracking on a handful of customers, leads or vendors
• You either operate alone or with a very small team (2 other people, max)
• You’re a solopreneur who needs to differentiate your income streams (revenue from coaching services versus revenue from a course)

We cringe at the thought of a bigger business using Google Sheets for everything, but if you’re super small right now, you can get by with a Google Sheets CRM template.

Of course, that doesn’t mean plugging your CRM data into a Google Sheet is the best idea. More often than not, you’ll need to go with a proper CRM if:

• You have a large business or a large volume of data: Do you have a lot of contact records or customer data? A Google Sheet CRM template will become a living nightmare if you fill it with too much data. The CRM spreadsheet can even become glitchy and slow if you put too much data in it, which probably isn’t what you want.
• You have more than 2 salespeople: Google Sheets works fine if it’s just you and maybe two other employees on the sales team, but it gets messy and confusing for larger teams. If you give everyone edit access to the Google sheet, we guarantee someone will accidentally delete important data. Plus, if you’re using Google Sheets as a sales CRM template to calculate your team’s sales commissions, it’s not really fair to them because you can’t see the total sum of their efforts in a simple sheet.
• You want the power of third-party apps: Google connects with plenty of services, but Google Sheets won’t talk to QuickBooks, Asana or other third-party applications the way a CRM will. This means you’ll do more manual data entry and waste more time without the benefit of third-party automation.
• You need accountability: A CRM system gives you the freedom to assign tasks based on entries or custom fields in your database. With a Google Sheet, you have to filter through the data, read notes, and manually take action. This can really slow you down if you have a lot of leads.
• You’re losing data (or customers): With manual entry in tools like Google Sheets, it’s impossible to track everything. Since you can’t see all of your customer touchpoints and contacts at once, there’s a good chance you’ll send incorrect or irrelevant messages to the wrong customers. That’s a recipe for losing business.

Sure, a Google Sheet CRM means you can manage contacts more quickly than you could if they were in your email inbox. It’s fine for where you are right now, but your business probably won’t stay at this level forever. Google Sheets is better used as a CRM spreadsheet for small, simple businesses.

If you really want to future-proof your business, it’s a good idea to go with a lightweight CRM like Copper from the start. That way, you can avoid the hassles of importing your Google Sheet to a CRM later.

If you’re confident a Google Sheets CRM is right for your business, here’s how you can make a robust and useful Google Sheets CRM template.

1. Choose the data you’ll store

What data will you track in your Google Sheet? Google Sheets is more unwieldy than a real CRM, so upfront planning is a good idea. It’s impossible for you to track absolutely everything because it will make your spreadsheets messy and unusable, so you need to set parameters before you start.

We’ve added a few suggestions here to get you started, but remember to customize the data you collect so it fits your sales process. For example, if you have to go through several stages in your sales pipeline to close on a deal, or your leads come from multiple sources, add them! Every business is different and you should customize your Google CRM template to how you do business.

Keep your CRM spreadsheets as simple as possible. We recommend creating three tabs to track all of your data: contacts, deals and sales cycle overview.

2. Create tab #1: contacts

The first tab in your Google CRM template will track your contact information, including customer data. Add column headers to this CRM template to encompass a contact record, like:

• Name
• Company name
• Contact type (lead, referral, existing customer, vendor, etc.)
• Address
• Email
• Phone number
• Source (where you connected with them)
• Notes

This will enable you to track details about current customers, past customers, vendors and more.

Be sure to keep the Notes section as up-to-date as possible. If you’re collaborating with a sales team, you might want to add an “Owner” column to note which people on your team are responsible for each contact.

3. Create tab #2: leads

The second tab in the Google Sheet CRM should be for tracking deals in the sales pipeline. This gives you an overview of all the opportunities you have coming up, tracking every touchpoint you have with the leads and where they are in the sales funnel. Basically, if you want to know which deals are coming up and how likely you are to close them, you need to track everything in this tab.

We recommend adding these headers to make the most out of your lead tracking tab:

• Lead name
• Lead email
• Lead company
• Contact date
• Follow-up date
• Lead status (cold or warm, interested or not interested, etc.)

For lead status, it’s a good idea to format this column as a dropdown. You don’t want your sales reps to get creative with the language in the fields here; that will make reports and forecasting a huge headache.

4. Create tab #3: sales cycle overview

The final tab in your Google CRM template is your sales cycle overview. This will give you an idea of the revenue you can expect to earn over a period of time. It also makes it easy to see, at a high level, whether you’re winning or losing a lot of opportunities and need to go back to the drawing board.

This will change depending on your specific sales process, but we recommend adding headers like:

• Deal name
• Description
• Deal value
• Likelihood of closing (expressed as a percentage)
• Deal stage (follow up, qualified, unqualified, demo, negotiating, won/lost)

5. Set up formulas

Creating your tabs and dropping your data into each field is the bulk of the work, but there’s one final step to creating a Google Sheet CRM: formulas. The great thing about Google Sheets is that it’s designed to store and process data. If you want to accurately calculate important figures, like revenue projections, you need formulas in place to make it all happen automatically.

You might want to create formulas for:

• Setting up conditional formatting, like turning a cell green after you’ve contacted a lead.
• Restricting data to certain numbers, characters or options so your list stays clean.
• Referencing other data, like pulling deal values from your sales pipeline and into your sales projections.

P.S. Here’s a comprehensive list of Google Sheets formulas from Google if you want to choose a few that work for your biz.

The purpose of a CRM system is to reliably store your data and make it more actionable. With a Google Sheets CRM template, you’re pulling your data into one place, but it isn’t the most efficient way to run your business.

If the idea of a massive enterprise CRM sounds awful, know that all CRM solutions aren’t created the same: you don’t need enterprise-grade tools if you’re a small or midsize business.

If you’re already a fan of Google, Copper is a lightweight CRM that integrates with Google in just a few clicks. Copper makes it easy to transition away from Google Sheets and into a CRM that won’t overwhelm your team (or your wallet).

Your business depends on lasting customer relationships. Form connections that matter with Copper, the only Google-recommended CRM. Give it a test drive now with a 14-day free trial.