How to Build a Dynamic Dashboard in Google Sheets in 6 Easy Steps

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.

• Step 1: Preparing Raw Data
• Step 2: The Essential Google Sheets Functions You Need
• Step 3: Creating a New Tab to Hold Data
• Step 4: Using Google Sheets Functions
• Step 5: Using Data Validation to Make Your Dashboard
• Step 6: Passing Data Validation Values into the Filter Function
• How to Create a Dynamic Google Sheets Dashboard in Databox?
• Create a Dynamic Dashboard for Your Google Sheets Data in Databox

Step 1: Preparing Raw Data

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:

• Day and time categories. Create a day-of-sale category (e.g. Monday) and an hour-by-hour category (e.g. 7 pm to 8 pm).
• Timezone category. Assuming that not all of the buyers will come from the same country, we will have to establish the local time in which they decide to purchase a product. This will allow you to make global comparisons.
• Sales channel category. This category will provide you with a clear overview of where each sale in the spreadsheet is coming from.
• Buyer country category. Since you probably won’t be able to fit every single country in the spreadsheet, you can use columns for some of the major ones like the US, the UK, and Canada, and place the smaller countries in a ‘Rest of the World’ column.

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.

Step 2: The Essential Google Sheets Functions You Need

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:

• VLOOKUP – The vertical lookup function allows you to track a key value in a specific column of a defined range. It’s essentially the best way to search for a unique identifier in your spreadsheet.
• SUM – Used for summing up all the data into one specific column.
• SUMIF – Works the same as SUM, but only if the data fulfills a certain condition.
• AVERAGE – Finds the average value of data in one specific column.
• COUNT – Counts how many cells contain a number.
• Sparkline – You can use this function to create a dynamic graph that showcases the metric’s progress over time.
• ARRAY_CONSTRAIN – This formula provides a subset based on the input range, using the rows and columns that you specify. There are three subfunctions of ARRAY_CONSTRAIN – input_range, num_rows, and num_cols.
• FILTER – As the name suggests, this function allows you to filter out your data sets. For additional precision, you use the subfunctions range, condition1, and condition2.

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

Step 3: Creating a New Tab to Hold Data

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.

Step 4: Using Google Sheets Functions

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:

=array_constrain(‘Data Only’!A1:AE1,1,99)

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

Step 5: Using Data Validation to Make Your Dashboard

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.

Step 6: Passing Data Validation Values into the Filter Function

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.

How to Create a Dynamic Google Sheets Dashboard in Databox?

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.

• Step 1: Connect Google Sheets
• Step 2: Create a custom metric
• Step 3: Drag and drop your custom metric onto a dashboard

Step 1: Connect Google Sheets

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.

Step 2: Create a custom metric

After you have finished connecting your Google sheet to Databox, you can proceed to create your first custom metric.

Databox uses the Metric Builder tool to power the Google sheet integration. With the help of Metric 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.

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

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?

Build Performance Dashboard from Your Spreadsheet Data for Free

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.