10 Techniques for building a google sheets dashboard

This is a step-by-step guide for creating a dashboard with Google Sheets (a free, online spreadsheet application).

It covers the full process from collecting the data with Google Forms to formatting and sharing the finished product.

This resource and the following information was contributed by Alice Macfarlan.

Authors and their affiliation

Ben Collins

Key features

The sections of this tutorial include:

• Collect user inputs through a Google Form into a Google Sheets dashboard
• Retrieve data with LOOKUP formulas
• Apply logic with conditional formulas
• Automate your dates
• Add interactivity with data validation
• Chart your data
• Show trends with sparklines
• Apply conditional formatting to show changes
• Format like a pro!
• Share and publish your dashboard for the world to see

How have you used or intend on using this resource?

I'm a real nerd for spreadsheets and use Google Sheets a lot because it's accessible from anywhere with an internet connection and is to share with others. There's not a lot in this tutorial that is completely new to me or that I couldn't have figured out by doing a search, but it's handy refresher on what features are available and lists some formulas that didn't know existed that will likely save some time. In terms of actually using this for dashboard creation, I would be looking at this resource - which focuses on the technical aspects of dashboard creation - in tandem with other guides that go into more detail about the how and why of visualising and reporting data. The BetterEvaluation Rainbow Framework task on Visualise Data (written by Stephanie Evergreen) and the option page on Data Dashboards (written by Judy Gold and Jenny Riley) are good places to start and include examples and links to curated resources on this. 

Why would you recommend it to other people?

This resource will be particularly useful for those who are new to Google Sheets, as well as those who need to find systems to document and report regular changes to a range of stakeholders and team members. I think one of the benefits of this resource is that it starts with a quick tutorial on setting up data collection using Google Forms, and then explains how to integrate this with the dashboard. This, alongside the instructions on publishing and embedding the form in a webpage, make this tutorial more useful than a lot of the tutorials out there for using Google Sheets. The guideance is also based around the freely available features of Google Sheets, rather than suggesting paid add-ons, which is another advantage over other tutorials.