Google Spreadsheets Hacks for SEOs
Quality SEO is impossible without services and automation. But when it comes to data analytics nothing can compete with MS Excel (or Google Spreadsheets), probably the most under-rated tool in the industry.
Back in the days, I used to think that profound Excel knowledge was a prerogative of Business Analysts and Accountants. My usage of the iconic programme was bound to filtering results for keywords and competitors or simply storing some basic information.
Things have changed drastically over the last years. When facing a new challenge in my day-to-day SEO routine, first of all, I consider if there is a way of doing it with the help of Excel. And in many cases, it works.
So how is that?
Excel is at our service when doing keyword research, competitors’ analysis, snatching websites metadata or status codes, spinning texts, etc.
VLOOKUP Function
How it helps for SEO: find and retrieve data from a specific column in the neighboring sheet.
This is a very useful formula if you need to transfer data from one sheet to another by matching the common data. It has too many use cases to describe here so I will showcase the most popular situation.
Use case:
For example, you have downloaded separate competitors’ reports from Ahrefs and Semrush and would like to combine the data in one sheet.
This is when VLOOKUP comes into play. You have to specify the cell you are matching (A2), the Sheet and range you are wanting to pull data from (Sheet2!A:B) and the number of the column from left to right to search for data (2). Zero stands for FALSE and means we are looking for exact matches on both sheets.
=(VLOOKUP(A2,Sheet2!A:B,2,0) >> =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),"")
You can put it on steroids and wrap up into IFERROR functions if you want to get rid of N/A’s in your results. Instead, you will see blank cells that is much more convenient and eye-pleasing.
There is one more essential variation of the VLOOKUP that simply tells us whether a cell item is found in a specific range on the same or another sheet. I simply cannot count the number of times I use this function during the work week.
=IF(IFERROR(VLOOKUP(A2,Sheet2!H:H,1,FALSE),"")="","",1)
Use case:
You need to check if your sites are among the Alexa top million. So you have two lists. A short one with your domains on Sheet1 and a million domains from Alexa on Sheet2.
The above-mentioned function will place the digit “1” against each your website (Sheet1) that is found on Sheet2.
REGEXP
Regular expressions deserve a separate guide and might be overwhelming at first. I bet you will definitely want to master its power once see the potential advantages.
When it can be useful: to extract the host from the URL.
=IFERROR(TRIM(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(A2,"https?://",""),"^(w{3}\\.)?","")&"/","([^/?]+)")),"")
IF Function
Simple yet powerful, it can be of great help.
When it can be useful: imagine you need to identify the positions for the URLs in SERP (happens quite often when downloading top-100 via Scrapebox, for instance).
=IF(C2=C1,A1+1,1)
Pivot Tables
Despite minor differences between desktop MS Excel and Google Sheets, Pivot Tables do a great job turning loads of data into a sweet report.
Most Often Used Excel Formulas for an SEO specialist
COUNTIF/SUMIF
=COUNTIF(A2:A30,">100") – counts the number of entries above 100
=COUNTIFS(AI2:AI30,">25",AI2:AI30,"<50") – counts the number of entries more than 25 but less than 50
=SUMIF(A1:A6000, "domain.com",X1:X6000) – calculates the sum of entries in column X if domain.com is fount in column A
=SUM(COUNTIFS(AI2:AI26,{">200","<300"})) – calculates the sum of entries above 200 but less than 300 in column A
=SUMPRODUCT(--(ISNUMBER(FIND(text,rng)))) – counts the number of entries from one list into the other list
TRIM
=TRIM(A2) – deletes unwanted symbols before and after the keyword
TRANSLATE
=GoogleTranslate(A2,"en","de")
— Import data from other Sheets —
=IFERROR(VLOOKUP(A2,sheet2!A:B,2,0),"") – Import data from another Sheet
=IF(IFERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE),"")="","",1) – Check if an item is found on another Sheet in column A
=query({sheet1!A2:H5;sheet2!A2:H5},"Select * where Col1 is not null ") – combine data from other sheets in one master-sheet
— Get host or TLD from URL —
=IFERROR(TRIM(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(A2,"https?://",""),"^(w{3}\\.)?","")&"/","([^/?]+)")),"") – Get host from URL
=iferror(trim(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(A2,"https?://",""),"^(w{3}\\.)?","")&"/","([^/?]+)"))) – extract domain from URL in Google Sheets
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,".","*",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))) – get TLD from URL
=if(regexmatch(B8,"^https?://[^/]+/?$"),"home","inner") – identify the page type (home or inner)
— Check website status code online —
=HTTPResponse(A2) – Get Status Code of the URL in Spread Sheets Copy&paste the function into a script editor. Moreover, you can schedule the response code monitoring by modifying the code – https://www.atulhost.com/http-response-code-google-sheet
function getStatusCode(url){var response = UrlFetchApp.fetch(url);return response.getResponseCode();}
— IF —
=IF(AND(A1="this",B1="that"),"x","")=IF(ISNUMBER(SEARCH("/",A1)),"inner","home") – check if cell contains a symbol “/” (you can replace it with whatever you want).
=IF(B2=B1,A1+1,1) – specifies the positions for URLs if the query is listed in column B
— Conditional Formatting —
=MOD(ROW(),10)=2 – highlights every tenth row in a selected table (starting from the second row).
— Tricks with Text —
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1) – Count Words in a Cell
=IFERROR(LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)-1),A1) – trim longtail keywords to 3 words or =RegexpReplace(A1,"^([^\\s]+ +[^\\s]+ +[^\\s]+).*$","$1")=LEFT(TRIM(F49),FIND("",SUBSTITUTE(F49," ","",LEN(TRIM(F49))-LEN(SUBSTITUTE(TRIM(F49)," ",""))))-1) – Trim the last word in a cell
=RegexpReplace(F2," ([^\\s]{1,3})$","") – Trim short words (1-3 symbols) at the end of the cell
=REPLACE(A1,1,1,UPPER(LEFT(A1,1))) – Capitalize only the first letter in a cell
=MID(D2,FIND("@",SUBSTITUTE(D2," ","@",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))-1))+1,255) – Leave the last two words in a cell
=LEFT(SUBSTITUTE(A1," ","|",4),FIND("|",(SUBSTITUTE(A1," ","|",4)))-1) – first four words in a cell
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),180)) – get the last three words in a cell
=SUBSTITUTE(C2,B2,A2)=IF(ISNUMBER(SEARCH("/",A1)),"inner","home") – if cell contains, than this or that
Useful sources of information about Excel:
- https://exceljet.net/
- http://howtouseexcel.net/
- https://seotoolsforexcel.com/
- https://ahrefs.com/blog/google-sheets-formulas-seo/