Submitted by Emma Butler

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).

Breakdown of this Crash Course:

Why Use a Dashboard on Google Sheets

• Key Design Elements of Good and Bad Dashboards

Functions to Know

• VLookup
• Pivot Table
• Data Validation
• Sparkline Graphs

Contents of Dashboard (14 minute video: Ultimate Guide)

• Dynamic Charts using Vlookup and Data Validation
• Easy Sparklines
• GEO chart

Automate Data (stay synced and up to date)

• Best tools to integrate your data with Google Sheets

Why Create a Google Sheets Dashboard:

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 Dashboards:

Key elements of good reports:

• Don’t include overly complex distracting visuals. If a simple bar chart shows the data clearly, use it. There’s no competition for the fanciest type of graph.
• Make sure your color scheme is bright and clear. Too many colors all over the place will distract from the data.
• Exceptional dashboards allow you to filter your data based on variables of key metrics. (i.e If you want to view the data of sales from only a certain region, you should be able to filter all the elements of your dashboard to fit that criterion. More examples of how to do this to come)
• You should tell a story; understand how each graph is connected and its relevance to the overall story you’re trying to convey

Some Examples:

Elements that make a bad report:

• Colors all over the place create a messy distraction.
• Too many types of visualizations are never good. Maps, bar charts, steam graphs, area charts, can be a lot to take in. If you need to use a large variety of visualizations, make sure they are clearly linked and the viewers understand their relationship
• Don’t overdo visualizations. If you create a 3D pie chart and it might look cool, but it might make it harder to gage the portions of the chart.
• TOO MUCH DATA. Filter your data. This isn’t a raw spreadsheet.

Some Examples:

Terms/Functions to Know:

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.

VLookup

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.

Pivot Table

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.

Data Validation

Simply put, Data Validation lets you have a dropdown in each cell with a list of values you’ve defined.

Sparkline

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.

Key Elements in Creating a Dashboard:

You’ll need to know how to turn your data into 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.

Automation:

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

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 with Complex Data Transformations

Actiondesk

With Actiondesk, you can easily get data from any source, transform it with the usual spreadsheets functions you know and push it to other tools (including Google Sheets)

Actiondesk lets you push all your existing data to Google Sheets unlike Zapier who will push only future data"

See Actiondesk’s integrations here

Business Oriented, Simple Push of Data, Lots of Integrations

Zapier

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

See more alternatives to Actiondesk and Zapier here

Conclusion

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.