Web Analytics India

- Contributed by Gunjan Aggarwal

We all like conditional formatting for the fact that data can be better projected that way. It makes data easy to read and conclude. It can be used in creating the Interactive dashboard, Routine reports, Adhoc reports and many more places where the Graphs play’s a vital role.

Imagine how conditional formatting can make your chart look attractive and interesting. In this article we will learn how to do that.

1. Make a simple table with the required data
2. In the subsequent Column insert the required formula, In our example we have set the standard to be 500 Visits, Accordingly the formula is written as:
   =IF(Visits<500,0,NA()) – RED {Less than 500 Visits}
   =IF(Visits>=500,0,NA()) – GREEN {More than or equal to 500 Visits}
   
3. Now create column chart (A1 11)
   
4. Click on Chart. Go to “layout” Tab. Click on drop down and select “Red” series
   
5. Click on “Data Labels” -> more data label options
   
6. Select “Category Name” and “below” option
   
7. Change the data label font on the Axis to ‘Red’ or ‘Green’ Color as required
   

Finally you will arrive at the chart with conditionally formatted axis that adds up glamour to your reports and dashboards.

Note: The logic is applicable not only to Vertical Bar Graph but also to other chart types like Line graph, horizontal bar graph etc.,

If you need to know more about any of the dashboard or chart tricks, please leave your email id in a comment. And we will do our best to reply to your queries.

- Contributed by Gunjan Aggarwal

In earlier blogs, we learn how to create interactive charts with the help of checkboxes. Those who have missed that blog post can read the article on using check boxes for a neat treat .
It’s very much necessary for a web analyst to analyze the trend and figure out the reason for the hike or fall in the traffic at a given point of time. If it’s a Normal Trend Chart, we can insert a text box (Auto shape) with added comments beside it to reason out the Hike or the downfall. But if in case we are using Interactive Chart alone with Check Boxes, then inserting an Auto Shape for comments would not be the right thing to do, because it remain constant and appear on other section graph when selected.

To overcome this limitation, we will show you in this post how to add comment in interactive charts, without affecting any other graph.

In the above chart, you can see a hike in organic Search traffic on 14th of December. To add a comment directly at the point of Hike, Please follow the steps jotted below:
1) Select that data point.
2) Right click on data point

3) Click on “Format Data Point”.
4) Select “Marker Options” -> Built-in ->Type “circle” ->Size 7

5) From “Marker Fill” select required color

6) Your Data point will look like this

7) We have highlighted the point of hike. But now to add “comment” go to source data

Instead of Date “14th Dec” write the reason behind the hike, I have written “High Traffic due to Key phrase ‘Outsource Web Analytics’

On that Particular data point again right click and select “Add Data Label”.

9) By Default, it gives value of that point. Again right click and select “Format Data Label”.
10) Select “Category Name” under Label Contains section

11) This is how your trend line with comment would look like

12) You can also format and move the comment box
13) After formatting, our final chart will be

Just tryout and please leave your valuable feedback.

If you need a sample report on any of these dashboard tricks, please leave your email id in a comment. And we will mail it over to you.

 - Contributed by Gunjan Aggarwal

A layman who would look at the above graph, at his first glance would tell that the Paid Search had not performed well in the initial months, or HE/SHE would think that there must be some changes happened in the Paid Search Section in the month of June which in turn shows hike from July onwards
Through this trend in first glance you will say that Paid Search didn’t yield any visitor in the month of April, May and June. But the fact is Paid Search Campaign started in July.
Now how to show trend line starting from July onwards is a trick.
Goto your Data Source

In the Paid Search Section Replace the “0” for which the data is not available with the formula “=NA()”.

By making a minor change in the data source the Graph can be depicted in more meaningful manner as shown below:

A person looking at the above graph would understand that the Paid Search has started from July onwards.

If you need a sample report on any of these dashboard tricks, please leave your email id in a comment. And we will mail it over to you.