Blog

How to Create an Automated Google Sheets Dashboard

Emma Butler
Emma Butler
Apr 19, 2020 · 5 min read
"And breathe" written with neon

Want to create a dynamic and engaging dashboard on Google Sheets? 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 Excel or Google Sheets expert. That being said, it’s helpful to know how a spreadsheet works and have familiarity with basic functions: SUM, AVERAGE, COUNT, MAX and MIN.


I’ll walk you through an example of how to make a simple automated dashboard on Google Sheets for your metrics using the SPARKLINE and VLOOKUP functions, as well as Data Validation and Pivot Table features. If none of those sound familiar, don’t worry as I’ll show you how to use them in a super simple way. 

Course breakdown

1. Why create a Google Sheets dashboard?

2. Key design tips of dashboards (with visual examples)

  • 4 rules to build outstanding reports
  • Examples of good reports
  • 4 biggest mistakes from bad reports
  • Examples of bad reports

3. The basic functions and features you should know

  • VLOOKUP, to search an element in your spreadsheet
  • Pivot Table, to summarize your spreadsheet data
  • Data Validation, to ensure data consistency throughout your spreadsheet
  • SPARKLINE, to create dynamic graphs into a spreadsheet cell

4. Your ultimate guide: a 14-minute video to create an automated Google Sheets dashboard

5. How to automate your Google Sheets dashboard: stay synced and up to date

1. 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. They'll help you and your colleagues understand in more depth your business and answer with data to key business questions. Ultimately, you'll be able to improve your business and increase your revenue.

  

2. Key design tips of dashboards (with visual examples)

4 rules to build outstanding reports


Rule #1: Keep things simple

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.


Rule #2: Make sure your color scheme is bright and clear

Too many colors all over the place will distract from the data.


Rule #3: Tell a story

Understand how each graph is connected and its relevance to the overall story you’re trying to convey.


Rule #4: Allow to filter 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).

 

Examples of good reports
good-example-google-sheets-dashboard

 

good-example-google-sheets-dashboard

4 biggest mistakes from bad reports

Mistake #1: Too many colors

Colors all over the place create a messy distraction. You can use a color palette generator to get colors that go along well with each other. It’s very easy to find for free online so you don’t need design experience to prettify your dashboard.


Mistake #2: Too many types of visualizations

Maps, bar charts, steam graphs, area charts: too many become 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


Mistake #3: Overdone 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.


Mistake #4: Too much data

This isn’t a raw spreadsheet. The whole purpose of the dashboard is for you to summarize the most relevant information so you should filter your data.

Examples of bad reports
bad-example-dashboard

 

bad-example-dashboard

 

3. The basic functions and features you should know

In the following video, you’ll see simple use cases of each of these functions. I suggest you take a quick peek of their general description below here before moving on to the video.


VLOOKUP, to search an element in your spreadsheet

 

If you’re not familiar with this function or need to refresh your memory, you should check out this easy 1-minute video. In it, I’ll show you how to use VLOOKUP to search an element in your spreadsheet. 


In the example below, I wanted to see how much money each 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 you have multiple outputs for one input (if, for example, you have multiple amounts earned for one person and you want to add them all up or just aggregate them).

 

There are a few key steps to VLOOKUP.

To recap, the function is written like this:

=VLOOKUP(search key, range, index, is sorted)

  • 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.
  • Then, index refers to the column 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.
  • We can ignore IS SORTED for now as Google Sheets already takes care of that for the most part.

 

In the example below, I want to find the Total $ Earned in relation to the Last Name. So I’ve highlighted columns that contain both the last names and the total earned. Now going from left to right, the Last Name 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.

 

vlookup 1 Automated Google Sheets Dashboard

Watch how to use the VLOOKUP function in the video above.

Pivot Table, to summarize your spreadsheet data


In the following video, I’ll show you how to create a Pivot Table to summarize your spreadsheet 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. You’ll find Pivot Table to be a great way to sort your data.

 

In this video, I’m mainly using Pivot Table for its convenient sorting and isolating capabilities with only rows and values. You can make much more complex pivot tables to showcase your data. While you can make a wide range of complex pivot tables in Google Sheets.

 

how-to-make-a-pivot-table Automated Google Sheets Dashboard

 

If you want to delete your Pivot Table at some point but keep a backup of the resulting data, I recommend this 1-minute video that explains it easily.

 

Watch how to use the Pivot Table feature in the video above.

Data Validation, to ensure data consistency throughout your spreadsheet

Simply put, Data Validation lets you have a drop-down in each cell with a list of values you’ve defined. This is great if you need to protect structured data, like formulas, from other people who may be editing your spreadsheet. 

google-Sheets-data-validation

Watch how to use the Data Validation feature in the video above.

Sparkline, to create dynamic graphs into a spreadsheet cell

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.

sparkline-google-sheets

Watch how to use the Sparkline function in the video above.

 

4. Your ultimate guide: A 14-minute video to create an automated Google Sheets 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 a simple way of understanding creating a dashboard that’s automated:

 

 

5. How to automate your Google Sheets dashboard: stay synced and up to date

When building a dashboard in Google Sheets, you're using data about your business that typically comes from an external system (maybe your company database or CRM, etc). Once you built the dashboard once, ideally, you'll want to keep it updated with new data as times goes by.

 

If you want to keep your data live in Google Sheets, you have 4 main options:

1- Google Sheets + Manual copy-pasting / CSV import

Many companies download their data in CSV format from one source and manually copy-paste it (or import it) in Google Sheets. Then, your Pivot Tables should update and therefore update your tables. But that’s time consuming. You'll have to do this process every time you want to update your data. It's also error prone.

 

2- Google Sheets + Script

If you think you’re spending way too much time copy-pasting data into Google Sheets and want to save hours of doing the exact same process over and over again, another solution is to write a script (ie some code) to automate that process. We've written an article on how to do this: How to automatically retrieve data from your database to Google Sheets with a script.

3- Google Sheets + Add-ons / Zapier  

Great add-ons have made it easy to move external data to Google Sheets and keep it live, without having to write a single line of code. We listed here The Top 4 Tools to Import Live Data to Google Sheets.

4- Actiondesk

If you want to access your key company data in a spreadsheet interface, then you should try Actiondesk.

Actiondesk is the easiest way for startup teams to make better decisions with their data. You can access your company key data without code or writing SQL, you'll just need basic spreadsheet skills. Careful, Actiondesk is not Google Sheets, it's a new type of spreadsheet that connects directly to a company's database or data warehouse.

SEE HOW ACTIONDESK WORKS

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.

Keep reading

X
Please wait...
Oops! Something went wrong while submitting the form.