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

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.

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

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:

• How To Use IMPORTRANGE Function In Google Sheets
• QUERY with IMPORTRANGE in Google Sheets
• VLOOKUP with IMPORTRANGE in Google Sheets

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

• How to Use IMPORTDATA in Google Sheets
• How to Use IMPORTHTML in Google Sheets
• How to Use IMPORTXML in Google Sheets (Examples)

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:

• How to Make a Pie Chart in Google Sheets
• How to Create and Use a Pivot Table in Google Sheets
• How To Use SPARKLINE in Google Sheets (+ Examples)

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

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.

1. 2. To make it easier to work with the data, you can name the table and the columns you need.

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.

1. 2. Under ‘Chart type’, select the scorecard chart.

1. 3. Check the ‘Aggregate’ checkbox and select ‘Sum’ from the dropdown menu.

1. 4. You can customize and format the scorecard however you want.

1. 5. Click the vertical ellipsis on the top-right corner of the scorecard and select ‘Copy chart’.

1. 6. Paste the chart into the sheet where you want the dashboard.

1. 7. You can remove the gridlines by going to View > Show and unticking ‘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.

1. 2. Under ‘Chart type’, choose the column chart and check the ‘Aggregate’ checkbox.

1. 3. Select the chart, copy it, and paste it into the dashboard.

1. 4. Repeat steps 1-3 for the columns containing the products and the amounts.

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.

1. 2. The second time, we will use the QUERY function to sort the data by the amount in descending order.

1. 3. Select the top three rows - both the name and the amount - and go to Insert > Chart.

1. 4. Select the ‘Table chart’ under ‘Chart type’.

1. 5. Copy the chart and copy it into the dashboard. You can resize the chart and add a title above it.

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.

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.

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.