Web Analysis : Analyzing with ‘Microsoft Excel’

Webmaster/SEO professional usually uses analysis tool like Excel for analysis (searching, filtering, sorting, comparison and alerts) to keep track of the past and current web performances and efficiency.Here are the Microsoft Excel skills every webmaster/SEO professional should possess.



1. Functions
CONCATENATE

Microsoft Excel definition: Joins several text strings into one text string.

Syntax: CONCATENATE(text1,text2,…) ->
Alternative Syntax: text1&text2
excel_concatenate
The formula used for the above scenario is : =CONCATENATE(A2,B2)
Alternative Formula is =A2&B2


You may also add text string before, after or in between the text string by adding a double quote like this :

Syntax: CONCATENATE(” “, text1,” “,text2,” “,…)
Alternative Syntax: ” “&text1&” “&text2,” ”
excel_concatenate1
The formula used for the above scenario is : =CONCATENATE(“http://”,A2,”.”,B2,”/”)
Alternative Formula is =”http://”&A2&”.”&B2&”/”

COUNTIF

Microsoft Excel Definition: Counts the number of cells within a range that meet the given criteria.

Syntax: COUNTIF(range,criteria)

countif
The formula used for the above scenario is : =COUNTIF(A:A,”None”).. Change the text string in the double quote to the name you would like to count.

For instance, to count the number of christine in the column, change the “None” to “Christine”. The new formula will look like this =COUNTIF(A:A,”Christine”). Simple!

IFERROR

Microsoft Excel Definition: Returns a value that you specify if a formula evaluates to an error; otherwise, it returns the result of the formula. Use IFERROR to trap and handle errors in a formula.

Syntax: IFERROR(value,value_if_error)

iferror
The formula used for the above scenario is : =IFERROR(A2/B2,”None”). The formula will take value in column A, row 2 and divide it with the value in column B, row 2. The division will return a value or 0. In this case, if 0, the formula will return value as “None”.

TEXT TO COLUMN

Microsoft Excel definition: Distribute the contents of one cell across separate columns.

No Syntax.

1. Highlight the row content that need to be separated
texttocolumn0

2. Go to “Data” tab on the top menu panel.
texttocolumn1

3. Go to “Text To Column” button.
texttocolumn2

4. A pop up window will appeared. Select “Delimited” and click “Next”.
texttocolumn3

5. Choose “Other” then key in “/”
texttocolumn4

6.Format cells (skip for this example) and select a destination (it’s ok to select default destination )
texttocolumn5

7.Done
texttocolumn6


2. Convert range to a table using Ctrl+L (Windows) & Ctrl+T (MAC). The converted table make sorting and filtering data easier.

1. For instance you have raw file consisting 2 columns which you want to convert to table.
table1

2. Highlight the cells you want to convert
table2

3. Then, press CTRL+L and a pop up window will appear. Click OK to create table.
table3

4. Finally, a new table is created.
table4

5. To filter or sort, click on the button on the bottom right of every cells.
table4a

6. You will see this drop down menu. Select the available options to sort or filter
table5

  

21 thoughts on “Web Analysis : Analyzing with ‘Microsoft Excel’

  1. Christmas Light Installation Dallas FortWorth says:

    hello there and thank you for your info – I have definitely picked up something new from right here. I did however expertise some technical points using this web site, as I experienced to reload the website many times previous to I could get it to load properly. I had been wondering if your web host is OK? Not that I’m complaining, but sluggish loading instances times will often affect your placement in google and can damage your high-quality score if ads and marketing with Adwords. Anyway I am adding this RSS to my email and could look out for a lot more of your respective fascinating content. Ensure that you update this again soon..

    http://www.christmaslightinstallationdallasfortworth.com/