How to Build a Sales Dashboard in Google Sheets

How to Build a Sales Dashboard in Google Sheets
May 14, 2021 Rand Owens

Overview

A sales dashboard shows a bird’s eye view of your sales performance and whether or not you’re meeting your targets.

It presents your data in comprehensive, easy-to-read visualizations and reports.

However, while most sales management and Customer Relationship Management (CRM) tools provide built-in dashboards, most have limited customization features and only include a few metrics.

What’s more, unless you’re well versed and know how to set up a sales dashboard in Salesforce, the entire process could take up too much time and effort.

Google Sheets provides a more straightforward solution with its features, apps, functions, and formulas that allow you to create custom reports and visualizations for your sales dashboard quickly.

In this guide, we’ll cover what a sales dashboard is, what key metrics to include, and how you can build one using Google Sheets.

What is a sales dashboard?

A sales dashboard is an easy-to-understand graphical presentation of your critical sales data. It’s intended to help sales managers and company owners (among others) to make better, data-driven business decisions.

Dashboards present your sales data in a more meaningful way, often through graphs, heatmaps, gauges, and other visualizations.

These can be interactive or static; and most modern sales dashboards are dynamic, time-based (showing data in adjustable or regular time frames), and often connected in real-time to a database or data source.

Essentially, a sales dashboard involves sales-specific data points and metrics that give you an up-to-the-moment view of your sales operation’s health.

This helps you identify crucial areas for improvement and optimization, and determine which sales reps are closing deals. It will boost your sales performance and help you gain better outcomes.

You can visualize essential metrics or create variations and specific dashboards, such as a sales metrics dashboard. You can also include data you want to track and see in your dashboard, such as your total revenue, win ratio, and lost vs. won deals.

Why use Google Sheets to create a sales dashboard?

Google Sheets offers more than the basic formulas and functions. If you know what you’re doing (or do a bit of digging), you’ll uncover many of the program’s tools, tables, and charts to build custom reports easily.

Some of the analysis and reporting you can do in Google Sheets include Gantt charts for product management, sales forecasting, sales pipeline creation, lead to opportunity conversion rate tracking, and many other use cases.

Google Sheets is a calculation-centered program, offering a huge list of functions (formulas) that allow you to manipulate your data to extract your desired information and insights.

The spreadsheet program is cloud-based, letting you access your Google Sheets dashboard from anywhere with your Google account. This can help streamline teamwork and collaboration and allows flexibility in your work.

While Google Sheets might not have the most advanced features, it offers the essential tools and functions necessary to build a comprehensive sales performance dashboard. It performs crucial analyses and generates reports.

Google Sheets also include functions for importing data from multiple structured data types, returning the maximum value selected from a database’s range or table-like array, and linking data from other spreadsheets and sheets.

Determine what to include in your sales dashboard

Before you start building your sales dashboard, identify the data points and metrics you want to track, highlight, and see.

Know the Key Performance Indicators (KPIs) that are meaningful to you, your managers, and your team. You can then determine the data sources and types to monitor.

It helps to consider these questions.

  • Where will your data come from? Will your data source allow automated importing, exporting, and syncing, or will you need to do it manually? Will this require a third-party tool?
  • Who is the dashboard for (or your target audiences)? Is your sales dashboard for your sales manager, reps, or admin?
  • How often will you need to update the sales dashboard?
  • What specific metrics and data points show up often in your reports?
  • What are your identified KPIs?
  • Are there metrics that are regularly seen or reviewed as more crucial than others during your sales team meetings and one-on-one salesperson (or rep) reviews?
  • Does your company have multiple sales teams, such as field and inside sales units?

Address these questions to help you determine what data and metrics to include and how to best present the information to get the most out of your sales management dashboard.

Import your data 

Let’s start by importing your sales data to Google Sheets.

In this guide, we’ll show how you can import your data from Salesforce to Google Sheets seamlessly using the Coefficient application.

Coefficient is a powerful tool for importing, exporting, and syncing your data. It’s easy to set up and use, streamlining fetching data from your data source.

Once you install the Coefficient: Salesforce, HubSpot data connector app from Google Workspace Marketplace, launch it from the Add-ons tab on the Google Sheets menu.

On the Coefficient pop-up window, click Import Data and select Salesforce.

If you have a ready Salesforce report, choose Import from report. This can save you a lot of time and effort when importing the sales data you will use to build your dashboard.

The Salesforce importing options on the Coefficient popup window.

Select your preferred Salesforce importing method.

Select the objects you want to include in your Salesforce data import and use the search box to find objects quickly. Next, select the fields you want to add to your dataset.

The list of Object fields.

Choose the object fields to include in your data import.

You can also add a filter and sort criteria, set an import limit, and name your import so you can find and reuse it easily for your future reports and analyses.

Click Import. This should auto-populate the data to Google Sheets in a few seconds or minutes, depending on the size of your data.

This is how your Salesforce data import on Google Sheets can look.

A sample Salesforce data import on Google Sheets.

Coefficient imports your data to Google Sheets instantly.

Create your interactive sales dashboard

A sales dashboard can include many components and sections. However, in this guide, we’ll focus on building a dashboard with these sections:

  • Deals (Won, Open, and Lost)
  • Geo chart with total revenue of all countries and conversion rate
  • Lost deals vs. Won deals
  • Win ratio

Use a sales dashboard template

To simplify creating your sales dashboard, start with a template that you can easily build on, such as this sample Monthly Sales Dashboard from HubSpot.

A monthly sales dashboard template by HubSpot.

Streamline building your sales dashboard with a Google Sheets template.

Using a sales dashboard template for Google Sheets speeds up your dashboard creation since you can easily customize the sections and replace the data.

Deals (Won, Open, and Lost)

Your imported sales data should include your won, open, and lost deals.

To calculate the number of deals based on won, open, or lost status, use this sample formula with the COUNTIF function. In this example, we’ll calculate the won deals.

=IF (

ISBLANK(A19) ,

COUNTIF (Deals!AL2:AL,”won”) ,

COUNTIF (Filter(Deals!AL2:AL,Deals!Z2:Z=A19),”won”))

To better understand this formula, here’s a quick breakdown:

  •  Deals!AL2:AL should be the status column of your imported data
  • Deals!Z2:Z is the org_id.address column of your imported data
  • A19 is a cell with the country name

Use the same formula to calculate the number of open and lost deals (replace “won” with “open” and “lost”), and you should end up with something that looks like this.

A column with open, won, and lost deals and another column with their values.

Calculate the won, lost, and open deals from your dataset.

Create a visualization of the values using a pie chart. Select the data range, click Insert from the Google Sheets menu, and click Chart.

The Insert drop down menu and Chart option.

Insert a chart to create your data visualization.

On the Chart Editor pane, select Pie chart from the Setup tab. Click the Customize tab and format the labels, colors, texts, and other chart elements.

A pie chart and the chart editor pane.

Customize your pie chart as you want.

Add the pie chart to your sales dashboard template or spreadsheet.

Geo chart

To create your Geo chart, you’ll need three columns for conversion rate, country, and total revenue, respectively.

You can use the Data Validation function to fill in the Country column manually. Click Data on the Google Sheets menu > Data validation and select the column or range with the country names on your dataset.

The Data tab dropdown menu and the Data validation option.

Use the Data validation function on Google Sheets to fill in the country column manually.

A faster way is to use the sample formula below.

={“Country name”; UNIQUE(Deals!Your range here)}

Next, let’s figure out the conversion rate per country, which is the ratio of your won deals to your total deals.

Use this formula:

=COUNTIF (Filter(Deals!AL$2:$AL,Deals!$Z$2:$Z=A61),”won”)/

COUNTA (

 Filter (Deals!$AL$2:$AL,Deals!$Z$2:$Z=A61))

The Deals!AL2:AL in the formula refers to the status column, and the Deals!Z2:Z is the org-id.address (or country name) column of the imported data. A61 is a cell for the country name.

Drag the formula or use the Ctrl+d Copy Down shortcut to use it for the other countries in your data.

Finally, to get the Total revenue, calculate the sum of all the won deals for each country using this formula.

=SUM (

Filter (Deals!$AF$2:$AF,Deals!$Z$2:$Z=A54,Deals!$AL$2:$AL=”won”)

)

The value column is the Deals!AF2:AF while Deals!AL2:AL refers to the status column of the imported data. A61 is a cell with the country name, and Deals!Z2:Z is the org_id.address column.

Your final table should essentially look like this.

A conversion rate, total revenue, and country columns in a spreadsheet table.

Calculate all the values necessary for your Geo chart.

Now that you’ve set up the data you need for each country, add a Geo chart to visualize the information for your sales dashboard.

Select the table, click Insert from the menu, then Chart. On the Chart editor, under the Setup tab, choose Geo chart.

A Geo chart and the chart editor pane.

Customize the Geo chart elements and colors accordingly.

Customize your Geo chart’s appearance and configuration accordingly and add it to your sales dashboard.

Lost deals vs. Won deals

Calculating the lost and won deals is pretty straightforward by using the formula below.

=IF (

ISBLANK(A19) ,

COUNTIF (Filter(Deals!AL2:AL,Deals!CN2:CN=2016),”won”) ,

COUNTIF (Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),”won”)

)

Replace Deals!CN2:CN=2016 with each corresponding year to get the value of the rest of the data. Use the same formula to calculate your lost deals and replace “won” with “lost” in the formula.

Once you have the data, apply a column chart.

Select the column containing the years, lost, and won deals. Follow the same process of inserting a chart, choose Column chart as the chart type, and customize the graph as you prefer before adding it to your sales dashboard. 

A lost and won deals column chart and the chart type options.

Create a column chart for your lost and won deals.

Win ratio

Calculate the win ratio by dividing your won deals by the closed deals (lost deals plus won deals).

You can use a simple formula: A cell with your won deals in 2016/(a cell with your lost deals in 2016+a cell with your won deals in 2016).

For example:

=E75/(D75+E75)

You can then calculate the win rate for the remaining years and create a smooth line chart to add to your sales dashboard.

Select the year and Win ratio columns and follow the same steps when inserting a chart. Choose the Smooth line chart from the chart type options and modify the graph elements accordingly.

A smooth line chart of the win ratio.

Insert a smooth line chart to visualize your win ratio.

At this stage, you should have all the charts and data visualizations you need to create your sales dashboard.

Map with data showing the number of deals and total revenue earned per country

View how the number of deals and total revenue you earned per country.

A sales dashboard.

Format and organize your sales dashboard.

Apply your desired formatting and customizations and organize your charts in a way that best suits users and your target audiences.

You can create specific dashboards, such as a sales KPI dashboard, and add other key metrics. This can include your lead conversion rate, customer lifetime value, monthly sales growth, and retention and churn rates.

Conclusion: Building a sales dashboard in Google Sheets doesn’t have to be rocket science

Now that you know the essential steps to create a sales dashboard in Google Sheets, you can start creating one with the help of our guide.

Using Google Sheets’ formulas and functions to create your sales data reports, analyses, and visualizations, you can efficiently build and customize your sales dashboard.

Plus, importing, exporting, and syncing your data is uber-easy fast with Coefficient. The app lets you fetch data from your source to import to Google Sheets instantly with a few clicks.

You can also guarantee your dashboard data is always live and updated since Coefficient lets you schedule data auto-refresh. This means you won’t need to import new data every time the information from your source changes, saving you and your sales team a huge chunk of time and effort.

Try Coefficient for free today!