How to Create an Automated Google Sheets Dashboard

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, Excel, or a spreadsheet software like Actiondesk. 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.

You want to make sure your data is accurate and always live, so using Google Sheets or a user-friendly spreadsheet software like Actiondesk is the optimal solution. As you update and transform data on your spreadsheet, your graphs, charts and visuals stay synced.

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

‍

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

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.

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, keep in mind that a tool like Actiondesk enables even greater flexibility and provides real-time updates to these tables.

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.

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.

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

‍

You’re probably pushing your data from a lot of different sources into Google Sheets (SQL, Stripe, Salesforce, Hubspot, etc.). Here are all the solutions you can choose from and what I recommend.

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.

Current spreadsheets software were created 30 years ago, and my team believes they are broken. That’s actually why we created Actiondesk, in order to stop this huge time waste in companies. If you’re curious, that’s the vision we shared after doing Y Combinator: Turning 1 Billion Spreadsheet Users into Programmers.

‍

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, I can only recommend this article: How to automatically retrieve data from your database to Google Sheets with a script.

‍

3- Google Sheets + Add-on / Zapier

If you choose a third party to push updated data to Google Sheets, the first thing you’ll want to check is if your external data source is supported in the integrations of that platform.

We did an in-depth benchmark of the top integration tools, comparing Zapier, Supermetrics and other alternatives for startups & SMBs. You might start with this summary: Infography: 9 alternatives to Zapier compared.

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

While all of these are viable options for creating automated Google Sheets dashboards, they can be tedious and time-consuming to set up. Even the best third-party integration tools like Zapier require regular “zaps” that consume your team’s valuable time and resources. Consider investing in a tool like Actiondesk instead.

With a simple setup, Actiondesk enables you to import all your relevant data from a wide variety of sources, build custom dashboards, and stay on top of key insights with automated real-time updates. With just a few clicks, all of your data is readily available — anytime you need it.

‍

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.

‍

While there are a number of tools to help streamline data analysis in Google Sheets and enable automated Google Sheets dashboards, none are as powerful, flexible, or efficient as Actiondesk. Actiondesk marries the functionality of an advanced BI tool with the ease and efficiency of a spreadsheet. With automated-real time updates and integrations for more than ten data sources, Actiondesk enables you to make more of your data. Try it for free today.