Tracking sales is an essential part of most businesses. Effectively recording your data helps to build accurate forecasts to make sensible plans. It also lets you stay on top of your inventory and tax requirements.
But, sales tracking software is often costly. A cost that many beginning entrepreneurial endeavors can’t afford. The answer to this issue is to use a Google Sheets sales template. In this guide, we will cover how to make your own from scratch and provide a few templates. Read on to learn how to build a spreadsheet sales template.
Our Google Sheets Sales Templates
Why Build a Sales Report?
A sale report collects specified information from a CRM like Salesforce, HubSpot, and other related applications. Your sales team’s performance is evaluated using the data, which you can also utilize to assist teams and managers in making appropriate adjustments in their project management plans to boost sales.
A sales report demonstrates the effectiveness of your sales staff so that you may improve the sales cycles, tactics, and business projects. You don’t have to add all sales metrics to your activity reports to get valuable data and insights. Rather than overwhelming massive data dumps, focus on essential data points. When building your spreadsheet, consider what you need to collect and why.
You can use your CRM software and platforms where the sales data is gathered and stored to build weekly or monthly reporting statistics. Your sales team and its reps can use the roadmap given by a sales report that emphasizes the most important factors that will improve performance and continue with ongoing analysis by updating the reports.
Factors to Consider When Creating a Sales Tracker Template
When constructing a sales tracker template, it’s essential to make an outline and add some of the following factors that you deem to be most valuable to your business:
- The data you need to measure: Choose the KPIs that you wish to be more visible. To avoid overwhelming your sales agents with statistics that can impact their sales success, keep it straightforward and concentrate on the most critical data.
- Target audience: To choose the most relevant data and best formatting, decide who will benefit from receiving your sales report information. For instance, some sales teams use a sales report to inform company leaders of sales performance, while others use it internally, especially for reps, to track and enhance performance.
- Context: Give context and background information along with your statistics to convey the whole picture. For example, you could include context describing the spike (or dip) in sales for your target market in a particular location for a holiday period. That way, your team leaders don’t set unrealistic goals for non-holiday periods.
Google Sheets Sales Tracker Key Performance Indicators
Here are some essential KPIs to consider when building a sales tracker:
- Sales Calls: Sales representatives should report the results of their weekly sales calls, including the number of calls made, connections made, voicemails left, and prospects who did not answer.
- Overall Pipeline Production: Depending on meetings each sales rep has held each week and thus far this month, this estimates the pipeline creation rate for your team.
- Overall Deal Creation: Sales representatives outline the daily transactions they produce while keeping track of the total deals made and the week and month-end totals from the sales report with the dates.
- Work Rate: The work rate tracks the number of distinct potential customers each sales representative interacts with throughout a month, assuming you have many sales teams attempting to meet monthly targets.
- Lead Reaction Period: This monitors how long it takes for leads to reply after being contacted by a rep. You can better comprehend how variations in cycle time may impact your sales plan.
- Closed Won Deals: This keeps track of your successes, including the volume of closed transactions and the revenue generated by your sales force. By reviewing this data, your team may identify successes, congratulate them, maintain momentum, and get insightful knowledge about sales strategies that work and don’t.
Related Reading: Google Sheets Ledger Template
Reasons to Use a Google Sheets Sales Template
Teams of any size can use Google Sheets for sales templates. You’ll gain a lot from using them, whether you’re a startup company or an established enterprise firm.
They can support other tools, even if you don’t use them as your primary tool for doing business. You might, for instance, use a free CRM to keep track of customer data before using a Google Sheets invoice template to generate bills and invoices for each client quickly.
A Sales Tracker for Google Sheets Is Free
The Google Sheets templates can be used for any purpose and are free. This saves you from paying a ton for a subscription for another tool or Excel enterprise costs. Just ask everyone to create a Google Workspace account, so they can access the sales templates on Google Sheets.
Sales Tracking With Google Sheets Is Simple
You don’t need excellent spreadsheet skills to use a Google Sheets sales template. The fields and calculations are already included in the templates. If they’re already present, you don’t need to create complex formulas and functions. The document will generate a report or compute a total once your team enters the numbers.
A Sales Template for Google Sheets Makes Teamwork Easy
Your team may collaborate using Google Sheets templates with little gatekeeping. They will have access to the file when you click the “Share” button in the top right corner of the document. You can also restrict the feature of collaboration to only viewing or commenting. This is a smart choice for private documents that a select group should only edit.
It’s Easy to Customize a Spreadsheet for Sales
Even when using a template, you’re not forced to use its style. With Google Sheets’ built-in tools, you can simply alter the fields, colors, and fonts. Once you do, the template will seem exclusive to your company.
No Data Loss
It is difficult to lose data when using Google Sheets templates. Google Sheets has a version history feature that allows you to return to a prior document version if your sales template is damaged.
All modifications are saved in the cloud. If you lose your internet connection, Google Sheets just freezes the copy until you can reaccess it. Google Sheets also freezes the copy if your laptop breaks down. You can access the copy on another device easily. Besides that, you should still take certain safety measures, like enabling offline editing and downloading a file copy to your local storage.
How to Create a Google Sheets Sales Tracker Template
If you’re looking for an easy way to create and use a template for sales tracking in spreadsheet, you can use one from Google’s template gallery. However, if you wish to create one that suits your needs, you may need to create one from scratch.
The Google Sheet sales template you create for your business depends on your needs, which is why your sales tracker may vastly vary from the one we create in this example. For this example, we will create a simple quarterly tracker containing a few charts to visualize the sales. Feel free to add or remove elements.
Entering Titles and Formatting the Sheet
Before we start entering sales data, we need to add the categories to the spreadsheet. This can be done manually by entering the data into the spreadsheet. Alternatively, you can also link your CRM software for integration. The data will then be automatically added to your spreadsheets.
For this example, we are going to presume you don’t have such software and are entering your data manually.
We will be creating a yearly sales data template for this spreadsheet. To do this, we must create four sections representing all four quarters in a year. Dividing this requires careful planning to determine how many sales you need to add to each section.
We are going to assign ten cells for each quarter. To do this, select the cells in column A, then click on Format, and then on Merge cells. There, click on Merge all. Do this for all four quarters.
You’ll notice that we also horizontally merged the OPPTY, ECONOMICS and PIPELINE subheading cells. You can also do this.
Once the data has been added, you can add borders and colors to the cells by selecting the cells and then clicking on the Borders icon in the toolbar and selecting the type of border you wish to apply to the cells. To add color to the cells, click on the cell and then on the Fill color icon. There, select your desired colors for the cells.
To calculate the totals for a lot of the sales data in your spreadsheet, you will have to utilize formulas in some of the cells. The formula we will use to add up the values in the cells is SUM. Here is the syntax for the formula:
=SUM(data1, [data2, ...])
We will add the cell references for the cells for the Size of Deal from Q1 to Q4 to forecast the sales for the entire year.
Let’s take a look at the steps you need to follow to do this:
- Enter the initial part of the formula into the cell. In this case, we will write =SUM(
- We are going to add a total of four parameters here. The first is the cells containing the data for the Size of Deal column. The cell range we are going to add here is E3:E10.
- Now, add a Comma ( , ) to separate the parameters.
- Now add the other cell references. These are E14:E22,E26:E34,E38:E46. Make sure to use commas to separate the cell references.
- Finally, add a Closing bracket “)” to finish the formula.
You can repeat this formula in the Total Projected Revenue cell by taking the cell references from the weighted forecast section and using the SUM function.
You may also want to add quarterly totals using a similar process. Customizing the template depends on the needs of your business.
Add Visual Elements
To make your spreadsheet look a bit more interesting, you can add charts and graphs to your Google Sheets sales tracker. Thankfully Google Sheets allows you to easily add some with just a few clicks. In this example, we will create a line graph to compare the performance in the four quarters.
Here are the steps you need to follow to add a chart to your Google Sheets sales dashboard template:
- Click on Insert in the main top bar of the spreadsheet.
- In the dropdown menu that shows up, click on Chart. This will add a blank chart to your spreadsheet and open the chart editor.
- First, we must select the chart type we wish to add. To do this, click on Chart type and then Smooth line chart.
- Now, we need to add the data ranges to the chart. To do this, click on the textbox under Data range.
- Now add the data ranges for all four quarters separated using a comma. In this example, it will be
Although the fundamental part of your chart is complete, you can still customize it to make it look the way you want it to. To do this, head to the Customize section in the Chart editor.
Building a Dashboard for the Template
If you want to have the charts in a separate sheet to make a dashboard, you can easily do this, just:
- Add a new sheet using the + symbol on the bottom left of the screen
- In the new page, select an empty cell then follow steps 1-5 as above, except add the Sheet! Locator before each cell reference, like so:
This lets Sheets know it has to search the first page for the data.
You could also add appropriate titles and sections using formatting, as shown in the first section.
Related Reading: Building an OKR Template in Google Sheets
Frequently Asked Questions
How Do I Create a Sales Spreadsheet in Google Sheets?
To create a Google Sheet sales tracker spreadsheet, first, you will need to title the columns that will contain your data. You can also add colors, borders, and fonts to better visualize the data. Then, add formulas into specific cells to perform calculations. Usually, the SUM function is enough to collect totals. Finally, add charts to finish up your sales dashboard template.
Can I Use Google Sheets as a CRM?
Currently, Google does not have its own Customer Relationship Management (CRM) platform. However, because of the flexibility Google Sheets provides, you can create a similar experience by making a sales pipeline template Google Sheets. This will help you keep track of ongoing deals and manage contacts. These spreadsheets are customizable, so you can make one that suits your business needs. Many CRM services also provide their CRM templates for Sheets.
How Do You Automate Sales Reports?
You can automate your data capture in the spreadsheet using Google Sheets Apps Script. Although this requires some coding knowledge, you can automate almost everything in the spreadsheet and even process and email the final report and the end of every month.
Making the Perfect Template
As we mentioned, one of the strong points of using a Google Sheets sales template is how customizable they are. You may want to use one of our premade templates, but make sure you add or take away the right parts to make it the perfect fit for your business.
- Free Mileage Log Template for Google Sheets
- Independent Contractor Expenses Spreadsheet [Free]
- 2 Free Crypto Portfolio Tracker Google Sheets Templates
- Free Google Sheets Profit and Loss Template
- The Free Google Sheets Task List Template [Easy Guide]
- Free Social Media Calendar Template in Google Sheets
- 5 Useful Google Sheets Project Management Templates
- Free Google Sheets Inventory Template