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

Year of publication

2015

Type of resource

  • Guide

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

Who is this resource useful for?

  • Evaluation users;
  • Evaluators;
  • Those involved in evaluation capacity strengthening;
  • Other – programme staff and people who need to collect and share data efficiently with others

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.

Source

Collins, B. (n.d.) 10 Techniques for building a Google Sheets Dashboard. [Webpage]. Retrieved from: https://www.benlcollins.com/spreadsheets/10-techniques-google-sheets-das...

0
No votes yet
Rate this Resource:
This resource is useful for:
A special thanks to this page's contributors
Resource Suggested By
BetterEvaluation Website and Engagement Coordinator, BetterEvaluation and ANZSOG.
Melbourne, Australia.

Comments

There are currently no comments. Be the first to comment on this page!

Add new comment

Login Login and comment as BetterEvaluation member or simply fill out the fields below.