Web Analytics India

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


Excel options in Windows button



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

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.

- 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:

1. First make simple chart and checkboxes
2. 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.



3. Now below the table make another table having “If” formulas.
   Formula = If (Checkbox control cell, cell which contains actual value, NA ())
4. Link the chart to the table having formulas
5. 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.