3 Steps to Create cool dashboards in excel

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.

So, a cool dashboard would look something like the below one:

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.

Comments

RSS feed for comments on this post. TrackBack URI

Leave a comment