When good enough is good enough: a quick guide to simple dashboards
Author’s note: Like many things in my life, I stand on the shoulders of giants. The shoulders on which I stand in this case are those of my learned colleague Paul Knight, who introduced me to the zany idea below when I joined the team — thanks Paul!
Dashboarding software, much like GIS software, can be rather complex: if you need to connect to your organisation’s sensitive databases, include up-to-the minute data feeds from several sources and display them in a rich visual, then Business Intelligence software like PowerBI or Tableau is the way to go.
However sometimes what you need is something with bar graphs that people can click on. With that, I present: the humble Excel dashboard.
What? That sounds silly! Excel is just for tax returns or for people who like writing things on squared paper, isn’t it? You can’t make anything decent with that!
To that I say: you are partially correct. You can make something entirely functional albeit lacking in pizazz. So dear reader, come with me on a journey into the exciting world of pivot tables.
Like many things in this world, the process starts with well formatted data.
If you don’t have this, I can’t help you (aside: I can help you, but we’ll need to have a chat first).
Put all your data on a worksheet, call it “Data” and make it a table with a sensible name (this may sound pedantic but you may find a time when you don’t remember what Table84 contains — trust me).
Next make a second worksheet, call it “Pivots” and in it create some pivot tables, also with sensible names. Then create some pivot charts. Then a slicer(s).
The image below shows:
- a series of pivot tables, a slicer and a pivot chart on a separate worksheet
- the worksheet is named “HoHPivots” as it held the pivots summarising the data collected about the Head of Household
- the pivot table selected is named “HoHPivot1” as this is the first of the pivot tables on this worksheet
- the Slicer being used to filter the pivot table to the community of “Bengo”
- the pivot chart visualising data related to the gender of the Head of Household
When you are happy that the slicer is filtering everything you are expecting it to filter, make a new worksheet and call it “Dashboard” (or something equivalently helpful for your end user). Select the whole sheet and colour fill it with white to get rid of all those ugly grid lines. Then copy and paste your slicers and charts over from the pivots worksheet. Shuffle everything around until it looks acceptable (albeit, by Excel’s default settings, like something made in 1998 [you can put more effort in here to make it not look like something from 1998, if you wish]) and there you go: a dashboard.
Your end user can click on the slicer and the pivot charts will update depending on the filter they have selected. In the dashboard above, I have set up slicers to relate to the provinces of Angola: when a user clicks on a province they are able to see summarised data for that province.
To update your data (e.g., from a subsequent round of household registrations) all you need to do is copy/paste over the data in the “Data” tab and, as long as the table’s extent includes all the new data, then you can just press “refresh all” on the pivot tables and everything should update.
Are the any extra steps you’d recommend based on previous experience?
Yes! Depending on your audience, you may want to use some of Excel’s inbuilt locking functions to prevent accidental data loss/changes and prevent people from moving the graphs around the page whilst clicking.
For example, you could hide the sheets that contain raw data, so your dashboard file is less confusing to the end user:
To prevent the end user from moving around the items on the dashboard page or unhiding worksheets, you could lock the workbook with a password:
What about a map?
Yes! Perhaps you have read a recent blog about making maps in Excel?
In anticipation of you asking that exact question, I have just tried this myself and it does work, although needs a slight workaround. Map charts don’t work with pivot tables (why Microsoft?), so what you need to do is make the map with a copy of the data from the pivot table, and then update the data source for the map to be the pivot table. I used these instructions to make this:
The content of the pivot table (left) was copied to make the table (centre). The map was created using the copied data, then the data source was updated to refer to the pivot table (note: you can then delete the copied table).
If you then link the pivot table up to your slicers, the map will re-draw depending on the filter applied in the slicer.
Surely this isn’t useful, is it?
You know what? Yes it is! Making a dashboard in Excel allows you to:
- quickly and easily create a minimum viable product with a widely used and accessible tool
- have the potential for your end user to see the under-the-hood workings of your analysis, should you want them to
- share that dashboard with anyone who has Excel on their computer, (including people who Microsoft deem to be not “part of your organisation”)
- share that dashboard in a single file
Picture these end-users:
- someone deployed to a complex environment lacking in reliable/consistent network connectivity
- someone who has a computer but who only has basic IT literacy
- someone who wasn’t the intended end-user but has emerged as a key stakeholder
All of these people can receive a key data analysis product in a single file attached to an email. They can then distribute to whoever they want, how ever they want.
Even if you only use the Excel dashboard as the first step towards a more complex product, the work you do to understand your data and the analysis steps needed will benefit you hugely should you decide to transfer your proof-of-concept workflow into another tool, say PowerBI:
A note of caution
As with all good Information Management, you must be mindful of the kind of data being used in the end product. You must keep sensible data protection principles front of mind and adhere to any legislation that applies to the context in which you are working.
In the case of Excel dashboards, the advantage of having your data and dashboard in a single file may also present a data protection challenge: native workbook and worksheet protection in Excel is relatively weak and the end user’s use of the file may have unintended effects (e.g., them opening the file in Google Sheets removes the protection).
So, even with data that appears non-sensitive, be conscious of the effect that an accidental or malicious data share may have on whatever it is your data relates to.
In the example I have given above, the complete registration data that was recorded in the survey was not included in the Excel file distributed, it was kept securely stored separately. No data that could be used to identify a specific person remained in the Excel file and everything that remained was anonymised. For this blog post demonstration, I have also transferred the data collected to a completely different country.
Hopefully the above guide is enough to get you started but if not we might be able to help, especially if your dashboard has a map in it! Either send us a request for support or come for an informal chat at our weekly surgery space — the details for both are on our RedRoom page.
