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.

- Contributed by Deep Moni Hazarika

Counting the length of a string or characters is easily done in excel. Just the LEN() function and simple. But the one day I was asked if there was any way to count a particular word in a cell which had a long sentence. That sounded tricky but needed some fact finding. So the problem was to find a word that occurs repeatedly in a sentence which happens to be in a cell in an excel sheet. Though I don’t know of any direct function or method to calculate out that, I was able to find out the solution with a combination of functions.
Considering the cell with the sentence is B3, the combined function to use is
=(LEN(B3)-LEN(SUBSTITUTE(B3,”Word_To_Count”,”")))/Length_Of_The_Word_To_Count
e.g. Suppose we want to know how many times the word “subject” appears in the sentence “subject matter of the subject in the name of the subject” in cell B3 of your workbook, the formula will be
=(LEN(B3)-LEN(SUBSTITUTE(B3,”subject”,”")))/7
Now let’s try to understand the function. The first use of LEN() calculates the length of the complete string. In the second use of LEN(), we first use SUBSTITUTE() with the word “subject” to be replaced by ‘null’. In the process, SUBSTITUTE() will result in the sentence removing the word “subject”. LEN() is then used to calculate the length of this reduced string. The difference of these is the total length of missing words “subject” and this result divided by the number of character in “subject” itself will result in the number of times the word appeared in the sentence. Isnt that tricky?
If you know of any other shorter or smarter method, do comment.

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 Deep Moni Hazarika

What’s wrong with the calculations below? Hint: Related to Excel 2007

This is one of the problem numbers (there are many!) for Excel 2007. If the problem is still not decipherable, here’s the answer. These are the multiplication of two numbers A and B which gives the wrong result in Excel. Multiplying 77.1 with 850 should return 65535, but Excel 2007 calculation has messed it up here. It appears that Excel 2007 also needs some multiplication tables tutoring. Haha!

One of the employees of Microsoft posted about this flaw in the MSDN blog, also making it clear that the error is in the presentation and not in the calculation. In the above example, multiplying the resulting cell with 2 correctly gives 131070. Anyhow, what matters to us is how we are able to see the result. So was there a fix for that?

 Yes! There is a solution and that’s the good news. The fix to error was made available in the Excel 2007 hotfix package. Information about the fixes are also available in Microsoft Office Suites Service Pack 1.

Click here to directly download the Excel 2007 hotfix package.
Click here to directly download the 2007 Microsoft Office Suites Service Pack 1.

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 Deep Moni Hazarika

Most of us had well adapted ourselves to the excel 2003 interface and were able to find the required menu options and controls when needed. And when most of us got pretty comfortable with Excel 2003, Microsoft introduced us to the newer baby of spreadsheets – Excel 2007. Fantastic. Learning doesn’t end there. We are now forced to learn the new interface, figure out where the “File”, “Edit” and those options under the earlier menu and toolbar structure has gone and also understand and memorize this new tab structure. There started another round of talks, likes and dislikes. Well, the window makers expected that, I guess. That’s why they didn’t mind, completely changing the menu structure and introducing the ribbon concept, the very first thing that would surprise first time users of Excel 2007. Excel 2007 looked different and attractive, saved the file in a newer file format, as with other office applications and above all, needed schooling of its new ways.

Questions floated around everywhere – Yahoo Answers, Excel Expert blogs and of course Microsoft provided answers to those in their knowledge base, online and offline as well.

Then – MS Excel 2003

Now – MS Excel 2007

They have drawn a radical uplift at looking at the spreadsheet and also other much used office tools. And then slowly we realized that this new concept actually saved time in our usual work. Well yes, initial few struggling days can’t be ruled out. But Microsoft had help documents, guides and FAQs ready for any kind of questions, considering an average user migrating from Excel 2003 to Excel 2007. If you are not able to find a command or feature, probably you are not looking at the right place.
If you are still wondering as to where the familiar menus and toolbars have been mapped onto the present Excel 2007 download the Excel Ribbon mapping workbook.

There is also an interactive reference guide available for the confused ones that is pretty much helpful.

Preview of the interactive reference guide

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 Deep Moni Hazarika

A colleague once pinged me “How do I copy a value written in a cell in all the cell below it?” Well, almost anyone familiar with basic excel usage might have figured that out. Anyhow, I wrote back in the chat window explaining how to do it. Didn’t work! I called up the number at his desk. Tried explaining how to do it…. “double click on the black dot at the lower right corner of the selected cell. If there are values to the adjacent column, that value will get duplicated in the column till there are values in the adjacent column”. These things I am sure most of us had figured out on our own. No, don’t blame my colleague to be stupid. Unexpectedly, speaking over the phone also didn’t help him. Then I walked up to his desk. Wait a minute. Where’s the black dot at the lower right end of the selected cell. That explains why it didn’t work through chat or even explaining over the phone. Well, I couldn’t address that problem. There has to be some setting for that I guess.

Long time back, while working on Perl programming, I had learnt “There is more than one way to do it” or TIMTOWDI. Python programming however rejects that saying “There should be one obvious way to do it right”. Well, in this case I didn’t want to stick to the obvious way. The things needed to get done, whichever way!

I went ahead showing the other way to do it. Just select the cell with the value you want duplicated. If you want the value to be same in 10 subsequent cells below, hold shift button and left click on the 10th cell below. In the process, all 10 cells in the column are selected. Now type CTRL+D and Bingo! There are 10 cells in the column with same value. That copied the value downwards. In case this is needed horizontally in a row and not down a column, typing CTRL+R will copy the value sideways. He was cheerful and thanked me.

There are many more such small tricks and workarounds and I shall be coming up with them one after another. Keep checking for future reads!