Dashboard Design Tricks [Part 1]: Using Checkboxes for a neat treat
Saturday, December 6, 2008 2:28- contributed by Gunjan Aggarwal
Dashboard designs are one of today’s most discussed topics. The first dashboard created for a client is always a challenge. Limited time and best results.
One of the things that can be done to simplify a dashboard of a web analytical report, containing data of a lot of verticals, is using the checkboxes to make the dashboard neat and near-perfect!
Let’s assume, If we are asked to create a graph for the eight different sources of visitors, we would end up creating 8 different graphs and hyper linking it to different cells numbers or creating a single graph that would show the trend lines simultaneously in a messy manner as shown below:
Demerits of such a Graph:
- Its difficult to analyse the trend for a particular source of Visitor which is more important from an analytical point of view
- The chance of lines graph over lapping with each other is more
- The Source with less traffic may not be visible
A definite idea to showcase such a graph with different source of data is a CHECK BOX. Check Box is one among the Excel functions that would allow the user to select the required data source and view the trend for that particular source. To top it, it gives various options for the user to select a particular source of the data and view its trend, select two different sources and compare it with the other. Now, the best thing about it is that, it needs no VBA code or macros! Here, for instance, if you select Paid Search Check Box you can see a sharp drop in visitors in August which was not visible in cluttered chart. It works at the user’s option were in you can customize the graph and view only the trend that is required for the moment!
For instance: you can notice in the graph here, that when there is a drop in traffic of Paid Search in August, Direct Traffic is showing hike.
Now here’s how we can make Interactive Multiple Line Chart:
- First make simple chart and checkboxes
- Now we have to allocate one cell to control checkbox. On selection of Checkbox the cell value will be “True” otherwise False. Right Click on the checkbox, click on “Format Control”. Click last tab “Control” and under cell link type cell number where you want to give control of checkbox.
- Now below the table make another table having “If” formulas.
Formula = If (Checkbox control cell, cell which contains actual value, NA ()) - Link the chart to the table having formulas
- Finally the Graph with the Check box idea will be ready
Well, I have been researching on some of the simple dashboard design techniques for graphs. We can still make enhancements to the current post, about checkboxes. This would include using drop-down menu for the same metrics. So hope to get back with more useful stuff soon!
If you need a sample report on using checkboxes, please leave your email id in a comment. And we will mail it over to you.
Amit says:
December 11th, 2008 at 2:09 am
The article is good & the graphical representation exhibited is the article. Dashboards present real mirrors for web traffic & analysis of their behavious pattern on their web site.
mukesh says:
December 29th, 2008 at 2:48 am
Was really impressed.Looks gr8 but some links in the explation are missing. Hence need an elaboration on how to link the chart with the formula table??
Gunjan says:
January 15th, 2009 at 7:52 am
Mukesh:
To link chart with formula table,check post http://www.webanalyticsindia.com/2009-01-14/connecting-graph-to-the-source-data-in-the-check-box/
Jaime says:
April 15th, 2009 at 12:37 pm
hello
i need some input from you guys, i have an excel sheet with a check box. i need a VBA code that when the check box is checked, it take the value from 2 cells on the same sheet does an operation and puts the result in a different cell
for exaple i have the values 5 in A1 and 7 in A2, when i click on the check box it gets those values does a sum and displays the value on a different cell in the same sheet
Gunjan says:
April 24th, 2009 at 12:12 am
Jaime,
You can do this without VBA. Suppose you want result in cell A5. Then you can add formula in cell A5
=If(A4,sum(A1:A2),”-”)
Check Box is linked with A4 cell.
Krystal says:
May 21st, 2009 at 3:49 pm
Hi Guys ,
gr8 explanation. However I’m still having trouble linking the graph to the combo box. I’vee already written an offset fx to link the data (e.g Capital value, Periodic Cashflow, Net Cash flow and Cummulative totlal) to the combo box , which updates as you select the different choices. However the graph for the different variables are is nt updating when selected Where do I write the IF statement?
Chris Hunter says:
May 26th, 2009 at 4:39 pm
Would love to see the sample worksheet on graphing and checkboxes. Thank you in advance
admin says:
May 27th, 2009 at 6:18 am
Hey Chris, Hey Krystal,
Thanks for dropping by. Have mailed a sample report on check boxes to the you. Please have a look and get back to us in case of any queries or doubts.
Gary says:
June 24th, 2009 at 9:14 pm
Gunjan, Wish I would have found this site earlier. Full of great ideas. Could you please send me the excel file showing how to use check boxes with graphs. Thanks again.
ricky says:
July 16th, 2009 at 8:57 pm
awesome. please send a sample. thanks.
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.
Ramesh says:
September 24th, 2009 at 12:43 pm
Its really cool.Could you please send me the sample report explaining the functionality of the formulae?
Thanks in advance,
Regards,
Ramesh
Mark says:
October 5th, 2009 at 8:05 pm
This is great. Could you send me your example excel sheet
Bharath says:
December 3rd, 2009 at 2:40 pm
Looks gr8.. please mail me a sample
Simon says:
December 8th, 2009 at 6:25 am
Is this Microsoft 2007 or 2003? If 2003 could you send me more data about this please? Thanks
mandavi says:
February 15th, 2010 at 7:26 am
looks great , pls e-mail me template.