Web Analytics India

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

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.