347 647 9001+1 714 797 8196Request a Call
Call Me

Best way to analyse webmaster tools data

May 28, 2015
, , ,

Recently, I read an article on Neil Patel’s blog about using the data from Google Webmaster tools. In that article, he categorized URLs in a list using colour codes for CTR and rankings. It took me a while to actually find a way to do that. So I thought of sharing the method that others can follow to get the excel sheet in the desired format.

There are three steps attached to this:

Step 1: Changing the numbers

According to the article, the first step that one needs to take is change the position of all the URLs/queries which rank below 10 to 10. This is easy. By using the IF function, one can easily get the result.

- IF(*cell which contains position* > 10, 10, *cell which contains position*)

- IF(D2>10,10,D2)

using if function in webmaster tools data

Step 2: Colour coding the cells

In the next step, Neil asked us to "Highlight all rankings in the 1st, 2nd, 3rd, or 4th position in green. Highlight all rankings below that in red”.

It took me some time to figure it out but I finally managed to find the answer.

Conditional Formatting!

- First copy-paste the values from the last step into the positions column. Select, copy and paste (Alt+E+S+V).

- Click on conditional formatting -> New Rule

- Select the second option “Format only cells that contain”, add values in the boxes and then click on format to select the colour.

using conditional formatting to filter webmaster tools data

- Do it similarly for colouring a cell red for position between 4.1 and 10.

- Shortcut to use conditional formatting and select new rule: “Alt + H + L + N”.

Step 3: Colour Coding CTR column using data from position column

This was the toughest task and again Conditional formatting came to the rescue.

In the next step, Neil said, “As for click-through rates, highlight keywords that rank in the 7th, 8th, 9th, or 10th spot and have a click-through rate greater than 3% in green, and, of course, use red for scores that are lower than 3%.

As for keywords that rank in the 4th, 5th, and 6th spot, highlight the click-through rate in green if it’s greater than 8% and in red if it’s lower than 8%.

For keywords in the 1st, 2nd, or 3rd spot, highlight them in green if the click-through rate is greater than 20% and highlight them in red if it’s lower than 20%.”

For this, you need to create rules in conditional formatting using formulas.

creating rule in excel conditional formatting

I used the formulas below to get the colour codes Neil was talking about.


=(AND(D2>=4,D2< =7,C2<8%))



=(AND(D2>=4,D2< =7,C2>8%))


Note: You need to add each formula individually and add the colour. Phew!

And this is how the sheet finally looks:

analysing webmaster tools data

This is a great method to filter out the pages/queries which are working well and the ones you need to work on. Analysing and working on this should help you in improving your website’s traffic.


About the Author

Think. Apply. Learn. Repeat.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 76273