Earlier in the year, our PPC Manager, James, wrote a post about how he uses Excel for PPC. Following on from his post, I’m going to show you some useful formulas and shortcuts which can be used for SEO purposes.
When starting at Crafted 18 months ago, my Excel knowledge stretched to summing data within a column. Now, the majority of my know-how stems from using Excel on a daily basis and looking for tips and tricks in other blog posts. By finding quick ways to manipulate data, I can spend more time analysing the results to gain useful insights and plan actions based on these findings.
Below details many of the tools, formulas and shortcuts that I use regularly to perform data analysis efficiently.
Text to Columns
‘Text to Columns’ allows you to split up a text string, by ‘,’ ,‘ ’, ‘:’ or a character of your choice, / ? etc. This is a very useful tool when trying to isolate a portion of a URL. After using ‘Text to Columns’, I often use the ‘Remove duplicates’ tool located next to the ‘Text to Columns’ button, to find unique cells.
The reverse of ‘Text to Columns’, this function combines cells. You also have the ability to add characters in between these cells, for example ‘/’, ‘.’, ‘-‘.
There are many aspects to this section. I mainly use the duplicate values tool and ‘Text That Contains’, which highlights a particular keyword within a cell. This is useful for sorting through a website’s backlinks. Both of these functions can be found within the ‘Highlight Cell Rules’ within the ‘Conditional Formatting’ section.
When I need to know how many times a cell or a portion of a cell appears within hundreds of rows of data, the CountIF function is essential. For example, if you want to see how many times ‘dog toy’ is mentioned in cells, use =COUNTIF(A:A,”dog toy”) or =COUNTIF(A:A,A4).
Counting words in each cell
The LEN function tells you how many characters appear within a cell. For SEO purposes there isn’t a huge use for this, apart from counting cell length for title tags. A more useful extension is to use this to find out how many words are in each cell. The formula for this is: =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1).
A useful purpose for this would be to calculate the number of anchor texts with that number of words. For example, to find the number of keywords equal to and above say four keywords, use: =COUNTIF(A:A,">=4").
A really handy formula when trying to find a cell within a table and then displaying a corresponding value: =VLOOKUP(lookup_vaue,table_array, col_index_num,range_lookup). For example, if you were looking to see how many dog toys were sold, the formula would be: =VLOOKUP(D2,A2:B7,2,0).
So what does this formula show? D2 is the cell you’re interested in, A2:B7 is the table in which you want to look, ‘2’ is the number of the column in which your data is held and finally ‘0’ displays the exact match to the question. One limitation in using a VLookUP, is that the cells in which you’re interested have to be on the far left of the table, in this case ‘Product’.
When exporting annual comparisons from Google Analytics, the data is seldom in an easy format. By using the OFFSET function, the data can be transposed into an easy to use table. For example, if you wish to turn the format of the data from,
You’ll need to use the =OFFSET($A$2,(ROW($A3)-2)*2,0). The screen shot below shows the formula for each of the cells.
Aside from the obvious, copy, paste, find shortcuts etc., I’ve found the below useful to speed up everyday tasks:
- Ctrl D copies the cell above
- Ctrl R copies the cell to your right
- Ctrl - removing the whole row or column
- Ctrl + adds a new row or column
For more information on the above functions please see:
By Rachel Tooke