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.
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. 🚘
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.
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.
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.
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.
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:
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.
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.
One great thing about these charts is that you can change your raw data and see the change in the chart immediately.
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.
Lookup formulas help you find a specific value in a large set of data.
There are two types of Lookups:
Here’s an example of VLOOKUP in action:
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.
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:
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:
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:
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.
Go to Data in the menu and click on ‘Data Validation.’
You will see a data validation window open, like the one below.
In the Criteria option, change ‘List from a range’ to ‘List of items’ and write the five channel names separated by commas.
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.
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.
Here’s what you can do with the formatting options for charts:
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.
After that, go to File > Share > Publish to Web.
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.
Here’s your dynamic dashboard that you can share with your teammates.
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.
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.
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.
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!
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.
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:
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.
What more can you ask, right?
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.
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:
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.
With Datapad’s team onboarding feature, you can:
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.
With Datapad’s mobile-first approach, you can:
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.
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:
You can use our app for free. Just sign up to our platform and track KPIs on dynamic dashboards hassle-free.
How to Create an Excel Dashboard in 2023 (Free Templates Included): Learn how to create dashboards in excel, drawbacks of using it and many more.
How to Make a Pie Chart in Google Sheets? [Secret of Creating Stunning Charts]: You will learn about how to make a stunning pie chart on sheets and also discover a better alternative for it
How to Make a Chart in Google Sheets: Easiest Method Explained Here: Find out about the easiest method of creating stunning charts on sheets.
