This post on how to make Excel dashboards was one of the first I published here, and it still is one of the most popular posts. But I actually prefer broader data visualization discussions than Excel tips & tricks. Excel dashboards, although something that interests me, was never a priority. Until now.
I just launched an ebook on Effective Excel Dashboard design (warning: in Portuguese), so you can assume my return to dashboards are commercially motivated. And yes, if an ebook can help pay the bills I will be more than happy, because I will be able to keep thinking and writing about these things. But I’m a terrible salesman, and the true reason lies somewhere else and it is much deeper.
When I wrote my data visualization book I wanted it to be a reference for businesses, statistical offices and other organizations that share a common perspective when it comes to datavis. I wanted it to be justifiable, rational, functional and with a bit of fun. Perhaps 80% Stephen Few and 20% David Mccandless, if you want a formula. Obviously, some people think those 20% of fun are unacceptable, and that really saddens me.
Anyway, most of the book was about making individual charts. But I believe there are two key moments when you want to improve your visual literacy: first, when you understand that a chart is a communication tool, not something to illustrate a few numbers (with Excel defaults); second, when you go beyond the individual chart; the second one happens when you start thinking beyond the chart or a succession of slides in a PowerPoint presentation. You need a more complex and and consistent message that only multiple charts and other visual objects can provide. Writing about this is the next natural step.
Designing a dashboard in Excel is a great learning experience. Seriously. And it goes much beyond Excel. So, here is my list of reasons why you should design a dashboard in Excel:
Excel is an universal learning tool
Excel is everywhere, and most people are familiar with its metaphor. So, unless you prefer pen & paper (I do) Excel is kind of the least common denominator to do something with a tool in a training session.
Improved Excel skills
If you do want to improve your Excel skills, designing a dashboard in Excel is a perfect project. You have yo make several charts and technique work together, you have to manage the data, the user interface, manage screen real estate (which means finding chart type with a smaller visual footprint) etc.
You can use Excel to learn how to make better charts
I have no idea how many charts are made in Excel every single day. I think alot is a good estimate. The world would be richer it Microsoft implemented better defaults, but it always sided with us, data visualization enthusiasts, by providing an endless stream of ugly and effective defaults for our before/after exercises.
Exploring new chart types
You can guess from my ExcelCharts Shop that I like to have fun trying to find new ways of communicating with charts. I even try to salvage notoriously bad charts (like speedometers/gauges). Excel allows you to do that, unlike other tools that force you to take a more structured approach and are less flexible when it comes to chart formatting and design.
Excel is our Illustrator
There are almost no constraints when it comes to adding objects to a worksheet. You can let your imagination run wild for the basic dashboard layout. If you are not sure about it, just add a new sheet and start again.
A dashboard is a process of self discovery
You can see a dataset from multiple perspectives, and the more charts and the more datasets we add the more personal that perspectives become. Two people using the same datasets are unlikely to come up with very similar dashboards: they will have different data visualization styles, their interpretation will differ because of different priorities. When you design a dashboard with your own data, the data you use daily, you’ll have a better understanding of the data itself but also of how you see it. When I wrote my book, that was one of my goals: to understand what data visualization means to me, something that I couldn’t do with a blog post. A chart versus a dashboard is a somewhat similar process: you dig deeper and in some cases you’ll be surprised
A dashboard helps you find a line of thought for your communication
Storytelling is everywhere. All brands have a story to share with you. I think narrative is a much better word when used in data visualization. Just like graphical landscape is a more generic term for dashboard. They all share the need to create a consistent communication using a set of visual and non-visual objects.
Prototyping dashboards in Excel
It’s easy to disdain Excel as a proper dashboard tool (BI vendors do it all the time). But you need to separate Excel as a designing tool from Excel as a production tool. There are 1001 cases where Excel should never be considered as a serious production tool. Of those cases, Excel can play a relevant role in 999 of them as a design tool. Think about it. You know your data better than anyone else. When you design a dashboard you know what is relevant to you and you have an idea of how you want it to be shared, or monitored. This is a far better starting point than endless requirement meetings where a consultant without subject-matter expertise tries to understand what you are saying you need.
Designing a dashboard for benchmarking and tool evaluation
If I know what I need and how I need it, and can translate it into a dashboard, it’s easier for me to understand what each vendor has to offer, and how close they can get to my original design. Note that other tools can offer better alternatives, so prepare to be surprised.
A functional dashboard provides better feedback
A look & feel closer to the real thing will help users to understand what they should expect and provide better and more detailed feedback. If possible, try to get real data and spend a few hours/ days working on the dashboard to make it as real as possible.
I’m I forgetting something? Do you disagree? Add your comments below.