Analytics

How to Create a Dashboard in Google Sheets? [with 3 Examples Included]

Want to know how to create a dashboard in Google Sheets? Follow our step-by-step guide to do it in no time.

Filip Stojanovic Filip Stojanovic on June 16, 2022 (last modified on June 21, 2022) • 16 minute read

Using spreadsheets in your business is a great way to store data and keep it organized.

However, in situations where you need to quickly pull up a specific KPI or metric, spreadsheets simply won’t cut it.

In the sea of raw data, finding the one metric you are interested in can feel like searching for a needle in a haystack.

To resolve this issue, we use Google sheets dashboards.

Google sheets dashboards are used for storing data in a visual format, which allows you to have a birds-eye overview of your most important metrics.

Dashboards are extremely engaging and they allow your employees, coworkers, and key stakeholders to quickly go over any type of information in a timely manner. 

In this report, we are going to explain exactly what Google sheets dashboards are, how you can create them, and provide you with some free templates that you can incorporate into your own company.

setup-free-dashboard-databox-dsss

What is a Google Sheets Dashboard?

Google sheets dashboards are one of the best ways to acquire a clear overview of your company’s key metrics and KPIs since they turn dry data into understandable visuals.

With the use of graphs, charts, and tables, your readers will have an easier time comprehending the important information and use it to make better data-driven decisions.

Plus, if you put a bit more work into the visuals, you can make the dashboard look both beautiful and professional, and impress your audience with a unique aesthetic.

This isn’t really the case with spreadsheets though, as they quickly become an ‘eye sore’ and you won’t be able to extract important data in a timely manner when it matters the most.

Furthermore, a dashboard in Google sheets can focus on different things. There are project dashboards, company-wide dashboards, team-specific dashboards (Marketing, Sales, HR, etc.), executive dashboards, and plenty more.

Nowadays, using a dashboard has become pretty much standard practice among companies. Team leaders will use them to showcase their performance to an audience by emitting them on an office TV screen.

This results in better-motivated teams, helps keep everyone in the loop, allows high-ranking members to make quick decisions, and makes identifying problematic areas a whole lot easier.

Related: How to Create a KPI Report in Google Sheets? Step-by-Step Guide

Google Sheets Dashboard Tutorial: Create a Comprehensive Google Dashboard in 3 Easy Steps

Now that you know what a Google sheets dashboard is and how important it can be, you are probably feeling tempted to try it out.

If that’s the case, here are the three easy steps you should follow when creating a dashboard in Google sheets.

Step 1: Importing or Gathering Data

The first thing you will have to do is make sure that you have enough raw data in your Google sheet before you start building a dashboard.

There are a few different ways you can import data into your sheet, so let’s go through them one by one.

Using IMPORT Functions

In case the data you want to import is already accessible, you can use Google sheets’ IMPORT functions to get the job done.

Using IMPORT Functions in Google Sheets

Depending on your format, here are some of the most-used functions:

  • IMPORTDATA – Used for importing csv and tsv formatted data.
  • IMPORTRANGE – Used for importing data within a specific range of cells.
  • IMPORTFEED – Used for importing ATOM and RSS feed data
  • IMPORTXML – Used for importing several data types (TSV, CSV, XML, HTML, RSS, ATOM, etc.). This function is essentially a combination of IMPORTDATA, IMPORTFEED, and IMPORTHTML.
  • IMPORTHTML – Used for importing data that is located on a specific HTML page.

For this method to work, the data you want to import will have to be publicly available beforehand.

Related: 40 Advanced Google Sheets Tips for Marketing Pros

Using Google Forms

Google Forms can be very useful in case you need to import an abundance of data to your spreadsheet. By using the IMPORTRANGE function we mentioned above, you will be able to quickly transfer the data from Google Forms to Google Sheets.

Also, if you use the QUERY function in addition to IMPORTRANGE, you can filter out the most important data before it makes its way onto the dashboard.

In case you don’t have Google Form and are creating a new one, we recommend that you connect it to a spreadsheet so data can be stored separately and you don’t risk losing it.

Using an External Source via an Add On

While this method does have its advantages, it requires quite a bit of technical knowledge in order to be applied.

You can search for API connector add-ons in the Google Workspace Marketplace and import the data to your Google sheet by using the API.

Using an External Source via an Add On

Although, if the developer ever decides to update the add-on, that might disrupt your dashboard’s functionality. This can be a huge setback since you would have to start building it again from scratch.

Related: 13 Ideas on How to Use Google Sheets In Your Business

Step 2: Parsing Data (Using Formulas)

Okay, so now you have all your data gathered in the spreadsheet and it’s time to move on to the next step – organizing it.

The current state of your dashboard is probably a combination of messy numbers and confusing data. Don’t worry, you will be able to transform that into meaningful insights in no time.

While this step might be a bit more complex than the previous one, you don’t need to be an experienced programmer to get the hang of it – you just have to pay attention to how the formulas work.

Here are the functions that you will need:

  • AVERAGE – Used for finding the average value of data in a certain column.
  • COUNTIF – This function calculates how many times a specific criterion appeared in an individual column.
  • COUNTIFS – Works the same as the COUNTIF function, but only counts the data if it fulfilled a specific condition.
  • SUM – Used for summing up all the data in a specific column.
  • SUMIF – Works the same as SUM, but only sums up the data if it fulfilled a specific condition.
  • SUMIFS – Works the same as SUMIF, but there are multiple different conditions.
  • SORT – Used for organizing a specific range of data by a descending or ascending order.
  • UNIQUE – Used for recognizing unique values in data sets.

Step 3: Visualizing Data

The final step is to visualize the data in your Google sheets dashboard.

This involves adding different elements such as sparklines, tables, charts, graphs, etc.

Here are some of the best features to visualize your data and make it aesthetically pleasing for the readers.

Pivot Table

The Pivot Table feature allows you to organize a large spreadsheet by only using the available data. For example, you can organize data through subcategories like name, date, price, or any other helpful identifiers.

You can create a pivot table by following these steps:

  • Select the cells that you want to turn into a table, click on ‘Insert’ in the heading bar, and then press ‘Pivot Table’.
  • You will see a pop-up window appear. In it, you can choose the exact data range and select whether you want the pivot table to appear in the existing sheet or a new one.
  • The new pivot table should now appear. Also, there will be a sidebar through which you can customize the different elements in the table.
Google Sheets Pivot table editor

Slicer

To organize your spreadsheet, you can use the Slicer feature.

Slicer, as the name suggests, slices your spreadsheet into separate parts so only the crucial data is showcased. It’s a great way to manipulate what your spreadsheet is displaying.

Here are a few steps you can follow to use Slicer:

  • Select Data in your heading bar and click ‘Add a Slicer’.
  • Choose a specific data range.
  • The slicer will come with a sidebar on the right side. You can use the sidebar to alter the parameters and filter the data by condition/value.
  • After you finish customizing the parameters, just go back to the original slicer feature. The data should now be ‘sliced’ and separated from the raw data.
Google Sheets Slicer

Charts

Charts are one of the best visualization features to transform your data into meaningful insights.

Also, charts are great for accurately representing information since you will be able to easily draw out important patterns.

Follow these steps to incorporate charts into your Google spreadsheet:

  • Press ‘Insert’ and then click on ‘Chart’ in the main heading bar.
  • A blank chart will appear and you will see a sidebar next to it. In the sidebar, you will have different options for customization.
  • Select your ‘Data Range’ in the sidebar and use ‘Chart Type’ to choose a type that fits your spreadsheet.
  • Lastly, to alter the parameters of the chart, you should go to the ‘Customize’ tab in the editor.

Sparkline

While charts can be immensely useful when presenting important metrics, they aren’t exactly the best fit for spreadsheets. This is because they take up too much space and cover up cells that include crucial information.

For this reason, you might want to choose Sparkline as an alternative.

Sparkline solves the issue since it essentially adds a chart into one individual cell. It is most commonly used for showcasing how the numbers are progressing (seasonal increases/decreases, economic cycles, etc.).

You can either choose a line chart, column chart, bar chart, or win-loss chart.

Adding a Sparkline is pretty simple, all you have to do is type in this formula: =SPARKLINE(data, [options]).

How to Build a Dynamic Dashboard In Google Sheets

Dynamic dashboards are advanced dashboards in which data automatically updates in real-time, as soon as any changes occur.

They are also known as interactive Google sheets dashboards because you can alter and reorganize the reports rather quickly.

In essence, dynamic dashboards are the same as static dashboards, except they have ‘superpowers’. A static dashboard only showcases a fixed set of metrics and data, while dynamic dashboards update them in real-time.

Building a dynamic dashboard in Google sheets is also a bit more complicated than creating a normal dashboard.

You will have to organize the data differently, learn additional functions, create new data tabs, and use other methods (e.g. Data validation).

Free dashboard setup featured section

Prebuilt Google Sheets Dashboard Templates

If you think that making a dashboard in Google Sheets is going to take away too much of your time, you might be interested in checking out a free Google sheets dashboard template.

Templates can be a great starting point since they will already have a set foundation on what your dashboard will look like.

They can be a great alternative if you’re running short on time and can’t afford to create one from scratch.

There are a few great dashboard template options in Google Sheets, and you can find them on the main screen. You can find options on anything from a Google sheets analytics dashboard to an executive dashboard template.

Google Sheets Dashboard Templates

To find these templates, simply go to ‘File’ and then click on ‘Make a Copy’.

find Google Sheets templates

How to Create a Google Sheets Dashboard in Databox

With project management tools revolutionizing the way in which spreadsheets and dashboards are created, there aren’t many advantages to utilizing Google Sheets anymore.

Limited automation, integration, and not enough comprehensiveness are just some of the issues that users are having with Google sheets.

Databox can be the answer to these issues. 

Aside from having a bunch of new useful features, Databox also makes the process much less time-consuming.

When creating a dashboard in Google sheets, you might end up spending hours getting the hang of the different functions and utilizing the visualization tools. With Databox, this process lasts literally minutes.

Don’t believe us? Check out this three-step guide to see how easy it is.

Step 1: Connect Google Sheets

In the main menu of Databox, click on ‘Databoards’ to begin the process.

On the left side, a panel will appear and you can find the ‘Google Sheets’ source there. By clicking on it, all the raw data from your spreadsheet will be transferred to Databox.

Connect Google Sheets

Next, choose the specific Sheet you want to pull data from and press Continue. 

Connect your data with Google Sheets Wizard in Databox

Once you have selected your preferred sheet, the data will appear on your screen with each column defined. Now it’s time to begin creating a custom metric.

Step 2: Create a custom metric

Once you connect the raw data from your Google sheet, it’s time to create a custom metric.

To make the Google sheet integration even better, Databox uses the Query Builder tool which provides a smooth visualization experience.

It simplifies the visualization process since everything you want to do will only be a few clicks away, and you won’t have to spend time learning query language or how to write lines of code.

Select your metric in Google Sheets Wizard in Databox

You can also add finishing touches to your custom metric. Start with naming your metric, then choose the data aggregation option that works best for the values chosen.

Name your metric in Google Sheets Wizard in Databox

Step 3: Drag and drop your custom metric onto a dashboard

After you customize your new metric, you can locate it on the left side of the panel.

All you have to do now is drag and drop the metric onto the dashboard and that’s it.

Simple as that.

Google Sheets Dashboard Examples

In case you are dealing with a handful of important metrics and KPIs, the templates from Google sheets may not be enough to cover everything you need.

If that’s so, you might be interested in checking out these Databox dashboard examples. We can build any of these for you for free.

Financial Health Dashboard Example

The Financial Health Report Example helps you understand your numbers much more clearly, which later results in better financial decisions.

The questions this dashboard can answer are:

  • How much is the revenue growth?
  • Am I doing well with customer acquisition?
  • How much does the customer acquisition cost?
  • How much are the general and administrative costs?

Once you know the answers to these, staying on top of your company’s finances will be a piece of cake.

What’s more, the financial metrics can be transferred to a Google spreadsheet where you can then connect data from the other business systems your company incorporates.

Financial Health Dashboard Example

Sales Summary Dashboard Example

To keep track and monitor data from your company’s different sectors (marketing, HR, sales, accounting, etc.), you can use the sales dashboard example.

While you probably already have team leaders in charge of meeting the quotas, you will want to be informed of all the latest changes as soon as they occur.

However, building a dashboard for each individual department is simply too time-consuming.

This is where the comprehensiveness of the summary dashboard comes in handy, you will gain an overview of all the key metrics from each department in one place.

Additionally, by visualizing these metrics, you will make the performance KPIs much more understandable to the key stakeholders in your company.

Sector Dashboard Example

SMB Employee Engagement Performance Dashboard Example

No matter how reliable your employees may be, you should still track their progress towards achieving the overall business objectives.

The best way to do this is through the HR dashboard example. 

This comprehensive dashboard provides you with valuable insights into what exactly is going on among the ranks and you can use it to capture all the important employee engagement data in one place.

Not only that, but you can also use it as a place to track employee sentiment and identify the biggest issues that need to be quickly resolved.

Once you acquire all of this data, you and the other team leaders can use it to make better data-based decisions and create finer employee engagement plans for the following period.

HR Dashboard Example

Let Databox Build You a Free Google Sheets Dashboard

Building a dashboard in Google sheets might seem easy at first, but once you roll up your sleeves and start the work, it turns out to be a much more grueling process than you anticipated.

You have to pile up a ton of raw data to begin the process, filter out the exact metrics that you want to track, figure out how the various functions and formulas work, and then learn how to visualize the data through charts and graphs.

This can end up taking a huge amount of time, something that busy executives generally lack.

So why go through all this hassle if you don’t have to?

With Databox, you can sit back and enjoy while our team does the heavy lifting.

setup-free-dashboard-databox-dsss

All you have to do is connect with someone from our team and explain which data you want to include in the dashboard. We’ll do the rest.

We can set up your first dashboard in less than 24 hours and highlight some of the key functions that our customers usually use for performance analysis.

In case you aren’t sure which metrics should be included in the dashboard, don’t worry, we can also help you determine these KPIs based on your company’s overall goals.

Don’t have time to prepare a dashboard for your next meeting? Sign up for our free dashboard setup and leave a lasting impression on your key stakeholders.

About the author
Filip Stojanovic
Filip Stojanovic Filip Stojanovic is a content writer who studies Business and Political Sciences. Also, I am a huge tennis enthusiast. Although my dream is to win a Grand Slam, working as a content writer is also interesting.
You may also like...
Read more

10+ Popular Business Analysis Techniques Companies Use in Their Strategic Plans

50+ companies share the business analysis techniques they found to be most valuable in their strategic planning.

Analytics   |  Sep 28

Read more

Which Comes First: The Goal Or The Strategy?

We surveyed 91 companies about whether they set the goal or the strategy first. They shared their thoughts and tips on what’s more important and how to ensure both align in the best way.

Analytics   |  Sep 22