February 14, 2024
Written By
Rand Owens
Founding team member at Motive (Formerly KeepTruckin) and passionate about all things Marketing, RevOps, and Go-To-Market. VP of Marketing @ Polymer Search.

How to Create a Google Sheets Sales Dashboard

Google Sheets is equipped with a suite of features for data management, visualization, and analysis—from dynamic charts to smart functions. 

That’s why when it comes to creating sales dashboards, Google Sheets is a staple among sales managers and executives.

Continue reading to learn how to create a Google Sheets sales dashboard in minutes.

Table of Contents

  • What are sales dashboards?
  • Why build sales dashboards in Google Sheets?
  • Key metrics to include in your Google Sheets sales dashboard
  • 3 Ways to create a Google Sheets sales dashboard
  • 1. Create a Google Sheets sales dashboard from scratch
  • 2. Customize a sales dashboard template
  • 3. Use a Business Intelligence (BI) tool
  • See the big picture with Polymer

What are sales dashboards?

Sales dashboards are compilations of visual elements and tables that make in-depth sales data more readable. They provide an overview of Key Performance Indicators (KPIs), like total sales, orders, and leads. You can also use higher-funnel data to look understand key buying signals.

Unlike sales reports, a visual sales dashboard can track sales data in real-time. This is possible through a range of dynamic elements, such as:

  • KPI overview section
  • Data visualizations (e.g., pie charts and bar graphs)
  • Pivot table
  • Lists (e.g., new accounts and top-selling sales reps)

Advanced dashboards also include tools for investigating, sorting, and filtering large data sets. This makes dashboards extremely useful for data-driven decision-making. 

Plenty of eCommerce, sales management, and business reporting software automatically generate visual sales dashboards. You can also use spreadsheet software like Google Sheets to build custom dashboards from scratch. 

Why build sales dashboards in Google Sheets?

Here are the top benefits of learning how to create a Google Sheets dashboard for sales data:

  • Fully customizable. Build a sales dashboard from the ground up to tailor every element to your specific business needs. This ensures every component of your dashboard is built with a purpose (other than adding to visual clutter). 
  • Consolidate data from different sources. Consolidate and manage data from all your selling channels into one dashboard. Thanks to Google Workspace’s popularity, modern Business Intelligence (BI), eCommerce, and data management solutions directly support Google Sheets for data exports. 
  • It’s free. Google Sheets is completely free for all Google users. You don’t need to worry about pricing or subscription tiers—every feature is unlocked right out of the box.
  • Use templates. Choose from hundreds of Google Sheets templates to quickly build a fully-functional sales dashboard. Templates can be opened from the built-in template library or via third-party sources. 
  • Share dashboards with up to 100 users. Google Sheets allow up to 100 users to collaborate on a single spreadsheet. Sales platforms also come with collaboration features for multiple users, but these often come with a hefty price. 

Key metrics to include in your Google Sheets sales dashboard

Before you start building sales dashboards with Google Sheets, it’s important to identify which metrics to track. This is an important step in learning how to organize data in Google Sheets.

Here is a quick roundup of the key metrics you should track in your sales dashboard:

1. Average Revenue Per User (ARPU)

ARPU measures the revenue you make per customer over a given period of time. This is useful for calculating other KPIs and measuring returns, especially when compared against your Customer Acquisition Cost (CAC). 

To calculate ARPU, divide your total revenue over a time period by the number of paying customers within the same period. 

If you made $3,000 in a month with 24 active customers during that period, your ARPU is $125 ($3,000 ÷ 24 customers). 

2. Customer Acquisition Cost (CAC)

Sales dashboards can track the cost of acquiring new customers and how it changes over time. 

CAC lets you evaluate the effectiveness of your marketing and sales teams as well as your customer acquisition strategies. If necessary, it will also help adjust your pricing strategy and secure healthy margins for your business. 

To calculate CAC, divide the combined costs of marketing and sales by the number of new customers. 

Suppose the total cost of your marketing and sales is $1,500. If you acquired 300 new customers within that period, your CAC is $5 ($1,500 ÷ 300 customers). 

3. Monthly Recurring Revenue (MRR)

MRR is one of the most important KPIs in a sales dashboard, especially for service providers and subscription-based businesses. It’s indicative of your overall sales performance for a given month.

To calculate MRR, get your ARPU and multiply it by your total active accounts. 

For example, if your ARPU is $120 for a total of 20 subscribers, your MRR is $2,400 ($120 x 20 subscribers). 

Measure sales growth by comparing your current MRR with that of the previous month’s 

4. Sales target 

Tracking your sales target in your dashboard makes sales performance data more actionable.

For one, it allows you to determine how many new customers you need to meet or exceed revenue goals. It also helps ensure your business is generating enough to cover operational expenses. 

Sales targets can be visualized in different ways. A popular strategy is to track it alongside your current sales revenue in a combo or stacked area chart. 

5. Sales win rate

Sales win rate measures the percentage of sales-qualified leads that converted into paying customers. 

To calculate your win rate, divide your total closed sales by the number of qualified sales opportunities within a specified period.

If you closed 20 deals out of 100 opportunities, your win rate is 20% (20 sales ÷ 100 opportunities). 

Take note that win rate only considers sales opportunities with potential customers at the bottom of your sales funnel. These are prospects who check every box in your sales qualification process and exhibit indications of purchase readiness. 

If you compare your total sales with the number of all leads in your sales prospecting list, you’ll end up with your conversion rate—not your sales win rate. 

6. Year-over-Year (YoY) growth

In a sales dashboard, YoY growth measures changes in revenue over a 12-month period. It helps you look at the big picture and analyze sales performance without monthly volatility. 

To calculate YoY growth, subtract your revenue from the previous year from your current revenue. Divide the difference by the previous year’s revenue and multiply the result by 100. 

Suppose you made $50,000 last year and $70,000 this year. 

First, subtract $50,000 (last year) from $60,000 (current year) and you’ll get $10,000. The next step is to divide it by last year’s revenue, which translates to 0.2 ($10,000 ÷ $50,000). 

Multiply the result by 100 to get your YoY growth rate. With the figures above, your growth rate is 20% (0.2 x 100). 

7. Quota attainment 

Quota attainment helps evaluate the individual performance of your salespeople. This will let you know who among your sales team requires additional coaching or support. 

You can calculate quota attainment by dividing a rep’s sales by their quota for the same period. Multiply the quotient by 100 and display the result as a percentage. 

For example, if a sales representative sold 50 seats with a quota of 40 per month, their quota attainment score is 125% ((50 ÷ 40) x 100).  

Quota attainment can serve other functions outside of tracking the performance of salespeople. 

In some cases, quota attainment can indicate bigger problems in management, sales enablement, branding, and other top-level areas, particularly if the metric trends downward across the board. 

Some sales managers also use quota attainment to create dynamic leaderboards in their dashboards. This can galvanize your sales team to perform better or competitively among their peers. 

8. Market share

Tracking your market share is helpful for forecasting growth and planning long-term goals. This can be measured by dividing your total revenue by the Total Addressable Marketing (TAM) of your industry. 

For example, the office software market is valued at $27.92 billion in 2023. 

If your company’s annual revenue is $120 million, your market share is 0.4%. That’s still a sizeable market share to penetrate. 

Just remember that TAMs are not linear. Most industries grow in value each year, but some macroeconomic events can cause markets to decline over time. 

9. Leads by source

Leads by source is a metric that will help you identify your most effective lead generation channels. It tracks the total number of leads fed into your sales funnel from different lead generation channels, such as landing pages, social media, and display advertising. 

This metric can be visualized as a pie chart, bar graph, or dynamic table. 

Apart from lead acquisition, consider tracking the conversion rate of each lead source. Calculate this by dividing total conversions by the number of leads generated (from a specific lead source) and multiplying the result by 100. 

Let’s say your landing page generated 150 leads (leads by source). If 11 of them converted into paying customers, your conversion rate for that lead source is 7.3% ((11 ÷ 150) x 100). 

Before creating your sales performance dashboard, identify the most important metrics you need to track. This prevents you from polluting your dashboard with vanity metrics that don’t affect sales performance.

Apart from the metrics listed above, below are other KPIs you should consider: 

  • Customer churn rate
  • Net promoter score
  • Customer lifetime value
  • Cost per conversion
  • Demos/trials
  • Average sales cycle length
  • Lead-to-Opportunity ratio 
  • Sales by region
  • Profit per product

3 Ways to create a Google Sheets sales dashboard 

After identifying the metrics you need to track, you have three options to consolidate everything together in a Google Sheets dashboard: building from scratch, using a dashboard template, and importing data from Business Intelligence (BI) platforms. 

1. Create a Google Sheets sales dashboard from scratch

To build an effective sales dashboard, start by learning how to create a dynamic dashboard in Google Sheets

A dynamic dashboard includes elements like charts and tables that automatically update whenever you add or change data. 

How to manually insert charts into your sales dashboard

Suppose you need a sales performance dashboard that tracks your top lead sources with a pie chart. 

The chart itself pulls data from a separate sheet, which organizes leads under columns for each lead source. 

On your dashboard sheet, click ‘Insert,’ then select ‘Chart.’ 

Image Source: Sheets.Google.com

Under “Chart type,” select ‘Pie chart’ or any other suitable data visualization for sales performance metrics.  

Image Source: Sheets.Google.com

The next step is to pick the data range you need to visualize. 

Click the table 2x2 table icon under “Data range” and select the cells you want to include. Alternatively, enter the data range’s location manually using the syntax: 

‘[Sheet Name]’![Cells]

Image Source: Sheets.Google.com

Upon clicking ‘OK,’ your chart should automatically update to reflect the selected data range. 

Image Source: Sheets.Google.com

Feel free to customize your chart’s appearance, type, fonts, style, and more. Other than pie charts, below are other data visualization types you can add to your sales dashboard:

  • Line chart
  • Combo chart
  • Area chart
  • Column and bar graphs
  • Scatter plot
  • Data map
  • Gauge chart

How to create pivot tables for your sales dashboard

Another element you should include in your sales dashboard is a pivot table. It is a dynamic table that summarizes key takeaways from large data sets. 

To add a pivot table, click ‘Insert’ and select ‘Pivot table.’ 

Image Source: Sheets.Google.com

On the “Create pivot table” window, select the data range you want to include and whether to use a new sheet or an existing sheet. 

Image Source: Sheets.Google.com

Construct your pivot table by adding your columns, rows, values, and filters. You can also rearrange rows, show totals for important data sets, and more. 

Image Source: Sheets.Google.com

One of the benefits of creating a custom sales dashboard from scratch is flexibility. As long as you know what to track, you’re free to piece together a unique blend of data visualizations that fit your needs. 

2. Customize a sales dashboard template

Save time building your sales dashboard by kickstarting the process with templates. 

You’ll find tons of sales dashboard templates on the web, like this monthly sales dashboard template from HubSpot.

Image Source: Sheets.Google.com 

This template is designed to track sales growth month after month. It highlights crucial sales metrics like trial signups, new leads, MRR region, and MRR growth. 

HubSpot’s dashboard also lets you set monthly targets for leads, trials, and wins. 

Image Source: Sheets.Google.com

Although Google Sheets templates are customizable, it’s recommended to look for a template that already has everything you need. 

The right template allows you to start tracking vital sales data from day one. 

If you need to customize it, be sure to avoid changing existing formulas or rearranging the cells. Doing so might lead to errors or inaccurate sales data reporting. 

Check out our list of the top 10 free Google Sheets dashboard templates for more ideas.

3. Use a Business Intelligence (BI) tool

If you want the time-saving benefit of templates without the messy customization process, consider using sales dashboard software or comprehensive BI platforms.

You just need to learn how to import data into Google Sheets (or how to export data from it) and you’re good to go.

Solutions like Polymer consolidate data from a range of sources, including Google Sheets, Google Ads, Zendesk, Airtable, Jira, and more. This provides you with a single source of truth for all your business decisions. 

Polymer lets you manage, organize, and analyze your data via drag-and-drop tables. 

If you wish to export data from Polymer to Google Sheets, click the ‘Download’ icon from your data page and select ‘Download as CSV’ or ‘Download as XLS.’ 

On Google Sheets, click ‘File’ and select ‘Open.’ Switch to the ‘Upload’ tab, click ‘Browse,’ and look for the downloaded file from Polymer. 

Image Source: Sheets.Google.com

Another option is to import your data from Google Sheets to Polymer instead. This lets you take advantage of Polymer’s powerful data visualization tools—from interactive scatter plots to detailed pivot tables. 

These data visualizations can be combined and configured in virtually limitless ways. For ideas, take a look at these AI-based visualization charts suggested by Polymer. 

See the big picture with Polymer

Go beyond creating basic, boring-looking sales dashboards.

Build a stunning, easy-to-use, and highly customizable sales dashboard for your business with Polymer.

Our software allows you to turn in-depth sales data into refined, actionable insights with the drag-and-drop data visualization tool. Create pivot tables, timelines, scatter plots, scorecards, and more. 

Best of all, you don’t need to invest a single cent to get started. Click here to start your free trial and build your first Polymer dashboard today.

Get a free sales dashboard template from Polymer.

Related Articles

Browse All Templates

Start using Polymer right now. Free for 7 days.

See for yourself how fast and easy it is to uncover profitable insights hidden in your data. Get started today, free for 7 days.

Try Polymer For Free