Visualisation options for dashboards
Dashboards in Excel have always been popular, but they are becoming more so, as people’s desire for fast information increases. Dashboards are not reports, they are a snap-shot of the immediate situation, only highlighting high-end alerts. These could be issues, trends, completion, etc. Whatever the user needs to see at a glance, which they can then explore in the data or report. So, given how efficient dashboards need to be, there are a number of ways to display high end data, so that it can be easily recognised. Here are 10 ways we have used to help people visualise data.
Graphs
This is the usual type of visualisation used. Bar graphs, line graphs (a combination of the two pictured), pie charts, donut charts, even more elaborate spider graphs or waterfall graphs. These are great for showing data. I usually try to make these simpler and smaller on a dashboard, to see the trends, then a more detailed version in the reports for further analysis.
League Tables.
This is a surprisingly popular choice. Especially when clients, staff, products, or anything else needs to be ranked. Showing a small league table can be very useful. I can even let you toggle between tables (1 to 10, then 11 to 20 for example). I’ve even made tables with medal or trophy images.
Conditional Formatting.
This is a widely used option, simple conditional formatting, except it’s often used badly. The idea is to see what goes with the theme of the spreadsheet and also how urgent the alert is. I usually use these to show trends or strength of scores, so don’t make it too bold. I often use the corporate theme. I usually leave bold ‘alert’ colours like red and yellow for urgent alerts. That way your eye is drawn to that when there is an issue.
Maps.
This is an interesting one that I have done, which is to plot data on a static map. These can be expanding ‘bubbles’ or different coloured dots, and the map can be changed for your desired area. This needs to be a static graph, and it’s not 100% accurate, because it’s not actually a map at all. It’s a combination of a graph and an image of a map! Very useful if location plays a part in your reporting.
Symbols.
Many struggle with symbols like this, because they use the available conditional formatting symbols, so are restricted to those. By using emojis or special characters, and then using conditional formatting to determine the colour, I can do many different symbol types and colours to easily display important information from data sets.
Heat Maps.
Heat maps are very useful when the the reports lend themselves to it. When there are comparisons of data, or even locations in a room for example, I can create heat maps such as this one, either showing the numbers or not. These are very useful to spot trends, location, movement, etc. They can’t be used for everything, but when they are useful, they are very useful.
Check Lists.
Sometimes the old ways are best. Some prefer simple to-do-lists instead of graphics. These can be focussed on one thing (like invoices due) but can also be a combination of different tasks. I’ve even done some lists where it includes various kinds of tasks, sorts them by urgency, and then displays a list of the next few tasks until complete.
Alerts.
I use these often, usually for missing or incorrect data on various tabs. So, I list the tabs of the spreadsheet, and then each one either has a green alert (all OK), a yellow alert (missing data) and or a red alert (incorrect data). These are tremendously helpful, as you can see at a glance which tabs require data input or data correction. These types of ‘pop up’ alerts can be used for many things.
Graphics.
Sometimes I get asked for graphics that can change based on the data. In the case pictured, I have used graphs to create a ‘speedometer’ graphic. As the score improves, the needle moves up! I’ve created analogue clocks using a similar method, and other graphics using graphs, text, conditional formatting, and other means.
Data Representation.
This is something I do which is similar to bar graphs, but uses little emojis instead. So, in this case I have used burgers to represent food stock. The more stock, the more burgers. I’ve even done one before using squares to represent boxes, and not only done them in bars, but along a ‘conveyor belt’ to show where they were in the process. These kind of data visualisation tactics would depend on how suitable they are for the project.
There you have it, some of the tactics and methods I use to display information from data on a dashboard. The idea of the dashboard is to quickly and easily relay important information, so making it exciting, different, informative, graphical, is really important. These will vary from project to project, as it all depends what we will show on your dashboard, and the best way of doing so. Do you need a spreadsheet with a custom dashboard? Get in touch and let’s make you one.
Recent Comments