Dashboard reports allow managers & executives to get a high-level overview of the business in one snapshot. When it comes to making dashboards, Excel is an excellent choice. You can create powerful, insightful and good looking dashboards using Excel, thanks to features like Pivot Tables, Formulas, Charts and simple UI (user interface).
Today, I want to share with you five rules that can make your Excel dashboards impressive.
Most of you know that I’ve been a big fan of Chandoo.org over the past few years, and I’m very excited to announce that Chandoo has agreed to visit Australia this year as our guest to run his Advanced Dashboards in Excel Masterclass. Plum Solutions will be hosting this two day, intensive hands-on Masterclass. Enhance your Excel skills to create interactive, dynamic and polished looking dashboards your boss will love. Don’t miss out, this is a one-time opportunity to attend Chandoo’s live workshop in Sydney, Melbourne, Brisbane, Canberra and Perth in May and June 2012. Places are strictly limited.
Below is an article that Chandoo has written to give us a bit of a preview of the tips and tricks he will be providing in the Masterclass.
Rule #1: Design your dashboard on paper first
Many of us open Excel when we want to design a new dashboard. Not so soon! Next time when you are designing a dashboard, open your writing pad and starting jotting. Ask these questions:
• Who is going to use this dashboard (eg. CEO, Marketing Head, Sales Manager or Quality Control Supervisor?)
• What are the needs of our users? (to get a high-level status, track a project, monitor quality, get a pulse of customer service etc.)
• What our users do not want to see (for ex. CEOs may not want to see too much detail)
• How are these needs prioritised? (what should go first, what is least important)
• Where is the data that can fulfill these information needs? (in Excel, Oracle, MySQL or somewhere else)
• How often should this dashboard be updated? (daily, weekly, monthly, very rarely)
• Are there some templates to follow? (Corporate color schemes, reporting guidelines etc.)
Once you have answered all these questions, it is time to design the first version of the dashboard. Start drawing. Make a sketch of how your dashboard is going to look like. Discuss it with your users and team. Refine.
Rule #2: Separate Data, Calculations & Dashboard
Once you are ready to design the dashboard, do not put everything in the same Excel worksheet. This is going to cause you so much head-ache. Instead, keep all the data in one sheet, write all formulas (calculations) in another set of sheets and finally show the dashboard in a different sheet. This way you are keeping the structure flexible so that changes can be made quickly.
Rule #3: Follow the Design Principles CRAP
There is a great little book on design – Non-designers Design Book that changed how I design my dashboards. The book defines four basic rules that you must follow when designing anything.
1. Contrast: Use contrast to make things different. Eg: colour, font size, bold, size etc.
2. Repetition: Apply same design to all related things. Eg: all charts use same formats, all text boxes in same size etc.
3. Alignment: Every edge of every object should be aligned to every edge of every other object. No exceptions here.
4. Proximity: Keep related things together. For example, all sales related charts should be put in same location in your dashboard.
If you want to design dashboards that look good and communicate better, read the non-designers design book.
Rule #4: Use Text boxes & Shapes
Excel, due to its grid nature makes everything look like a box. But boxes can be dull and boring. Thankfully we can use Text boxes & Drawing shapes to add variety to our dashboards. What’s more, you can even link these to cells so that they can display values. See this blog article for an example: Use drawing shapes to enhance dashboards
Rule #5: Copy from great examples
There are so many very well designed dashboards. So next time you are set out to make one, just copy the design ideas. It is so much better and faster than re-inventing the wheel. Just make sure that you customise the dashboard so that it addresses all information needs as identified in Rule #1.
For some inspiration, see these pages
• Sales Dashboards – 32 Examples
• Simple KPI Dashboard in Excel
• 33 Resources for making better dashboards
That is all for now. Happy Dashboarding 🙂
Chandoo.
