Best 100+ Excel Hacks & Tips with Excel 2016. Take the course for only $10 !!

Arrow up
Arrow down
Menu

Excel Dashboards : Design Principles & 10 Golden Rules

A dashboard is a visual interface. It displays data graphically and provides at-a-glance views into key measures. How you can have a simpler and more effective dashboard design? Here are the 10 basic rules:

  • First of all, simplify your dashboard. Don’t include too much data or too much information in a report. Keep it simple. Also, the less fancy formatting your dashboard has, the easier is to read. Charts should present your data as simple as possible.

  • Avoid using background fills or fancy effects like shadows, gradients, or glow. Also, pictures or clipart doesn’t help.

  • Remove elements that don’t contribute to the core message. Here are some examples:

  • Remove borders

  • Avoid data labels that are not necessary

  • Remove gridlines (vertical and horizontal)

  • Remove axis that don’t

  • Try to fit your dashboard and your data on one page (maybe you can adjust column and row width or reduce white space).

  • Put your most important measures to the upper left side of Excel worksheet. Users pay attention to specific regions.

  • Use descriptive titles for each component.

  • Use effective format numbers.

  • Include a timestamp on your dashboard (monthly or yearly, for example).

  • When it’s necessary, use a few comments or labels to help users clarify your model.

  • Make sure that the users have to click a few times to get their data and the navigation is easy.

 

Also, before starting to create your dashboard, ask yourself three questions:

  1. Where are my source data and how they will be organized?

  2. What calculations do I need to perform?

  3. How and when I will update my dashboard?

 

The most useful Excel functions for dashboards are:

  • VLOOKUP function

  • HLOOKUP function

  • CHOOSE function

  • SUMPRODUCT function

 

Don’t forget to avoid the following issues:

  1. Importing too much data. You should only import data that’s necessary for the purpose of the dashboard.

  2. Trying to keep your data model limited to one worksheet. If a user needs to scroll to view all the elements, it will be difficult to understand the data model.

Thank you very much and waiting for your questions.


If you want to learn how to make amazing Dynamic Excel Charts, Dashboards and Pivot Charts in a few weeks, take our Udemy Course for only $25 !! (original price: $199 - 87% OFF!)

 


 

 

Last modified onMonday, 15 February 2016 17:25
Rate this item
(1 Vote)