fbpx
Search
Close this search box.

3 Steps to Create cool dashboards in excel

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

    You want to learn

    Welcome to Chandoo.org

    Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

    Read my storyFREE Excel tips book

    Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
    Rebekah S
    Reporting Analyst
    Excel formula list - 100+ examples and howto guide for you

    From simple to complex, there is a formula for every occasion. Check out the list now.

    Calendars, invoices, trackers and much more. All free, fun and fantastic.

    Advanced Pivot Table tricks

    Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

    Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

    17 Responses to “3 Steps to Create cool dashboards in excel”

    1. [...] What are your favorite data formatting tricks? [Also read : Creating cool dashboards in excel using conditional cell formatting] [...]

    2. [...] read : Create dash boards in Excel | How to draw impressive charts in Excel | Changing the default chart settings to impress [...]

    3. [...] 3 Steps to Create cool dashboards in excel | Pointy Haired Dilbert - Chandoo.org [...]

    4. 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

    5. Rajiv says:

      Hi Chandoo

      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?

      Thank you,

      Rajiv

    6. Arun says:

      Hello Chandoo,

      First of all i would like to thank you for ur awesome work.

      As Rajiv said this formula is not working i have tried it. Could you please hlep me out here.

       

    7. Geekyard says:

      I am working as in inside sales engineer and this tutorial was highly helpful for me to create a Excel dashboard and my mangers were pleased to see the report 😉

    8. vicki says:

      the formula works. He has hidden the % change in column E. This is why he is using cells E for comparison in the IF statement, and then concatenates the value from column E with the rest of the text you see in the result. Therefore the concatenation occurs in column F. He meant to say start in cell F11 and drag formula all the way up.

    9. JOHN says:

      Sir i want to make a simple dashboard involving trends in excel 2007.
      Can you give a more detailed and simplified information about hoe to make it?

    10. Ahsan Mohammad says:

      Dear Mr. Chandoo,
      Good Afternoon. please check your 3 steps Dashboard formula. I Think there is a simple mistake. Instead of E5 it mention here E11. So, please correct.
      =IF(E5>0,CONCATENATE(FIXED(E5*100,2,TRUE),"%",REPT("",10-LEN(FIXED(E5*100,2,TRUE))),"?"),IF(E5<0, CONCATENATE(FIXED(E5*100.2,TRUE),"%",REPT("",10-LEN(FIXED(E5*100,2,TRUE)))," ?"),CONCATENATE(FIXED(E5*100,2,TRUE),"%",REPT(" ",9-LEN(FIXED(E5*100,2, TRUE))),"??")))

      • Rouply christophe says:

        Bonjour Chandoo

        j'ai découvert ce site cette semaine et un grand merci pour le travail effectué et les tutoriels,je progresse à pas de géant.
        étant français,je me debrouille pour "traduire les formules" et en general je m'en sors assez bien en prenant le temps.
        Ceci-dit,je ne parviens pas à trouver la solution pour entrer les caractéres entre guillemets (triangle vers haut,le bas...) dans cette formule.

        merci de m'aider et bonne continuation

      • jay says:

        hi Ahsan,got some query???

    11. Darrern says:

      Hi Guys,

      I thought i'd share this... it achieves the same but is more efficient. Ron Coderre suggested this method

      =TEXT(K6,"#.00%")&REPT(" ",10-LEN(TEXT(K6,"#.00%")))&TEXT(K6,"?; ?;??")

    12. Sivanesan says:

      It is not working in excel, please help me..

      =IF(E11>0,CONCATENATE(FIXED(E11*100,2,TRUE),"%",REPT("",10-LEN(FIXED(E11*100,2,TRUE))),"?"), IF(E11<0, CONCATENATE(FIXED(E11*100,2,TRUE),"%"REPT("",10-LEN(FIXED(E11*100,2,TRUE))),"?"),CONCATENATE(FIXED(E11*100,2,TRUE),"%",REPT("",9-LEN(FIXED(E11*100,2,TRUE))),"??")))

    13. murali says:

      dear sir every five thousands we increased 30 rs extra how implement in excel formal

    Leave a Reply