3 Steps to Create cool dashboards in excel

Posted on September 5th, 2006 in Analytics. Learn Excel – 15 comments

Dashboards are very common business monitoring tools, but creating them in excel with all the bells and whistles is not so easy. So here is a quick 1-2-3 on how to do it.

Lets take a sample of 2 consecutive year sales figures for 7 regions. The colums have Region name, 2004-05, 2005-06 figures and finally YoY Growth percentages. The lame dashboard should look something like this:

But may be we can make it little better. Ideally, a person looking at this would like (to know) the following things:

  • What are the things that are going up / down / remaining constant
  • The chart should look simple and not cluttered; meaning, there cant be multiple columns to present information. He/she should be able to look at one column and concluded something
  • May be little graphics wont hurt the presentation while retaining the information.

Well, how to get it in 3 steps?

  1. Type the following formula in the cell F5 and drag it to apply to all the cells

    [Click on the image to see bigger version of the formula]
  2. Select the range F5:F11, goto Format- Conditional Formatting and enter the following values there:

    [Click on the image to see bigger version of the formula]
  3. Finally, if its already not, change the font of the worksheet to Arial, (see those arrow marks, they are not available in all fonts. And btw, if you dont know how to insert them in the formula use Start- Programs- Accessories- System Tools- Character Map and then locate the symbols.)

So, go ahead and impress everyone with the cool dashboards.

Share this tip with your friends

Andrew Pearce says:

This is great BUT I am attempting to modify and adapt for a dashboard at work. with regard to conditional formatting how can I show an insignificant change ie if the % change is -1% and 1% then colour the cell contents yellow?

This way significant changes are coloured red (negative) or green (positive) with insignificant changes coloured yellow.

The collective intelligence in the office is stumped and would dearly love to come back with an answer!

Thanks in advance – Andrew

I think there is some thing wrong in the formula that you have provided. If you notice the first sheet all your data is in the C and D columns whereas your formula is making comparisons with Column E. i.e. IF E11 0. To begin with there is no data in the cell E11. And, if I go by your 2nd excel screenshot all the up and down percentages should be in column E and I do not understand why you have asked to choose the range F5:F11 and under the conditional formatting you are putting it as $E11. How can this conditional formatting be done in Excel 2007?

Leave a Reply

Your email address will not be published. Required fields are marked *

Post Navigation