How to Create a Google Sheets Dashboard

In this article we are going to show you the steps to creating dashboards in Google Sheets without needing to know how to code. Learn how to import data and use relevant features (pivot table, slicer, etc) to improve your data reporting skills in no time

11-minute read
Table of Contents

Step 1: Plan out your dashboard

  1. Types of Dashboards
  2. Important Questions
  3. Other Considerations

Step 2: Import your Data to Google Sheets

  1. From an external source via IMPORT functions
  2. From Google Forms
  3. From an external source via ImportJSON API
  4. From an external source via add-ons

Step 3: Visualize Your Data

  1. Pivot Table
  2. Slicer
  3. Charts
  4. Sparkline

Final remarks

Several services such as Google Analytics and Shopify offer you ways of displaying important metrics on a single screen or page. 

The thing is, Google Analytics might not display the proper metrics for your needs, so you have to customize your own dashboards. One way to do so is by using Google Sheets.

What is a Google Sheets Dashboard?

A google sheets dashboard is used for visualizing data from one or more sources in a single location so you can get a birds eye view of all of your most important metrics. Dashboards help employees managers and stakeholders make more informed, data-driven decisions.

At this point, you may ask yourself:

Will a customized Google Sheets dashboard improve my productivity?

Here are some of their advantages over the built-in display offered by individual platforms:

  1. You can calculate key metrics not offered by a specific platform
  2. You can customize the display your own way
  3. You can store the raw data in another server

The main disadvantage, of course, is that you have to spend some time constructing one. (This still assumes that you won't encounter any significant technical problems when doing so.)

...Well, we're here to help out with the whole building process. If you have decided to create a dashboard yourself, then this guide is for you!

How to Create a Google Sheets Dashboard

You need to follow these 3 simple steps to create a dashboard in Google Sheets…

Step 1: Plan out your dashboard

The first thing you need to do is… to plan! Even though saying “plan first” or “when you fail to plan, you plan to fail” sounds cliche, there is a good reason to plan first. 

You need to know first what kind of information you want to get display.

Why? 

You can't just include all of your raw data in a dashboard. Putting too much data in it will render it useless. Combining relevant information with the right method of data visualization will make your dashboards indispensable in helping you observe the growth of your business.

Types of Dashboards

There are four main types of dashboards:

  1. Strategic dashboards - These focus on high level macro metrics that can be used for making strategic decisions.
  2. Operational dashboards - focused on tracking operational processes, shorter time frames.
  3. Analytics dashboard - focused on uncovering trends by processing huge amounts of historical data.
  4. Tactical dashboards - focused on tracking the performance of the company

Identifying a specific genre will help you identify what information will be included in your Google Sheets dashboard.

Important Questions to decide Dashboard Features

Additionally, it will help you see what features of Google Sheets you will use. These are some of the important questions to answer that will help you decide on the amount of work needed:

  1. Where will data come from? Services such as Google Analytics and Shopify have their own API with their complementary documentation where the structure of the data stored in their servers is described in detail. There are small but significant differences in the way the data is stored for each of these services. 
  2. What kind of data will be retrieved? Most of the time, the databases of these services contain as much data as they can store. You will need to filter the data that you will need so that the dashboard will not be overwhelmed.
  3. How frequently should the dashboard be updated? Should the dashboard be updated every minute? Every 15 minutes? Or every hour? This information will help you build a dynamic dashboard and also help your dashboard not be overwhelmed with the data coming from your services.
  4. Is visualization enough? Or does the data still need to be processed? Some common and important metrics are automatically calculated by the services and stored in databases, but others that you find relevant may not be offered. You will still need to calculate them in Google Sheets.
  5. Do you want to store the displayed data in separate sheets? There are advantages in storing the data in separate Google spreadsheets (or files), but these make the dashboard more complicated. You will need to do this if you still process the data to calculate important metrics, to store them for future audit and analysis. 

Once you address these questions, you will now have an idea of what should appear in your Google Sheets dashboard.

Other Considerations

Some other things to consider when you decide to create a dashboard from Google Sheets:

We have written an article about data analysis, which explains how to conduct analysis, from collecting raw data to data validation. A pretty Google Sheets dashboard is only the final conclusion of an extensive process.

Taking note of these tips, you will arrive at a design that will be useful to you and to other users. Once you are done with designing it, you can now start implementing it! Continue reading to learn the more technical side of dashboard creation. 

Step 2: Import Data to Google Sheets

Your answers to Step 1 (Where will data come from? What kind of data will be retrieved?) will dictate how your raw data is imported to your dashboard in Google Sheets. Here are some of the possible solutions to the problem of importing data to Google Sheets, depending on the source:

‍From an external source via IMPORT functions

If the data is publicly accessible, Google Sheets has a set of IMPORT functions depending on the format of the data source: 

The disadvantage of this method for importing data from outside Google is that it must be publicly accessible. For the case of IMPORTRANGE, you need to authorize access to the Google Sheets spreadsheet. (However, this is easy if you own the spreadsheet, as Google will prompt you to give access to it.)

From Google Forms

Google Forms is well integrated into the G Suite. Our guide to integrating Google Sheets with Google Forms covers everything you need to get started.

If there is already an existing Google Form or Google Forms with their corresponding spreadsheet where the results are continuously being stored, you can import the responses through the IMPORTRANGE function directly to your dashboard in Google Sheets. The IMPORTRANGE function is discussed here: 3 Easy Ways to Import Data from Another Workbook in Google Sheets 

For more versatility, you can combine the IMPORTRANGE function with the QUERY function. This allows you to filter the data before importing it to your dashboards. Here is our tutorial in doing so: How to SQL Query Multiple Sheets in Google Sheets 

If you are yet to create a Google Form, you should create it and create the corresponding spreadsheet to store the data separately from the dashboard. This keeps it from being accidentally overwritten. 

From an external source via ImportJSON API

ImportJSON is a JavaScript-based code by Brad Jasper that you can import to your spreadsheet via the Script editor. Here is our tutorial on using ImportJSON to import data from an external source via API. The code can be accessed in his GitHub repository

‍From an external source via add-on

Chances are, there are already existing Google Sheets add-ons for a specific service you are using. If not, you can dive a little into the technical side of things by checking the Google API connector add-ons available, and then connecting them using the API. 

There is a disadvantage to this method, however. The developer of the add-on can push updates at a moment's notice and completely break how your dashboard works.

Data Validation

Regardless of your source, data validation is essential before constructing your dashboard. After you retrieve data from your data warehouse, you want to ensure that you only have high-quality, useful information in your spreadsheet.

To complete the data validation process before building your dashboard, you can use a manual review process or a script or other program.

Step 3: Visualize Your Data

Once you manage to import all the data to your Google spreadsheet dashboard, how would you be able to visualize it?

Here are some of the Google Sheets functions and features you will find useful for turning your raw numbers into a pretty display of important metrics.

Pivot Table

An example of a Pivot table in Google Sheets. 
An example of a Pivot table. 

Pivot Table is a powerful feature that can summarize long worksheets of mind-numbing columns and rows. It allows you to summarize information by date, by category, and by specific data. To add a Pivot Table, here are the steps:

  1. Click on any occupied cell in the sheet containing the data.
  2. Click Data on the main menu, then select Pivot table
The Pivot table option can be accessed by clicking the Data option in the main menu.
The Pivot table option can be accessed by clicking the Data option in the main menu.
  1. A small pop-up window will appear, asking you whether to insert the Pivot Table to a separate new sheet or to an existing sheet. 
A pop-up window will ask you for the data range and in which sheet to add (or to a new sheet).
A pop-up window will ask you for the data range and in which sheet to add (or to a new sheet). 

We have prepared a set of tutorials for you to further harness the power of the Pivot Table:

Slicer

A slicer applying a condition to a long sheet.
A slicer applying a condition to a long sheet.


Another feature of Google Sheets that you can use is the Slicer. The Slicer is used to “slice away” portions of the long worksheets so that only the selected data remains. 

Here are the steps to add a Slicer in a different sheet:

  1. On the sheet where you want to insert the Slicer, click Data in the main menu and then click Slicer on the drop-down menu that will appear. Make sure you select an empty cell.
  2. A pop-up box will appear where you can add the data range. You can manually type the data range or select from a list of suggested ranges in the same windows.
A pop-up window where you can add the data range covered by the slicer. Google Sheets also suggests possible data ranges in the sheet.
A pop-up window where you can add the data range covered by the slicer. Google Sheets also suggests possible data ranges in the sheet. 

A Slicer will now appear in the same sheet.

The Slicer will be of big help in controlling what is displayed in charts. We have prepared two guides for you to harness the power of the Slicer feature:

Charts

A chart containing data from multiple ranges in Google Sheets.
A chart containing data from multiple ranges. That's a dashboard Google Sheets power users would be jealous of!

The mainstay of any and every dashboard, charts help you quickly understand the patterns in the data, especially in the important metrics.

To insert a chart in a sheet, click Insert in the main menu and then click Chart.

The chart feature can be accessed by clicking the Insert option in the main menu.
The chart feature can be accessed by clicking the Insert option in the main menu. 

A blank chart will appear in the center with a sidebar on the right side. You can add the data range covered by the chart by going to the Data range portion of the sidebar and then entering the data range manually by typing or by clicking on the symbol on the right end of the textbox, where you can select the sheet and then select the spreadsheet data.

While they may be the simplest chart types, line charts, pie charts, and bar charts can be some of the most effective options for visualizing data.

A Google Sheets worksheet containing a blank space for a chart.
A spreadsheet containing a blank space for a chart. 

We also have some guides to help you further harness the power of the charts in Google Sheets:

Sparkline

A sparkline chart in Google Sheets.
A sparkline chart in Google Sheets.

One disadvantage of using a bar chart, column chart, or another chart is that they often overlap with the sheet and can cover other cells that may contain important data. If you do not want the full-blown functionality of the Charts but still want basic visualization of the data through one of the following forms:

Then the Sparkline chart function is the perfect fit for you! The Sparkline chart function inserts a small chart in a cell where the formula is added. 

The dimensions of the chart follow the dimensions of the cell where it is included. This gives you more flexibility in your layout: you can precisely set the cell or cells covered by the sparkline chart, even more so than a typical chart. 

To insert a sparkline chart, use the following syntax:

=sparkline(<array_of_cells>,{<attributes>})</attributes></array_of_cells>

Where the <array_of_cells> contain the data to be visualized, while the <attributes> contain certain variables that will control the display of the sparkline chart. </attributes></array_of_cells>

For our simple example, we want to plot the per annum total from the sheet below:

An array containing annual sales data, covering the years 2013 to 2019.

We note that the range of values is on the array C3:C9, and we use it as the source array for the formula:

=sparkline(C3:C9)

Producing the following chart:

A simple sparkline chart containing a line chart.
A simple sparkline chart containing a line chart.

The SPARKLINE function outputs the plot of the given values without annotations such as the title of the plot or the legend (especially used in charts and charts that use multiple sets of similar data). 

To learn more, head over to our comprehensive tutorial on Sparkline here: A Beginner's Guide to the SPARKLINE Function in Google Sheets

Final remarks

These are just some of the things you need to know before you start working on creating a dashboard in Google Sheets. If you think that this is quite intensive work, then yes you are right! Nonetheless, the final result should give you a sweet taste of success, and hopefully, makes your job easier. In the end, you should consider what your business needs.

If you decide to customize your own dashboard, you might not have enough time to do this. We do have something else in store for you…

Consider trying Lido. With a few clicks, you can now create your own dashboard without needing to know how to make a dashboard in Google Sheets. Access all the relevant metrics without having to construct your own spreadsheet dashboard. Let our platform do it for you!

Enter Your Email Below to Try Lido for FREE!

Experience all of the benefits of the classic spreadsheet with much more powerful features that save users time!
  • One Click Data Imports From Anywhere
  • Transform Spreadsheets into Software in Seconds
  • Pre-built & Custom Templates
  • Visualize Real-Time Data in Centralized Dashboards
100% FREE - No Credit Card Required