How to Create a Sales Dashboard in Google Sheets

Video google sheet sales dashboard

copy-the-google-sheets-data

A sales dashboard provides an overview of your sales performance and lets you know if you’re hitting your goals. It presents your data in visually appealing and easy-to-read visualizations and reports.

However, most sales management tools and customer relationship management (CRM) software have built-in dashboards with limited customization features and only a few metrics. Plus, unless you’re familiar with setting up a sales dashboard in Salesforce, the whole process can be time-consuming and effort-intensive.

That’s where Google Sheets comes in. With its features, apps, functions, and formulas, Google Sheets offers a simpler solution for creating customized reports and visualizations for your sales dashboard.

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

What is a Sales Dashboard?

A sales dashboard is an easily understandable graphical presentation of your essential sales data. Its aim is to help sales managers and business owners, among others, make better data-driven business decisions.

Dashboards present your sales data in a more meaningful way, often using charts, heat maps, gauges, and other visualizations. They can be interactive or static, and most modern sales dashboards are dynamic, time-based (displaying data in adjustable or regular time intervals), and often connected in real-time to a database or data source.

Essentially, a sales dashboard involves specific sales data points and metrics that give you an instant view of the health of your sales operations. It helps you identify crucial areas to improve and optimize, and determine which sales reps are closing deals. This will boost your sales performance and help you achieve better results.

You can visualize essential metrics or create specific variations and dashboards, such as a sales metrics dashboard. You can also include the data you want to track and display in your dashboard, such as your total revenue, success rate, and lost deals compared to won deals.

Why Use Google Sheets to Create a Sales Dashboard?

Google Sheets offers more than just basic formulas and functions. If you know what you’re doing (or if you dig a little deeper), you’ll discover many tools, tables, and charts in the program to easily create custom reports.

Some of the analyses and reports you can accomplish in Google Sheets include Gantt charts for product management, sales forecasting, creating sales pipelines, tracking lead-to-opportunity conversion rate, and many other use cases.

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

The spreadsheet program is cloud-based, allowing you to access your Google Sheets dashboard from anywhere with your Google account. This can facilitate teamwork and collaboration and enables flexibility in your work.

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

Google Sheets also includes functions for importing data from multiple types of structured data, returning the selected maximum value in a database range or similar table, and linking data from other spreadsheets and workbooks.

Determine What to Include in Your Sales Dashboard

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

Identify key performance indicators (KPIs) that are meaningful to you, your managers, and your team. You can then determine the data sources and types of data to monitor.

It’s helpful to ask yourself the following questions:

Where will your data come from? Will your data source allow for automated import, export, and synchronization, or will you need to do it manually? Will this require a third-party tool?
Who is the dashboard for? Is it the sales dashboard for your sales manager, representatives, or administrator?
How often will you need to update the sales dashboard?
What are the specific metrics and data points that often appear in your reports?
What are your identified KPIs?
Are there metrics that are regularly considered or reviewed as more crucial than others during your sales team meetings and individual seller reviews?
Does your company have multiple sales teams, such as field sales teams and inside sales teams?

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

Import Your Data

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

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

Coefficient is a powerful tool for data import, export, and synchronization. It’s easy to set up and use, simplifying the extraction of data from your source.

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

In the Coefficient popup window, click on 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’ll be using to create your dashboard.

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

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

Click Import. This should automatically populate the data in Google Sheets within seconds or minutes, depending on the size of your data.

Here’s what your Salesforce data import in Google Sheets can look like.

Create Your Interactive Sales Dashboard

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

Deals (won, open, and lost)
Geographical map with total revenue of all countries and conversion rates
Lost deals compared to won deals
Success rate

Use a Sales Dashboard Template

To simplify the creation of your sales dashboard, start with a template that you can easily build upon, such as this monthly sales dashboard template from HubSpot.

Using a sales dashboard template for Google Sheets speeds up the creation of your dashboard as 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 the 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(Transactions!AL2:AL, "won"), COUNTIF(FILTER(Transactions!AL2:AL, Transactions!Z2:Z = A19), "won"))

To better understand this formula, here’s a brief explanation:

Transactions!AL2:AL should be the status column of your imported data
Transactions!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” in the formula), and you should get something that looks like this.

Create a value visualization using a pie chart. Select the range of data, click on Insert in the Google Sheets menu, and then click on Chart.

In the Chart editor pane, select Pie chart in the Chart type tab. Click on the Customize tab and format the labels, colors, texts, and other elements of the chart.

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

Geographical Map

To create your geographical map, you’ll need three columns for the conversion rate, country, and total revenue, respectively.

You can use Data Validation to manually populate the Country column. Click on Data in the Google Sheets menu, then click on Data Validation, and select the column or range with the country names from your dataset.

A faster way is to use the sample formula below.

={"Country Name"; UNIQUE(Transactions!Your range here)}

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

Use this formula:

=COUNTIF(FILTER(Transactions!AL$2:AL, Transactions!$Z$2:$Z = A61), "won") / COUNTA(FILTER(Transactions!AL$2:AL, Transactions!$Z$2:$Z = A61))

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

Drag the formula down or use the Copy Down shortcut (Ctrl+D) to use it for the other countries in your data.

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

=SUM(FILTER(Transactions!$AF$2:$AF, Transactions!$Z$2:$Z = A54, Transactions!$AL$2:$AL = "won"))

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

Your final table should look like this.

Now that you have set up the data you need for each country, add a geographical map to visualize the information in your sales dashboard.

Select the table, click on Insert in the menu, then click on Chart. In the Chart editor, under the Setup tab, choose Geo chart.

Customize the appearance and configuration of your geographical map and add it to your sales dashboard.

Lost Deals Compared to Won Deals

Calculating lost and won deals is quite simple using the following formula.

=IF(ISBLANK(A19), COUNTIF(FILTER(Transactions!AL2:AL, Transactions!CN2:CN = 2016), "won"), COUNTIF(FILTER(Transactions!AL2:AL, Transactions!Z2:Z = A19, Transactions!CN2:CN = 2016), "won"))

Replace Transactions!CN2:CN=2016 with each corresponding year to get the value for 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 deals, and won deals. Follow the same process of inserting a chart, choose the column chart type, and customize the chart elements according to your preferences before adding it to your sales dashboard.

Success Rate

Calculate the success rate 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 success rate for the following years and create a smooth line chart to add to your sales dashboard.

Select the year column and success rate column, then follow the same steps to insert a chart. Choose the Smooth line chart type in the chart type options and modify the chart elements accordingly.

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

Apply formatting and customizations as desired and organize the charts in a way that best serves the needs of the users and your target audience.

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.

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

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

Additionally, importing, exporting, and synchronizing your data is extremely easy and quick with Coefficient. The app allows you to retrieve data from your source and import it into Google Sheets with just a few clicks.

You can also ensure that your dashboard data is always real-time and up-to-date because Coefficient lets you schedule automatic data updates. This means you won’t have to import new data every time the information from your source changes, saving you valuable time and effort, as well as your sales team.

Try Coefficient for free today!

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