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)
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.
– 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.
=(AND(D2>7,C2<3%))
=(AND(D2>=4,D2< =7,C2<8%))
=(AND(D2<4,C2<20%))
=(AND(D2>7,C2>3%))
=(AND(D2>=4,D2< =7,C2>8%))
=(AND(D2<4,C2>20%))
Note: You need to add each formula individually and add the colour. Phew!
And this is how the sheet finally looks:
Related links you will like:
Google expands its knowledge graph to health related queries
Comments