Want to create a dynamic and engaging dashboard on Google Sheets for your report? Don’t have the time to sit and learn from an online course that might cost a hundred bucks?
This crash course isn’t meant for experts in Google Sheets or Excel. It’s helpful to know how a spreadsheet works and have familiarity with using “SUM,” “AVG” and other basic functions. (If not, head on over to this article, before starting this one )
I’ll walk you through an example of how to make a simple automated dashboard on Google Sheets for your metrics using the sparkline function, Vlookup, data validation and pivot tables. (IF none of those sound familiar, don’t worry; I’ll show you how to use them in a super simple way).
Dashboards are used to showcase data in fun, dynamic visualizations. The easiest way to digest data is in simple clear cut visuals.
You want to make sure your data is accurate and always live, so using Google Sheets is the optimal solution; as you update and transform data on your spreadsheet, your graphs, charts and visuals stay synced.
Key elements of good reports:
Elements that make a bad report:
You’ll see in the following video simple use cases of each of these functions. If you’re not familiar with these terms, take a quick peek of their general description here before moving onto the video.
A Vlookup is a way of searching your spreadsheet. In the example below, I wanted to see how much money each person (sales rep) earned. By inputting one value or phrase, Vlookup retrieves the corresponding value. So in this case, I’d input the ‘Last Name’ to retrieve the correlating amount earned.
This is super helpful if you have multiple tabs in your spreadsheets and want to find correlating values across the sheets. It’s also useful if let’s say you have multiple outputs for one input (so multiple amounts earned for one person and maybe you want to add them all up or just aggregate them).
There are a few key steps to VLookup.
The function is written like this
=vlookup(search key, range, index, is sorted)
For now, we can ignore “is sorted,” Google Sheets already takes care of that for the most part.
The Search Key refers to what input you want to find the output to. So I would highlight the cell that has the Last Name of the sales person I want to investigate.
Next, “range” refers to the span of cells you’re looking into. You’re not examining the whole wide web of all Google Sheets; you’re just trying to find where the output is on your spreadsheets in certain cells. So in the case below, I highlighted relevant fields.
The final step is “Index.” Index refers to the columns within the range and is always inputted as a number. It’s which column you want to retrieve data from. The first column you select from left to right is 1, the second is 2, the third is 3 and so on.
In the example below, I want to find the amount in relation to the last name. So I’ve highlighted columns that contain both the last names and the amount. Now going from left to right, the “Last Names” column is 1 (because it’s the first column highlighted), “Date” is column 2, and “Total $ Earned” is 3. I’m looking for data from column 3, So here I’d put 3.
Watch this all happen in the video below and read more in-depth on Vlookups here.
Creating a Pivot Table is a way of sorting your data. Perhaps you only want to display a few columns and rows or you want to change the layout of your raw data to be more digestible.
In this video below, I’m mainly using pivot tables for their convenient sorting and isolating capabilities with only rows and values. You can make much more complex pivot tables to showcase your data.
For a deeper dive into Pivot Tables, check out this guide here.
Simply put, Data Validation lets you have a dropdown in each cell with a list of values you’ve defined.
The sparkline function is best used for showing progress over time. By just choosing the range of data you want to show in the argument of the function, the Sparkline function creates a super simple, dynamic graph.
You’ll need to know how to turn your data into charts and graphs. For simple
Connect simple charts and graphs.
There is so much more you can do with VLookup, Pivot Tables and Data Validation. This is just a simple way of understanding creating a dashboard that’s automated.
If you want to keep your data live in Google Sheets, you have a few options. You're probably pushing your data from a lot of different sources into Google Sheets (maybe Salesforce, MySQL, Typeform or Mailchimp). If you choose a 3rd party to push updated data to Google Sheets, you’ll want to check your external data source is supported in the integrations of that platform.
You can always download CSVs and import it into Google Sheets and your Pivot Tables should update and therefore update your tables.
But that’s time consuming.
Here’s what I recommend.
Basic Personal Dashboard:
Supermetrics supports integrations mainly used by marketing teams such as Instagram, Twitter, Google Analytics and is a google sheets addon.
Check out their integrations here.
Business Oriented, Simple Push of Data, Lots of Integrations
If you have dozens of different applications you’re importing into Google Sheets, Zapier has so many integrations. Zapier doesn’t account for past data though but is simple to set up and zaps your data right into Google Sheets.
See Zapier’s integrations here
Discoverr more alternatives here
This is just the beginning of what you can do with your dashboards. Ultimately, everyone’s data and how they showcase it is unique to their company. What story we choose to tell with our dashboard will also vary. With these tools in mind, hopefully you can wow your boss with an amazing dashboard that’s custom to your needs.
Know a colleague or friend who could benefit from a better dashboard?
Send them this article and help them become the workplace hero they were meant to be.