7 Easy Steps to Build a Dynamic Dashboards in Google Sheets

K.I.T.T. Dashboard - Knight Rider
(not sure how the driver wasn't constantly distracted by all gadgets and gizmos)

Ah, vehicle dashboards, we all awe at their gauges, numbers, colors and pretty flashing lights. However  their main purpose is to deliver important information while not distracting the driver from driving. Usually this includes information about the car's velocity(mph, kmph), the engine's power quantity (rpms)  and a myriad of car diagnostic indicators such as the fuel volume indicator, engine temperature meter, or the oil pressure gauge.  One thing is certain some or all these indicators will change depending on what actions the driver takes, thereby the dashboard being reactionary to the driver. 


In the analytics industry, the term 'dashboard' has evolved into BI or business intelligence tools. Where the traditional dashboard was just a snapshot of current business performance, BI tools enabled users to select filters and customize the report to how they see fit and are usually feed into a big data source like HIVE. The dynamic dashboard is where we can use a manageable amount of data and have the performance of a BI tool. 

This post will focus on building a dynamic dashboard via Google Sheets. 


STEP 1 - Get Your Data Ready


Our fictional client "DynaCost" is a brick and mortar store that has 2 locations, one in New York and one in Chicago. They also have an online eCommerce site that will be the focus of this post. 

Getting started, the best way for this to work is to have all your source data consolidated on 1 sheet. For this exercise I'll be calling this tab 'Data Only'.

Data should be in tabular form:

STEP  2 - Introducing you to Google Sheets Functions

If you ever worked in Excel then you probably familiar with functions. They are usually start with the = sign. It is entirely similar to using functions in Google Sheets. some popular functions are:

SUM
COUNT
MONTH

The 2 key functions we'll need are:

ARRAY_CONSTRAIN(input_range, num_rows, num_cols)

• input_range - The range to constrain.
• num_rows - The number of rows the result should contain.
• num_cols - The number of columns the result should contain

• Generally used in combination with other functions that return an array result when a fewer number of rows or columns are desired.

AND

FILTER(range, condition1, [condition2, ...])

• range - The data to be filtered.
• condition1 - A column or row containing true or false values corresponding to the first column or row of range, or an array formula evaluating to true or false.
• condition2 ... - [ OPTIONAL ] - Additional rows or columns containing boolean values TRUE or FALSE indicating whether the corresponding row or column in range should pass through FILTER. Can also contain array formula expressions which evaluate to such rows or columns. All conditions must be of the same type (row or column). Mixing row conditions and column conditions is not permitted.
  • condition arguments must have exactly the same length as range.

 These 2 functions will help you obtain and manipulate the data needed for your dashboard


STEP  3 - Create a new tab to hold data

I recommend you create a new tab so that we can set up a temp location on where your dashboard data will rely on. In my sheet I created a new tab called DCD (Dynamic Content Data). 


STEP  4 - Using those new functions!

Within this new tab I first set up my column names by using 

=array_constrain('Data Only'!A1:AE1,1,99). 

Here is where I'm selecting only the first row of my data (A1:AE1) and then assigning 1 to the number of rows and 99 to the number of columns. 

Next with the FILTER function, go to B1 (or any other cell) and just put in the minimum amount of inputs . Remember the filter function needs 2 inputs , the data location as the first value, and the item you are filtering on as the second, for this example, we'll do this:

=filter('Data Only'!A1:AE242,'Data Only'!C1:C242>=date(2015,1,1))

You should get back some subset of your data with the filter criteria already selected. The beautiful thing about filter is that you can use many combinations of filters so something like this is possible:

=filter('Data Only'!A1:AE242,'Data Only'!C1:C242>=date(2015,1,1),'Data Only'!C1:C242<=date(2015,2,1))

This will return the subset of data with dates between 1/1/2015 and 2/1/2015. You may have noticed that your heading row is now gone since the addition of the 2nd filter criteria. This is why it was important to put in the array_constrain which will remain consistent throughout the whole filtering process.

I highly recommend playing with these functions (the filter one especially) in order to get familiar and comfortable with them.

STEP  5 - Making the Dashboard & Data Validation

Oh boy! The fun part! Now that we know how to manipulate the data, we can go ahead and start thinking about how dashboard is going to look and feel.

Create a new tab and call it "dashboard' (or use whatever name you like)

My recommendation is always put the filters close to the top. So users know what is actionable. In addition, its VITAL to understand the concept of Data Validations and drop downs. If you unsure of this you can easily follow along in the tutorial here.

What I like to do is start off by selecting date ranges, it usually infuriates me that data is preset to a particular range that I have to aby. If my data is in daily format, then I should be able to select the date range manually, like so:

Having both a "from" and "to" date selector with a calendar data validation drop down. 

In addition, having a selector for a categorical value would be beneficial to a dashboard as well. In this example we'll use the 'city' drop down.

Now that we have our data validation selectors that are relevant to our data set we wish to filter on. We can now proceed to the dynamic portion of the dashboard.

STEP  6 - MAGIC! Pass data validation values into the filter() function. 

Here is where things get interesting and amazing. I found that this is only possible to do in Google Sheets without the of a macro or a pivot.

Going back to the filter function in the "DCD" tab, we can now use the values from the data validation as inputs to the filter. For instance:

=filter(DATASET, DATA:COLUMN 1 (Logical expression) Dashboard Data Validation Field 1, DATA:COLUMN 1 (Logical expression) Dashboard Data Validation Field 2)

In my example, I use the following:

=filter('Data Only'!A1:AE242,'Data Only'!C1:C242>=Dashboard!A6,'Data Only'!C1:C242<=Dashboard!B6,'Data Only'!AD1:AD242=Dashboard!C6)

In my example, I have the date field "C1:C242" Being filtered twice. one using the "from" field, and the other using the "to" field. simple logic operators such as <, >, or = work nicely here.

The last field in the filter is the city selector, where AD1:AD242 contains the name of the city location

Now with these filters implemented, you can go ahead and test the data validation filters to see if it changes the data selected in the DCD tab. If all is set up correctly then you have successfully put in dynamic data selection. The rest is now easy!


STEP  7 - Charts, Tables and Creativity!

Now for the fun stuff, we can simply create any chart we wish and have the data point to the DCD tab. I recommend making them in that tab then simply cutting them over to the dashboard. 

One important caveat: I recommend setting up the data range for the charts to be the maximum length of the data you have. As the DCD subset will be of variable row size. Luckily the charts will only plot the data that is there and will ignore any blanks. For one of my line charts, I set up the data ranges to  go beyond the data (up to row 897). This will give me a buffer just in case I need the subset data to get large. 

Date Range            Data Point 1         Data Point 2
DCD!C1:C897, DCD!R1:R897, DCD!G1:G897

With the charts now successfully utilizing the data from DCD, we can begin crafting charts and summary tables like the one I have below:

Which is just an aggregation of the data I have in DCD, I have it actually living within that table but can be placed anywhere. 


Without further ado, you can see this in action in the mock dynamic dashboard I created. 

Feel free to leave any questions or comments below.

Cheers!
D