Using Combo Box for a Neat Dashboard Treat!

Wednesday, December 17, 2008 0:23
Posted in category Dashboard Design Tips

- contributed by Gunjan Aggarwal
We have already learnt how to create an interactive chart using Check Boxes in our last blog.

The check box allows the user to view different sources of data in a better manner. But it’s limited to a particular metric, the user can view only one metric for the data source, like visitors or visits or page views. This is a limitation of the Check Box option.

This limitation can be overcome easily, using the COMBO BOX.

A Combo Box is a value addition to a check box, that helps in projecting the graph in a better and more meaningful manner. Combo box can deal with multiple metrics for multiple data sources. COMBO BOX together with CHECK BOX will add magic to your dashboard!

Now let’s learn how to use COMBO BOX

  1. Before inserting a Combo Box, one has to enable the related tab first. Select the ‘Excel Options’ in the main Windows button on the top left of your Excel window.
  2. Enable the “Show Developer Tab in the Ribbon” option.
    3. Excel options in Windows button

    Excel options in Windows button

    Enabling Developer tab

  3. In the Developer tab, Insert option, click on Combo Box (Form Control). You just have to take mouse over the different options to see their names.

    4. Inserting combo box

  4. Drag and draw the Combo Box at the required place in the sheet.
  5. To insert a Combo Box, we have to allocate a particular cell and also select the input range for the Combo Box (input range refers to the range that is to be controlled and displayed by the Combo Box). In order to do this, you have to right click on the Combo Box and select Format Control.

    6. Combo Box drop downs
    Formal control
    Combo box values

  6. In the data source of the chart , write an ‘IF’ Condition as follows
    Formula = IF (Combo box control cell is ‘X’, cell which contains its actual value, If (Combo box control cell is ‘Y’, cell which contains its actual value NA ()))
  7. The Above formula is for the Combo Box data, which again has to be connected to the actual data source for the Check Box for which the
    Formula = IF (Checkbox control cell is true, then give cell which contains the Combo Box formula (value for the selected combo box) NA ())
  8. Then link the data source to the Graph.
  9. Dashboard with the Combo Box and Check box is ready.

    10. Dashboard with Check box and Combo box.

What next?

If you need a sample report on using combo boxes and checkboxes, please leave your email id in a comment. And we will mail it over to you.

You can leave a response, or trackback from your own site.
Tags: , , , ,

48 Responses to “Using Combo Box for a Neat Dashboard Treat!”

  1. mukesh says:

    December 30th, 2008 at 3:19 am

    Its gr8 stuff!Instead of using 3 graphs to depict the data now i am using just one with the combination of check boxes and combo boxes.The sample report really helped.Thanks a lot Gunjan!!

  2. Gunjan says:

    January 7th, 2009 at 4:42 am

    Welcome Mukesh. Stay tuned for more Dashboard Tips and Tricks.

  3. Jason says:

    March 10th, 2009 at 10:51 pm

    Finally something I can use.

  4. Kapil says:

    May 11th, 2009 at 6:50 am

    Thats really cool!!

    I tried on the check box thing.. its working fine.. However, one small query, I am using Office 2007, but how do i colour the text in the checkbox ? (like you have done). Also please do send me the raw data file for checkbox + combo box…
    I am fairly a newbie… learning things as they come to me.. and yes, reducing the number of graphs from 3 to 1 is a very gooooood idea :)

  5. Gunjan says:

    May 14th, 2009 at 5:02 am

    Kapil,

    The text near checkboxes are written in cell. You can do formatting of that easily.

    Sample report has been sent to you.

  6. Sumit says:

    May 28th, 2009 at 12:31 am

    Hi Gunjan,

    This is very neat. Can you share the excel file for this?

    Regards,
    Sumit

  7. admin says:

    May 28th, 2009 at 12:36 am

    Have mailed to you the sample report. Have a great day! Cheers!

  8. Vanessa says:

    May 29th, 2009 at 12:54 pm

    Hi! I think this will finally solve my issues with displaying multiple charts! Could I get a sample please? Thank you so much!
    Vanessa

  9. VeriS says:

    June 2nd, 2009 at 9:38 pm

    Hi Gunjan, Thanks for your valuable information about Dynamic Excel Chart with ComboBox. Could you please send me the sample excel file for this case?

  10. gil says:

    June 9th, 2009 at 3:48 pm

    Great stuff, could i get the excel xls as well? thanks

  11. Faheem says:

    June 10th, 2009 at 6:45 am

    saw this through Chandoo’s site. Would very much like to see the way the combobox functionality is implemented.

  12. Excel-Dynamo says:

    June 11th, 2009 at 8:01 pm

    This is a excellent trick to display large data and figures is a simple graph. Well Done. Please send the sample.

  13. bobd says:

    June 15th, 2009 at 3:34 pm

    Good stuff! Gunjan, Please send me the example to study.
    Thanks!

  14. DHARMA says:

    June 16th, 2009 at 12:56 pm

    THANKS NICE TIP!

    PLEASE SEND ME WORKSHEET FOR REFERENCE PLEASAE

  15. Julio says:

    June 17th, 2009 at 6:23 pm

    Very nice example… In fact, this site is amazing. im founding different ways to work in Excel.

  16. Kapil says:

    June 18th, 2009 at 12:59 am

    Nice tips Gunjan..please send me some example to refer

  17. DIGVIJAY RAWAT says:

    June 22nd, 2009 at 6:26 am

    send me sample file

  18. Gary says:

    June 24th, 2009 at 8:52 pm

    Gunjan, this will be really useful for my reports. Can you send me the excel file. Many thanks.

  19. anusha says:

    July 6th, 2009 at 11:44 am

    this looks really good. can i get the sample excel sheet.

  20. Kapil K says:

    July 7th, 2009 at 1:06 am

    Hey thats a realy cool tip and a very quicker one… Please can you share the excel sheet so that I can learn from it ? :) thanks in advance… Your webpage is very good for me in learning as I am a new person… in excel.

  21. Kevin says:

    July 12th, 2009 at 8:40 pm

    Very nice! I saw this on the Chandoo site and I would love to see the sample.

  22. Ricky says:

    July 16th, 2009 at 2:30 pm

    this is great. can you please send the sample. thanks

  23. Ricky says:

    July 16th, 2009 at 8:57 pm

    awesome. please send the sample. thanks

  24. Rashmi Raghunath says:

    July 21st, 2009 at 9:15 am

    Nice post. Could you send a sample report?

  25. callie says:

    July 27th, 2009 at 10:59 am

    Please send sample.

  26. Mike says:

    July 28th, 2009 at 3:50 am

    Great Example!
    can you send me sample file ?

  27. CWL says:

    August 5th, 2009 at 10:26 am

    Please send me sample excel, thanks.

  28. Bhagawat says:

    August 7th, 2009 at 8:15 am

    Hi Gunjan,

    Can you please send me a sample excel file.

    Thanks,
    Bhagawat.

  29. Bharath says:

    August 10th, 2009 at 11:57 pm

    Hi ,

    Could you please send me a sample excel file for “Using Combo Box for a Neat Dashboard Treat!”

    Thanks,

    Bharath

  30. admin says:

    September 17th, 2009 at 1:44 am

    Have mailed the sample. Please mail back for any clarification. Thank you for reading the Nabler team blog. Keep coming back for more updates.

  31. Sohil says:

    September 23rd, 2009 at 5:52 am

    Its really cool.Can you please send me the sample report.

    Also can i achive this using sharepoint list.

  32. Ramesh says:

    September 24th, 2009 at 12:26 pm

    Its really cool.Could you please send me the sample report explaining the functionality of the formulae?

    Thanks in advance,
    Regards,
    Ramesh

  33. N Shivkumar says:

    October 4th, 2009 at 10:56 am

    Fantastic! Mr. Gunjan, Is it possible to send me the example to study.
    Thanks!

  34. Niladri says:

    October 8th, 2009 at 5:23 am

    Hey Gunjan,
    Good stuff!! Kindly send over the sample report.
    Thanks,
    Niladri

  35. mark says:

    October 16th, 2009 at 5:53 pm

    Thanks this is very useful, can you e-mail me the sample file also.
    Regards

  36. Paul says:

    October 22nd, 2009 at 9:06 am

    Please send me the excel sample….

  37. Chris says:

    October 23rd, 2009 at 1:23 pm

    Hi Gunjan,

    I got the check box to work great, but am struggling with the combo box. Can you send the example file please! Thanks for your help!

  38. Anish Mathews says:

    October 28th, 2009 at 4:03 am

    Hi Gunjan, Please mail me the sample file with check box and combo box.

    Thanks in advance

  39. Jan Meyer says:

    November 11th, 2009 at 2:57 am

    Really Great, I was looking for this for ages. Sending me the sample would make my day.

    Keep up the good work.

    Jan

  40. Michele Teri says:

    November 28th, 2009 at 1:16 am

    Gunjan,
    This is a great solution! I would love to have the sample file to learn from. Thanks in advance! Michele

  41. Kannan says:

    December 15th, 2009 at 11:05 am

    Gunjan,

    This is great….

    I would love to get the sample excel worksheet for my reference….

    Thxs in advance….. Kannan

  42. ghj kjgjk says:

    December 18th, 2009 at 2:24 am

    many thanks

  43. Sandeep says:

    December 29th, 2009 at 7:25 am

    Hi, this looks pretty useful, however can I achieve the same on MS Excel 2002 which is what I have at office?
    Please mail me the excel sample though, thanks in advance :)

  44. Meilin says:

    January 7th, 2010 at 1:42 am

    Please send me a copy. Thanks!

  45. Ninad Pradhan says:

    January 13th, 2010 at 10:37 am

    Nice trick. Can you please email the sample worksheet to ninad7 AT gmail DOT com [remove spcaes]

    Regards,

    Ninad.

  46. Pablo says:

    January 20th, 2010 at 10:45 am

    It looks really great! Could you please send me the example?

    Pablo

  47. Francisco says:

    February 11th, 2010 at 12:43 pm

    This is great!! Could you please share the worksheet?

  48. Rez Mannan says:

    February 12th, 2010 at 3:44 pm

    Hi,

    Can you send me the workbook with the example, please?

Leave a Reply

« Dashboard Design Tricks [Part 1]: Using Checkboxes for a neat treat
Excel Tip 3: Excel Calculation Mess »