Don’t forget to share this post

Spreadsheet tools like Google Sheets help us analyze and manipulate data. However, when it comes to presenting or reporting on key metrics and indicators, tracking them down by searching through many different spreadsheets can be a pain. Fortunately, dashboards are a great way to keep track of important metrics and KPIs, which you can create in Google Sheets. You can use one of Google Sheets dashboard templates or build one from scratch.

In this post, you will learn what a Google Sheets dashboard means and the steps you can take to create one. You have step-by-step instructions on how to plan your dashboard, how to import data, how to visualize it, and how to put it all together. Finally, you have examples of useful dashboard templates available from Google Sheets.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

What Is a Google Sheets Dashboard?

Google Sheets Dashboards allow you to group your important metrics and KPIs in one sheet, giving you a bird’s-eye view. Regarding how you present these metrics, you can choose from all the options available in Google Sheets, including tables and pivot tables, charts and graphs, timelines, etc.

How to Create a Dashboard in Google Sheets?

Building a dashboard in Google Sheets starts with planning. You need to decide what you want to see and how you want to see it. Once you have decided, you’ll have to import the data and apply the type of visualization you want.

Follow the steps below to build a sales dashboard from scratch using Google Sheets.

1. Planning Your Google Sheets Dashboard

As mentioned, the first step is to plan your dashboard: what metrics do you need to include? How do you want to visualize them? Where on your dashboard will you place them? Trying to decide this on the fly will likely lead to wasted time, so take your time and look at the data you have and how it is structured.

My sales data is structured like in the screenshot below and includes data for the first quarter of 2022

How to Create a Google Sheets Dashboard Step by Step Sales Data
How to Create a Google Sheets Dashboard Step-by-Step - Sales Data

In my dashboard, I want to include the following information:

  • Total sales for Q1.
  • Total sales by team.
  • Total sales by product.
  • Top 5 sales agents.

2. Importing Data to Your Google Sheets Dashboard

Once you know what to include in your dashboard, you need to import the data. Fortunately, you can import data to Google Sheets in many ways and from many sources.

If your data is in the same file as your dashboard, but on a different tab, you can simply reference it. To learn more about this, check out our guide on Linking Google Sheets: How to Reference Another Sheet.

If your data is in a different Google Sheets file, you can use IMPORTRANGE to get the data. To learn more about this function, check out these guides on:

If your data is available on the web, you can use any of the following functions depending on the data format:

If your data still needs to be collected, you can easily do that using the Layer Google Sheets add-on or using Google Forms.

My dashboard will be on the same file as the data but on a separate sheet or tab, which means I can use IMPORTRANGE or simply reference the data I need.

3. Visualizing Data in Google Sheets

Google Sheets offers a variety of options regarding data visualization. In addition to tables, pivot tables, and sparklines, you have many different graphs and charts available.

Take a look at these articles for more information on how to use some of these features:

Given the metrics selected in the first step, I will use the following visualizations for each:

  • Total sales for Q1 - card
  • Total sales by team - column chart
  • Total sales by product - column chart or tree
  • Top 5 sales agents - MAX function
Top Free Google Sheets Templates and Financial Models
Top Free Google Sheets Templates for 2023

Top Free Google Sheets Templates and Financial Statements to help you manage your business financials, monitor performance, and make informed decisions.

READ MORE

4. Putting Your Google Sheets Dashboard Together

Once you know the metrics you want to include, how you want to import the data, and how you want to visualize it, it's time to put it all together. Let's build a sales dashboard that features the metrics chosen in the previous steps. For this example, I will create the dashboard on a separate tab within the same file to reference it easily.

  1. 1. Open Google Sheets to the file with the data.
How to Create a Google Sheets Dashboard Step by Step Open Sheets
How to Create a Google Sheets Dashboard Step-by-Step - Open Sheets
  1. 2. To make it easier to work with the data, you can name the table and the columns you need.
How to Create a Google Sheets Dashboard Step by Step Named Ranges
How to Create a Google Sheets Dashboard Step-by-Step - Named Ranges

Add a Scorecard Chart

  1. 1. To get the Q1 sales total, you need the sum of the values in the column with the amounts. Select the ‘sales_amount’ column and go to Insert > Chart.

If you plan to add more data to the table, you can use SUMIF or SUMIFS to filter by date first.

How to Create a Google Sheets Dashboard Step by Step Select Sales Amount
How to Create a Google Sheets Dashboard Step-by-Step - Select Sales Amount
  1. 2. Under ‘Chart type’, select the scorecard chart.
How to Create a Google Sheets Dashboard Step by Step Scorecard Chart
How to Create a Google Sheets Dashboard Step-by-Step - Scorecard Chart
  1. 3. Check the ‘Aggregate’ checkbox and select ‘Sum’ from the dropdown menu.
How to Create a Google Sheets Dashboard Step by Step Aggregate
How to Create a Google Sheets Dashboard Step-by-Step - Aggregate
  1. 4. You can customize and format the scorecard however you want.
How to Create a Google Sheets Dashboard Step by Step Customize Scorecard
How to Create a Google Sheets Dashboard Step-by-Step - Customize Scorecard
  1. 5. Click the vertical ellipsis on the top-right corner of the scorecard and select ‘Copy chart’.
How to Create a Google Sheets Dashboard Step by Step Copy Chart
How to Create a Google Sheets Dashboard Step-by-Step - Copy Chart
  1. 6. Paste the chart into the sheet where you want the dashboard.
How to Create a Google Sheets Dashboard Step by Step Paste Chart
How to Create a Google Sheets Dashboard Step-by-Step - Paste Chart
  1. 7. You can remove the gridlines by going to View > Show and unticking ‘Gridlines’.
How to Create a Google Sheets Dashboard Step by Step Remove Gridlines
How to Create a Google Sheets Dashboard Step-by-Step - Remove Gridlines

Add a Column Charts

  1. 1. Select the columns with the team names and amounts from the sheet with the data and go to Insert > Chart.
How to Create a Google Sheets Dashboard Step by Step Insert Chart
How to Create a Google Sheets Dashboard Step-by-Step - Insert Chart
  1. 2. Under ‘Chart type’, choose the column chart and check the ‘Aggregate’ checkbox.
How to Create a Google Sheets Dashboard Step by Step Column Chart Aggregate
How to Create a Google Sheets Dashboard Step-by-Step - Column Chart & Aggregate
  1. 3. Select the chart, copy it, and paste it into the dashboard.
How to Create a Google Sheets Dashboard Step by Step Paste Column Chart
How to Create a Google Sheets Dashboard Step-by-Step - Paste Column Chart
  1. 4. Repeat steps 1-3 for the columns containing the products and the amounts.
How to Create a Google Sheets Dashboard Step by Step Sales by Product
How to Create a Google Sheets Dashboard Step-by-Step - Sales by Product
Data Analysis in Google Sheets Tips and Examples
Data Analysis in Google Sheets (+ Tips and Examples)

Google Sheets offers plenty of Data Analysis features that we can use to make sense of large data sets. Here’s how to do Data Analysis in Google Sheets.

READ MORE

Add a Table Chart

  1. 1. There are many ways to find the top 3 sales agents. For this example, we will use the QUERY function twice. First, we will group the data by the sales agent.
How to Create a Google Sheets Dashboard Step by Step QUERY to Group
How to Create a Google Sheets Dashboard Step-by-Step - QUERY to Group
  1. 2. The second time, we will use the QUERY function to sort the data by the amount in descending order.
How to Create a Google Sheets Dashboard Step by Step QUERY to Sort
How to Create a Google Sheets Dashboard Step-by-Step - QUERY to Sort
  1. 3. Select the top three rows - both the name and the amount - and go to Insert > Chart.
How to Create a Google Sheets Dashboard Step by Step Select Top 3
How to Create a Google Sheets Dashboard Step-by-Step - Select Top 3
  1. 4. Select the ‘Table chart’ under ‘Chart type’.
How to Create a Google Sheets Dashboard Step by Step Table Chart Type
How to Create a Google Sheets Dashboard Step-by-Step - Table Chart Type
  1. 5. Copy the chart and copy it into the dashboard. You can resize the chart and add a title above it.
How to Create a Google Sheets Dashboard Step by Step Paste Table Chart
How to Create a Google Sheets Dashboard Step-by-Step - Paste Table Chart
  1. 6. That’s it. You can add more metrics and customize the appearance of your dashboard as you wish. When you make changes to the data, the dashboard will update automatically.
How to Create a Google Sheets Dashboard Step by Step Sales Dashboard
How to Create a Google Sheets Dashboard Step-by-Step - Sales Dashboard

Google Sheets Dashboard Examples

If you prefer, the Google Sheets Template Gallery features various dashboard templates, including some by Supermetrics and Sisense. You can use them as they are or as the starting point for your own design.

You should also check out the Top 11 Free Google Sheets Templates. These templates cover different types of projects and use cases by focusing on different types of data and metrics.

How to Create a Google Sheets Dashboard Step by Step Dashboard Templates
How to Create a Google Sheets Dashboard Step-by-Step - Dashboard Templates

How to Automate Your Processes on Top of Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

Conclusion

Building a dashboard in Google Sheets is not that complicated. The trickiest part is deciding on the metrics you need to include and how to represent them best. However, there aren’t many options in terms of automation without using the Google Sheets API or Google Apps Script. Fortunately, Layer can help you with this by automating your processes, including data flows to your dashboards.

You now know how to build a dashboard in Google Sheets. You know how to import data to your dashboard using different functions, as well as how to use some of the visualization tools available in Google Sheets. Finally, if you prefer to use or build on an existing template, you know how to access some free Google Sheets dashboard templates.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 19 2023, Updated Jan 28 2023