I’ve been writing about making dashboards in Excel ever since I started this blog because an Excel dashboard is a natural extension and application of Excel charts and it is relatively easy to implement. Excel is the de facto BI leader, and new technologies unveiled recently by Microsoft will probably make its leadership even stronger.
I started writing about Dashboards in Excel using as an example dashboard made using world population data, the Demographic Dashboard. One of my first posts was:
And it still remains one of the most popular posts. There are many ways to manage the data in an Excel executive dashboard. I believe that you should:
- Link the file to an external data source (instead of pasting the data into the spreadsheet);
- Use pivot tables whenever possible (instead of relying on lookup functions);
- Not be afraid of adding some VBA code (most of the time, you just have to record a macro).
Here are some tips:
Because Excel is such a flexible tool, I was able to make two different versions of the Demographic Dashboard, apparently very similar but using very different techniques. The first one was the “VBA-Free” dashboard, where I use a single pivot table and no VBA:
After that, I made a new one, with no VBA and no pivot tables, only tables and lookup functions:
Not everyone agrees that we should use VBA in dashboards:
And advanced Excel users are often fighting IT:
What is the best tool to create a dashboard? How can we see beyond the marketing barrage? One of the options is to make a prototype in Excel and then try to port it to other tools. I’ve tried that with Crystal Xcelsius and failed miserably: