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.
I’m very excited to announce our upcoming Advanced Dashboards in Excel Masterclass in USA
Chandoo.org & PowerPivotPro.com 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 my live workshop in Chicago, New York, Washington DC & Columbus OH in May and June 2013. Places are strictly limited.
Yes, reserve my spot in Masterclass nowBelow article will give you a bit of a preview of the tips and tricks you will be learning 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 prioritized? (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 customize 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.
This article originally appeared on Plum Solutions website. Re-produced here with permission.