How to Build a Dynamic Dashboard in Google Sheets: Is There an Easier Way to Do it

Cem Ruso
Cem Ruso
September 13, 2022
November 14, 2022

Google Sheets is undoubtedly a great way to create statistical dashboards and track KPIs. 

The Google Sheets charts and graphs provide a clear overview of KPIs and help you and your team make data-driven decisions.

Creating a dynamic dashboard in Google Sheets is easy and similar to Excel dashboard creation, but a lot of manual procedures go into its making.

In this article, you will discover what a dynamic dashboard is, how to build one in Google Sheets, its limitations, and an alternative to track your KPIs better.

But let’s first understand the basics.

What is a Dynamic Dashboard in Google Sheets? 

A dynamic dashboard is an interactive dashboard that you can access and make changes in real-time.

Such dashboards are required in time-sensitive situations or are great for companies whose KPIs change frequently and must act upon them immediately.

You can add, remove, or edit any data from the dynamic dashboard and it reflects the changes in the front-end immediately without much delay.

Creating a dynamic dashboard with Google Sheets is a tricky yet effective process. There are 6 steps to creating a dynamic dashboard in Google Sheets which we will be discussing later.

One of the best examples of a dynamic dashboard is the dashboard you see in any car. 🚘

dynamic dashboard car example

You can see how the speed fluctuates when you increase or decrease the car speed, you can see the distance traveled increasing as you keep traveling, the fuel indicator lets you know about the amount of fuel you have left, and many more KPIs.

But a car dashboard is a more advanced dynamic dashboard; creating such dashboards on Google Sheets is a tough and lengthy process.

Benefits of Using a Dynamic Dashboard to Track KPIs

  • Lets You Make Real-Time Changes: As mentioned earlier, you and your team can make all the changes a KPIs needs in a dynamic dashboard’s back-end, and it will reflect immediately in the front-end of the dashboard.
  • Easy to Edit: With dynamic dashboards in Google Sheets, you and your team can make changes easily. Via strong data visualization features, you can transform your raw data into charts immediately.
  • Drill Down on Insights: The best benefit of using a dynamic dashboard instead of a static dashboard is that when you want to know what’s the reason behind the fall of a KPI, you can click on its graph and access all the live data. This will help you and your team identify the problem and rectify it instantly.

Now that you know what a dynamic dashboard is in Google Sheets let's look at a tutorial you need to follow to create one.

How to Build a Dynamic Dashboard in Google Sheets - Step-by-Step

Step 1: Import or Collect Data to Analyze

The very first step in dynamic dashboard creation is to compile all the KPI-related data you want to track.

You can use a Google Form to collect data manually or import data from a data source like Google Analytics or as a CSV file.

If you choose to go by the route of Google Form, you can automatically convert the form into a Google Sheet and use the data to create a dynamic dashboard.

Here’s an image of a small set of data we will be using as an example to create a dynamic dashboard in Google Sheets.

import or collect data for dynamic dashboard

Step 2: Elements You Should Know About When Creating Dynamic Dashboards

When creating a dynamic dashboard in Google Sheets, there are several elements that you will use. Let’s first understand the functionality of these elements before creating a dashboard.

#1. Pivot Table

A pivot table gives you the summary of a large database you already created in Google Sheets. It helps you condense the data into aggregated form to extract and find the specific data range or sets you want.

For example, if you have an ecommerce store with multiple products.

A pivot table can help you identify the total revenue generated by a certain product in a certain location.

Essentially, pivot tables give you a birds-eye view of your raw data and help you understand it better.

With the current dataset (you can see the image in Step 1), let's say we want to know which sales channels were used the most over the year.

You can do that via a pivot table; here’s how:

create pivot table for sheet

Note: You can use Google Sheets formulas to derive insights like the example above, but pivot tables are much faster and easier to use. It also reduces the chances of human error and data inaccuracies.

#2. Charts

Charts are the most prominent feature of Google Sheets that you will be using when creating a dynamic dashboard.

They help you quickly analyze a large set of data via visual representation.

You can make charts from raw data as well as pivot tables as long as it has an X and a Y axis.

Here’s an example of a bar chart we created from our raw data.

create chart in google sheets

One great thing about these charts is that you can change your raw data and see the change in the chart immediately.

sheets change raw data on bar chart

#3. Slicer

The slicer function of Google Sheets lets you ‘slice away’ data from a dataset, so you can focus on what’s important. 

You can use the slicer feature for pivot tables, and they're also essential to keep control of the charts and graphs in your dynamic dashboard.

Here’s a little sneak-a-peek into the Slicer function. Setting the slicer up just takes seconds, and it can be a real help when you have a large dataset to analyze.

google sheets slicing feature

#4. Lookup Formulas

Lookup formulas help you find a specific value in a large set of data.

There are two types of Lookups:

  1. Vertical Lookup (VLOOKUP): Finds the key value in the first column of a range that you define and returns a value from another column that’s in the same row.
  2. Horizontal Lookup (HLOOKUP): Same as vertical lookup, just horizontally.

Here’s an example of VLOOKUP in action:

vlookup formula google sheets

The formula for VLOOKUP in Google Sheets goes like this:

=VLOOKUP(search_key, range, index, [is_sorter])

Where,

Search Key is the value of the search in the column.

Range is the upper and lower values to be considered for the search.

Index is the column number to search for value in (must be a positive integer).

Is_sorter is an optional input that you can choose:

Type true if you’ve sorted your data in any way,

And false if it’s unsorted.

#5. Sparkline

Sparkline helps you create a small line graph without any axes instantly. It can be helpful to analyze a data trend instantly.

Here’s how to make use sparkline formula on Google Sheets:

create sparkline google sheets

#6. Conditional Formatting

With conditional formatting, you can sort data in ascending or descending order, assign a color schema to data, and hide data that you don’t need.

It’s quite helpful when you have a specific goal assigned for a specific range of values.

The best use of conditional formatting is done when someone is doing keyword research.

One important metric to track when doing keyword research is keyword difficulty. 

With conditional formatting, you can set color schemes for different keywords.

One such color scheme could be:

  • Green for keywords with difficulty 0 to 29
  • Yellow for keywords with difficulty  30 to 59
  • Red for keywords with difficulty 60+

 

conditional formatting google sheets

Step 3: Create Chart/Tables For Your Raw Data

When it comes to dynamic dashboards in Google Sheets, charts and tables are your only option to track KPIs.

We say only, but they’re enough to track every KPI you can think of.

Let’s see how you can create a table to track KPIs via a dynamic dashboard.

From the raw data example, if you want to track the number of new users per month. You can select both columns and create a pivot table as we did in Step 2.

The same is true for charts.

With Google Sheets, you can create 8 types of charts:

  1. Line charts
  2. Column (Bar) charts
  3. Combos
  4. Area charts
  5. Pie charts
  6. Scatter diagram
  7. Maps
  8. Miscellaneous charts
create chart or table on google sheets

Step 4: Use Data Validation Feature

The data validation feature of Google Sheets is used to ensure that you only input a value in a column that's valid.

Let us explain it to you with an example.

Suppose there are 5 sales channels - A, B, C, D, and E, and we want to add those to our raw data sheet.

Data validation is the best way to ensure that you or your teammates don't add any other option in any of the cells apart from the 5 channels we mentioned above.

To do so, select the column you want to validate.

data validation on google sheets

Go to Data in the menu and click on ‘Data Validation.’

You will see a data validation window open, like the one below.

data validation google sheets

In the Criteria option, change ‘List from a range’ to ‘List of items’ and write the five channel names separated by commas.

data validation choose list of items

Click on Save after you’re done.

You will now see a drop-down in the column named ‘Sales Channel’ from which you can select the channel for each month.

data validation feature

Step 5: Formatting and Customization

Formatting and customization can be done for both raw data and the charts you make to track KPIs via a dynamic dashboard.

For formatting the raw data, you can use conditional formatting. You can see how to do it in Step 2 of this guide.

When it comes to formatting charts, Google Sheets has an array of features.

sheets formatting and customization

Here’s what you can do with the formatting options for charts:

  • Select the graph type you want for your KPI from multiple options
  • Change the color and font of the chart
  • Change the axes titles or flip the axes if required
  • Insert error bars, data labels, or trend lines if needed
  • Introduce a legend axis
  • Enables gridlines and ticks on the charts 

Step 6: Convert all the Charts into a Dynamic Dashboard

To make a dynamic dashboard in Google Sheets, you must publish the charts onto a webpage.

To do so, open a new sub sheet and copy and paste all the charts on it.

convert charts to dynamic dashboards

After that, go to File > Share > Publish to Web.

publish charts

A small window will pop up on the screen. 

Instead of the ‘Entire document’ option, choose ‘All Charts’ from the drop-down menu and click Publish. Copy the link and paste it into a new Google tab.

sheets publish dashboard to public

Here’s your dynamic dashboard that you can share with your teammates.

Drawbacks of Creating Dynamic Dashboards in Google Sheets 

#1. Your Dashboard Can Easily be Ruined by Someone Else

Keeping track of business KPIs via a dynamic dashboard on Google Sheets is great if you’re an individual doing all the heavy lifting. But if you have a team to help you out track KPIs, Google Sheets can be your new nightmare.

Especially if some teammates of yours are complete beginners to KPI tracking. 

They might change a few things in a dashboard to test the waters, and there are chances it'll completely ruin your dashboard.

Also, there's always a risk of sharing the sheet accidentally with someone outside your organization, leading to data leaks.

#2. No Mobility

The mobile experience of Google Sheets isn't great; you have to scroll both horizontally and vertically to look at your KPIs. Apart from this, it's also very tough to edit something on Google Sheets via a mobile phone.

Our suggestion is, don’t rely too much on Google Sheets for your dashboards when you need quick access.

What happens if you need to check specific data in your investor meeting when they ask you to?

You can’t just sit there firing up a laptop and taking your own time to give them an answer.

#3. It’s Hard to Maintain a Dashboard on Sheets

Google Sheets dynamic dashboards are a decent option for just getting started with KPI tracking, which means that it's great to track a handful of KPIs (5 or 6). 

But due to a lot of manual procedures, tracking multiple KPIs will take a lot of time. 

When your company grows, you will have multiple departments tracking a bunch of KPIs separately.

Maintaining Google Sheets dashboards is tough at that point in time, and also, with so much data loaded in the Google Sheets, it becomes slow.

There might come a point where the time taken by the dashboard to load will be more than the time required for you and your team to track KPIs, and you can't afford such a waste of time when you have to track KPIs almost every day.

Solution: Keep Track of All Your Key Metrics in One Place for Free with Datapad

With the above dynamic dashboard in Google Sheets, it's quite evident that you can't track KPIs on the go.

But there must be some app that lets you do it, right? 🤔

And there is!

datapad homepage

We’ve created Datapad - a free-to-use mobile-first software (available for both iOS and Android) that helps you keep all your KPIs in your pocket and in one place.

With our integration with Google Sheets, you can track your raw data with ease.

Let us show you how you can build a dynamic dashboard in Datapad for free.

Step 1: Sign Up and Create a Dashboard

Creating a dashboard on Datapad is a piece of cake. Just click on the plus sign you see on the top right corner of the app, insert the title and description of your dashboard, give it an emoji if you like, and click on create. That’s it!

Here’s a GIF for you to understand the process better:

datapad team communication feature

Step 2: Create Raw Data on Google Sheets and Integrate with Datapad

If you want to track any Google Sheets data on Datapad, you can do it via our one-click integration.

To do so, first create raw data in Google Sheets, as mentioned earlier. Go to our app and click the ‘+’ sign you see at the bottom of the app.

Select the option ‘Use a Data Source’ and tap on Google Sheets. This helps you automate data importation.

Log in to the Gmail account you want to add the Google Sheet from and select the sheet.

Our app also lets you select the sub-sheets and columns you want to take data from and create charts or tables for the same.

You can even customize the color of these graphs to match your brand’s color scheme. 

Datapad google sheets integration

What more can you ask, right?

Step 3: Create Multiple Charts in Minutes And Add Them to Your Dashboard

You can create multiple charts and graphs from the process mentioned in step 2 and use multiple sheets for the same. 

It doesn't matter if your data is scattered across multiple Google sheets; Datapad lets you track all the Google Sheets-related KPIs in one place.

datapad google sheets dashboard

Apart from Google Sheets integration, Datapad also resolves the issues we discussed about the drawbacks of creating dynamic dashboards in Google Sheets.

Note: Apart from Google Sheets, our app has multiple add-ons that you can use to import data in seconds.

Here’s how:

Feature #1. Team Onboarding with Datapad

Datapad lets you share dashboards and onboard your team on the app so you can track KPIs along with your team.

It's a no-brainer that the number of KPIs you need to track increases significantly along with data history when you expand your company. It's not a one-person job anymore.

You will have department-specific KPIs for which you will need experts to track and analyze.

With Datapad, you can onboard your team in seconds and assign an individual dashboard to every team member.

datapad team communication

With Datapad’s team onboarding feature, you can:

  • Onboard your team in seconds
  • Track department-specific KPIs from anywhere in the world
  • Assign each team member a specific dashboard to track specific business-related KPIs they think they must know about

Feature #2. Mobile-First Software

Datapad is a mobile-compatible app that you can use on any size of device, it doesn't matter if it’s an iPhone or an iPad.

You can build, customize, and analyze data on Datapad from your mobile devices with ease; all you need is a stable internet connection.

Our app is meant for small business owners and managers who’d want to track KPIs daily but simply don’t have hours to analyze them. Datapad lets you search KPI and have a look at it in seconds instead of hours.

datapad search your metrics feature

With Datapad’s mobile-first approach, you can:

  • Track KPIs on the go from anywhere in the world
  • Search for a specific KPI in the app to track them immediately

Feature #3. Communicate with Your Teammates

Datapad lets you communicate with your teammates around every KPI via a real-time comment section present below each KPI you track.

This helps you drill down the issue in-depth when a certain KPI underperforms and rectify it immediately.

datapad commenting feture

Not just this, but you also get push notifications and alters when a KPI goes beyond your set goal.

With Datapad’s communication feature, you can:

  • Talk to your teammate around KPIs in real-time
  • Get push notifications and alters wherever a KPI goes beyond a set threshold
  • Set goals for individual KPIs and analyze them better

You can use our app for free. Just sign up to our platform and track KPIs on dynamic dashboards hassle-free.