I attended an excellent livestream last week about Excel dashboards, presented by the legendary Chandoo. In his tutorial, he built a KPI dashboard in Excel in around an hour. As well as learning lots of useful tips, it also made me think about the key skills (Excel and otherwise) that you need to build a great dashboard.
Understand the business
It’s a cliche but it’s true. If you don’t understand the business, you won’t be able to build successful dashboards, in Excel or in any other format for that matter. You need to know what Key Performance Indicators are important. You need to understand which comparators are meaningful. For example, for a retail business, it probably makes sense to compare to the same period last year. For a charity, a comparison to budget may be more helpful.
What needs to be dynamic?
As part of understanding the business, you need to know what elements of your dashboard will be dynamic, that is, what will need to change frequently. It might just be reporting period, but it might be other components such as products. What I mean is – if you just sell ten products and want to know how they are doing, the product element can be static in your model. Whereas if you sell thousands and want to know what your top ten are each month, then this must be a dynamic element of your dashboard.
Think about how data might expand or change in the future, i.e. will new account codes get added? If you’re reporting by account manager, does your model need to change allocations of sales easily?
Generally speaking, it’s much easier to build in these mechanics when you’re planning your dashboard. It’s a lot harder to build them in once you’ve got it working.
Key principles of data visualisation
It is so important to understand some key principles of data visualisation. You can dedicate weeks to studying this but the good thing for those of us without much of a sense of design is that there are a whole bunch of RULES which you can follow to achieve a respectable end result. For example, English speakers focus first on data at the top and the left. You should minimise your non-data-ink. Choose the right chart for each job (and don’t be afraid to use a table if it’s the best way to present the data). Etc etc.
I’ve written more here about Excel design tips for non-designers.
Specific Excel skills
This isn’t an exhaustive list, but here are the key areas I find most useful for constructing dashboards in Excel.
- pivot tables and slicers – in most dashboards there will be a pivot table or five, bringing key bits of data together. A tiny bit of PowerPivot or Power Query knowledge won’t go amiss either, for combining different sources of data or adding additional calculations.
- data validation to create drop down lists. You could also add form controls to this, but in most cases plain ol’ data validation will get you there.
- number formatting – not just formatting cells, but being able to translate numbers into specific text formulas so you have more options for including them in visuals.
- some specific formulas that allow you to select parts of a data set – for example FILTER, SORT and LARGE.
- Charting hacks, such as how to ignore empty data cells, or how to automatically highlight a maximum or average value. I might write a separate post about this, as the best charting hacks don’t require a huge amount of technical knowledge but they do need you to take a creative approach to the problem.
- Non-charting hacks – for example, knowing that you can use conditional formatting data bars to generate a really neat in-cell graphic.
Humility and a willingness to keep learning
It’s really important to be humble about your skills. By that I mean;
- Your dashboard is only fit-for-purpose if it’s actually being used by the people who need it. No matter how beautiful it is, you need to be ready to ditch it if it isn’t working for others.
- You will never know it all when it comes to Excel, particularly these days with the pace of its evolution, so keep watching webinars and reading blogs. For example, I had absolutely no idea you could use an emoji with an IF formula until I saw it in the webinar. This may sound a bit gimmicky and naff, but there are some very useful businessy emojis such as ✔ or ➕.
Chandoo is able to create a dashboard in an hour not just because of his mad Excel skillz but because he has years of practical experience. If creating dashboards is not something you do every day, you might want to get in some outside help. There are lots of people, me included, who can design and build dashboards in Excel for you. I can also train your team in how I did it. Contact me for more information.