Best Google Sheets CRM Template & How to Use It in 2023
This article is part of a larger series on CRM.
Customer relationship management (CRM) software helps sales teams 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. Follow our step-by-step guide on how to use Google Sheets as your CRM and start managing and organizing customer records, interactions, and sales.
Free CRM Spreadsheet Template
The quickest way to set up a Google Sheets CRM for tracking leads, contacts, and sales opportunities is by downloading our free template. It consists of two spreadsheets that make a workbook you can use as a basic CRM tool. These spreadsheets include contact information, deal status, and a sales overview.
Creating a CRM using Google spreadsheets is simple—especially if you’re familiar with using Google Sheets or Microsoft Excel. Follow these steps to get started and check out our bonus tips at the end of the article on how to maximize your new Google Sheets CRM.
1. Identify Information & Data to Be Processed in the CRM
The first step in setting up your CRM in Google Sheets is to decide what types of customer data you want to track. You must also determine what data to extract from your deals and contacts. Identifying this information upfront saves time and effort later. It’s more challenging to make workflow changes down the road without the advanced data management tools 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 onset, you don’t want to give yourself more work than necessary.
In our example, we’ll track only 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 two separate tabs in your CRM for your Google Sheets workbook, including contacts, deals, and your sales overview.
The Deals tab is where all the essential information of your contacts and deals are saved. These include company names, project names, and contact details, such as email addresses and phone numbers. There’s no easy way in Google Sheets to segment your columns. Hence, your best option is to create a drop-down menu for that column of data. We’ll discuss more on how you can do this in the fourth step.
The Deals tab also includes sales opportunities you’re trying to close, so carefully track their progress through each deal stage. In the sheet, we included columns for the deal stage, status, and value. This is where you can indicate whether a deal is won or lost or which deal stage it currently is in so it can auto-calculate results in the Sales Dashboard tab.
While it doesn’t have to strictly follow all the stages of your sales pipeline, the deal stages should reflect steps in your organization’s pipeline—especially those that require an action step from either the agent or the lead.
The sales overview tab is a quick snapshot of your expected sales in a specified time period. It shows the value of all deal stages—from follow-up 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. It also shows the assigned deals for each agent and how much revenue they can potentially bring in.
3. Name Your Column Headings or Sections Efficiently
Once you’ve set up your CRM spreadsheet, 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.
We predetermined column names in our free template, though you can modify it to add any additional types of information you need to track. 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.
This sheet should include basic contact information and a few other data points, such as:
- Contact details: Each deal should be associated with a company, corresponding contact, and their contact information, including company address, mobile, and email.
- Agent: Track the deals your agents are working on by adding this in the columns.
- Deal stage: Have easy visibility on which stage each deal is by predetermining deal stages.
- Deal value: Indicate estimated deal value so it’s easy for you to calculate projected revenues.
- Deal status: Track whether a deal is won or lost.
This provides a quick overview of your sales projection for a specified time frame and is mostly based on values inputted under the Deals sheet. Our CRM on the Google Sheets template also includes graphs beside each section, which you can use for visual reports.
- Total opportunities: Auto-calculates the number of deals in your Deals tab
- Deal stage: Lets you see how many deals there are in each stage.
- Agent assignments: Auto-calculates the value of deals each agent is handling.
- Deal value: 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 revenues for each month and deal stages.
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 in a Google Sheets system. Below are some samples of simple functions that you can add to your spreadsheets:
Consider using data validation to ensure only certain types of data are added to a cell or range. In the sample template, we used this feature in the “Deal Stage” and “Status” 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.
You can reference data from other cells to bring together the most important figures in a summarized format. In the sample template, we referenced deal values, deal stages, and agent assignments in a table in the sales dashboard tab to display the values of the sales outreach. Learn how to reference data from other sheets.
Making the Most of Your Google Sheets CRM
One of the benefits of using Google Sheets as your CRM is its ability to transform simple spreadsheets into an automated tool that is capable of processing business data. Below are other suggestions you can do to maximize the use of your new CRM Google Sheets.
Your CRM data can be used to create simple but visual and informative reports. In our sales dashboard column, we create graphs representing the numbers in the Deals and Sales Dashboard summary tables. By creating graphs in Google Sheets, you can show helpful data like the amount of value each agent brings or how many deals are currently in a particular deal stage.
Share & Centralize the Use of Your CRM
Who Google Sheets Is Good For
Google Sheets is easy to turn into a simple CRM system to keep track of your most crucial customer-related information, but it’s not for everyone. Below are some of the users who benefit best from using it:
- Users looking for a simple system to organize their contacts and deals: If you’re looking for a way to keep all your contacts’ information and basic deal updates organized in one place, storing them in a CRM Google Sheet comes in handy.
- Solo entrepreneurs keeping track of various revenue streams: Solopreneurs who want a simple tracking system of their actual and projected revenues from multiple sources can efficiently use Google Sheets as a CRM alternative.
- 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 Google Sheets Is Not Good For
Below are users who will benefit from using a dedicated CRM system:
- Businesses that handle large volumes of contacts and customer data: If you’re dealing with hundreds of contacts, building a CRM in Google Sheets may result in data duplications due to the high volume of manually inputted data.
- Users who employ many third-party apps: If your company uses a lot of other third-party apps, such as project management and sales automation, you’re better off using dedicated CRM software that seamlessly integrates with other apps.
- Larger sales teams that want to monitor the sales performance of their reps: If you want to monitor the targets and quotas of sales reps, a CRM template for Google Sheets is not an ideal option as you can only do basic automation functions in the platform.
Google Sheets CRM Alternatives
Fortunately, there are many free, robust CRM tools available, such as Freshsales. It has a Growth plan with up to 3 free user allocation and is easy to learn. It also has essential CRM functionalities, such as contact and account management, deal management, CRM reports, and a mobile app. Paid plans are also available when you’re ready to grow and upgrade.
Another great option is HubSpot CRM, which is our top pick for the best CRM software for small businesses. It enables you to create custom pipelines that fit your sales processes and provide an overall view of expected revenue. When you create deals involving saved contacts, all their details, such as contact information and communication history, are automatically filled into the deal.
Utilizing a Google Sheets CRM provides a straightforward way to manage your contacts and leads, as well as monitor your sales progress and incoming revenue. However, it’s essential to remember it is only ideal for handling and processing small amounts of data. Businesses that handle large volumes of customer and business data should consider using dedicated CRM software instead.