on June 20, 2022 (last modified on June 21, 2022) • 11 minute read
Static dashboards and Google spreadsheets are undoubtedly one of the best ways for busy executives to keep their data organized.
Google sheets dashboards provide a clear overview of information and you can quickly check out any specific metrics that you might need at a moment’s notice.
However, with new data constantly piling up, they have to be updated rather often.
This process can easily be avoided by creating dynamic dashboards in Google sheets.
Google sheets dynamic dashboard is a type of dashboard that automatically updates the included data as soon as any real-time changes occur.
This allows you to quickly identify new trends and opportunities, analyze problematic areas, and resolve any issues that occur in a timely manner.
Below, we will provide you with a detailed step-by-step guide that you can follow to build a dynamic dashboard in Google sheets.
To begin building a dynamic dashboard, you first have to connect the raw data to a Google spreadsheet and start preparing and categorizing it.
After uploading each data source, we recommend that you run a pivot table report to get a better overview of the data. This helps you identify any potential anomalies (incomplete gaps) and get familiar with the data you will be using.
In most cases, the raw data will represent an abundance of information. For example, if you are building an online sales overview report, you will have a lot of data regarding customer activity, such as the location from where they’ve been buying your products, and the channels they have used to buy them. Without organizing it, this information won’t be of much use.
Now, let’s assume that your eCommerce website has five sales channels, including the official website, affiliates, and paid ads.
Naturally, we will have to organize this data accordingly to try and transform it into something a bit more meaningful.
Here are some of the categories that you can use:
Okay, so now we have connected the data from each source, checked if everything is in order through pivot tables, and categorized it.
Time to move on to the next step.
If you have any sort of experience with Excel, then you are probably already somewhat familiar with the use of Google Sheets functions.
These functions work pretty much the same both in Excel and Google Sheets.
Here are some of the most important ones you’ll need to create a dynamic dashboard:
Once you learn how to use these functions in your Google sheets, you will be able to easily manipulate the data in your dynamic dashboard.
Related: 40 Advanced Google Sheets Tips for Marketing Pros
For our next step, we recommend that you create an entirely new tab so that there is a temporary place where your dashboard data can be stored.
You won’t have to do this each time – it’s just easier to understand the process through it.
Time to use the Google sheets functions we mentioned earlier.
To start, go to your new tab and organize the column names by using this function:
The ‘Data Only’ refers to the name of your Google sheet, ‘A1:AE1’ presents the first row of data, and ‘1/99’ is the number of rows and columns.
Next, use the FILTER function by selecting a specific cell and setting up the minimum amounts of inputs. You need two inputs – the data location and the filtering item.
This is what it should look like:
=filter(‘Data Only’!A1:AE242,’Data Only’!C1:C242>=date(2022,1,1))
The ARRAY_CONSTRAIN and FILTER functions provide you with the most possibilities when it comes to designing your dynamic dashboard.
To learn your way around them, it’s best to test out different variations and see how they work.
Related: 13 Ideas on How to Use Google Sheets In Your Business
If you have gotten this far, then you’ve probably gotten the hang of manipulating your spreadsheet data and using the different functions.
Now, let’s see how you can make your dashboard using the data validation method.
By using data validation, you will acquire a drop-down menu that can be used for selecting a certain parameter (in our case, a sales channel or a specific time).
Once you connect the data to the parameter, it will automatically update and you won’t have to bother with writing any lines of code.
While data validation isn’t the only method you can use, it stands out as the simplest and most powerful.
Firstly, create a new tab once more and pick a name for it (e.g. Dashboard).
Then, proceed with selecting a specific date range. The great thing about data validation is that you can manually select the date range, there won’t be any particular pre-set range that you’ll have to follow.
After you finish setting up the data validation selectors, we can move on to the ‘dynamic’ part of the dashboard.
Not sure which metrics to track or dashboards to build from your spreadsheet data? Have old reports you want to recreate in Databox? Share your dashboard needs with one of Databox’s product experts and we’ll build you a customized Google Sheets dashboard for free.
Here is an example of what your dashboard can look like (just imagine your data populating here)…
Whether you’ve exported data into a spreadsheet, entered data manually into one, or have a more complex database setup, Databox can be used to create an easy-to-read and easy-to-share visualization of your unique data.
Learn more about our free Google Sheets Dashboard setup here, reach out for assistance via email or chat, or get started immediately by following the steps below:
Lastly, to finalize your dynamic dashboard, you should pass the data validation values into the filter function.
The best thing about Google Sheets is that you won’t need to use a macro or pivot tables to do this (unlike in Excel dashboards, for example).
If you return to the filter functions you used in your new tab (Step 3), you can now connect the data validation values in form of filter inputs.
This is what it’s supposed to look like:
=filter(DATASET, DATA:COLUMN 1 (Logical expression) Dashboard Data Validation Field 1, DATA:COLUMN 1 (Logical expression) Dashboard Data Validation Field 2)
By using some simple operators (e.g. <, >, or =), you use input from both the ‘from’ field and the ‘to’ field, essentially filtering the data field twice.
Once you have all the filters set up, you can proceed to the testing process to check whether the data changes in the new tab you created.
If everything is going smoothly, then your dynamic data selection process is over.
Your final stop will be setting up the pies and charts to make your dynamic dashboard aesthetically pleasing. Since this part depends on your creativity and preference, we won’t be explaining it in a separate step.
Let’s face it, Google sheets is a bit outdated.
Business tools are rapidly evolving month after month, and with all the new features being released, there is no way for Gsheets to compare anymore.
Aside from the limited automation and consolidation, creating a dynamic Google Sheet dashboard will end up being far too time-consuming. What might take you hours in Google Sheets can easily be done in minutes using an advanced business dashboards tool.
And that’s where Databox comes in.
Here is a three-step guide that explains just how easy creating a dynamic Google sheet dashboard is in Databox.
Once you open up your Databox web app, go to ‘Databoards’ to create a new dashboard.
You will see a panel on the left side where you can find the ‘Google Sheets’ data source. When you click on it, it will start connecting the Google sheet to Databox.
Now, find and connect your spreadsheet to visualize data from it.
After you have finished connecting your Google sheet to Databox, you can proceed to create your first custom metric.
Databox uses the Query Builder tool to power the Google sheet integration. With the help of Query Builder, even users that don’t have any type of technical experience can visualize data simply by making a few clicks on the interface.
This makes the process immensely easier since you won’t have to spend time learning some type of query language or even know how to write a single line of code.
You will only be a few clicks away from extracting custom metrics from your spreadsheets.
Add finishing touches to your custom metric — from naming it and choosing the data aggregation option that works best for you, to selecting your trending preferences and the number format.
After that, all that is left is to add this metric to your dashboard. Simply drag it into your dashboard and drop it in any place you deem fit.
That’s it! Easy, right?
For the longest time, Google Sheets has been considered to be the epitome of spreadsheet tools.
However, these days are long past us, and dashboard tools have taken over the spreadsheet business.
While it can still be pretty useful for editing and organizing some smaller amounts of data, Google sheets simply don’t cut it anymore when it comes to creating a dynamic dashboard in a timely manner.
Sure, there are built-in formulas, pivot tables, conditional formatting, and a whole bunch of other features, but using them isn’t exactly the easiest task.
From creating new tabs and categorizing data to figuring out how exactly Google sheet functions work, you will end up losing both your time and nerves.
This is why simplifying the process through Databox can be a lifesaver. Or better yet, a timesaver.
By using Databox, you will be able to connect your Google sheet in a matter of seconds and start customizing it through drag-and-drop features, instead of going through the hassle of using complex functions and formulas.
Not only that, but the process of visualizing your key data through graphs and charts will go from ‘hassle’ to ‘fun’.
However, if you don’t have time for that either, don’t worry – you can hire our team to do it.
All you have to do is connect your data and create a dashboard wishlist, and we will take care of the rest.
Want to save up valuable time and still create a killer dynamic dashboard? Sign up here for a free trial.
| Sep 29
| Sep 28
| Sep 22
Latest from our blog
Popular Blog Posts
POPULAR DASHBOARD EXAMPLES & TEMPLATES