Web Analytics India

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