In a data-driven world, we all collect lots of data. The vast amount of information we collect makes it hard to oversee, to follow up, and to make decisions. That’s where dashboards come in handy.Â
A dashboard tells the story your data holds and makes your data comprehensible and accessible. It gives your audience insights into your organisation by visualising theÂ
key performance indicators (KPI). A well constructed dashboard simplifies complex data sets and acts as a strategic interactive tool. And the good news is: we can create our own dashboard with Google Sheets. Let's have some fun.
Plan Your Journey
Planning is crucial. A dashboard is more than a large number of graphs and it’s far more than dropping the most difficult functions one knows. It should take your viewers on a journey through your organisation.
Start by asking yourself the right questions.Â
Why do I collect the data?
Who are my viewers?
What are the KPIs?
What do you want to show, prove, measure, follow up etc.
Start with an overall view and slowly dig deeper and deeper into the data. It gives you a comfortable learning process and it gives the viewers the time to understand what your story is. Creating a dashboard yourself sounds scary, but in fact it’s building in all the basic knowledge of data and pushing the bars a little bit higher.
Safety First
Before we start, keep in mind our raw data is sacred, which means that we never modify our raw data. This is a difficult one. The best method is to use regular expressions in your forms and add a few restrictions so that data is complete, clean, correctly formatted, and of high quality. It can often be forgotten, especially if you've received data collected by others or have had to merge several datasets together.
It’s tempting to resolve a few problems, but, if possible, it’s better to keep your raw dataset pure and resolve the problems onÂ
 your working sheet.
Take the following steps:
Give your Google Sheet the name DATA.
Add a new empty sheet.
Give the sheet the name DDATA (dashboard data).
Copy row 1 from the DATA sheet to DDATA.
Secure the sheet DATA.
Hide the sheet DATA.
Use the function =VSTACK(DATA!A2:X).
Where X is your last column. We don’t add the number of the last row. If the form is still collecting data it will automatically appear in your dashboard.
Use the format options to format the numbers, dates and text uniformly.
Design Your Dashboard
At this moment we have two sheets, namely:
The sheet DATA with the raw data (locked and hidden),
The sheet DDATA with the data we’ve cleaned up,
The dashboard deserves a sheet of its own. So add a third sheet and call it Dashboard.Â
To make the formulas easy to read, use named ranges for your KPI’s. For instance a named range for the column ‘Enrollment’ and a named range for the column ‘Department’. For calculation reasons we don’t add the header row to the named range. So your named range starts at the first row with data. Designing the dashboard is the most crucial step. When you are new to creating your own dashboard then keep in mind to design in waves and not all at once. Give yourself the opportunity to grow and to feel comfortable and it doesn’t matter if you have to redo a few steps to add the functionality you want. It’s the result at the end that counts.
The Filter Zone
Let’s have a close look at the filter zone. Create dropdown lists. When you're creating a dashboard for the first time add one or two drop down lists (don’t overdo it). Here we have two drop down lists.
The Overview Zone
Create an overview zone with basic information such as the total number of students, total number of courses, etc. Fill in the formulas and don’t pay attention to the filter zone.
Connect the Overview Zone with the Filter Zone
To connect the overview zone with the filter zone, we need an IF function. What happens if the filter is empty? In that case, the overall total should appear. These formulas are already added to the overview zone.
The In-Depth Zone
To make your graphs and tables dependent on the filter zone, use the filter created in the overview zone and create your graphs based on the filtered table.
To make your filters dependent on each other, use a spare sheet and use filters. Create a dropdown list based on the filtered table.
Opmerkingen