Best Google Sheets Formulas for SEO & Content Audits
I love spending time knee-deep in data and have been working my Google Sheets skills to build dashboards and generally make better and more complex content audits.
This is a list of the Sheets formulas I use regularly and couldn’t live without. Each one has countless uses and can be manipulated in so many more ways than I can show here.
What did we do before spreadsheets and VLOOKUP formula?
Basic Sheets formulas for SEO
IFERROR
Example:
=IFERROR(SUM(A1:A10))
Wraps another formula to remove the error #N/A and leaves a cell blank when there isn’t a value
Especially useful in combination with VLOOKUP:
=IFERROR(VLOOKUP($A1,’Spreadsheet’!A:B,2,FALSE))
LEN
Example:
=LEFT(A1,LEN(A1)-10)
Extracts content from another cell and removes/extracts characters from the left or right
Useful for extracting the path or removing https:// from a string:
=RIGHT(B1,LEN(B1)-8)
SPLIT
Example:
=SPLIT(A1, “?”)
Splits the contents of a cell into different columns around a delimiter, eg. a ‘?’ or ‘blog’
Useful for removing the URL parameter on tracking IDs from GS data or splitting the taxonomy of URL structure
=SPLIT(A1, “/”)
SEARCH
Example:
=SEARCH(“blog”, B10)
Searches the content in cells to match a value, such as ‘blog’ or ‘domain’.
Useful in content audits to search for categories, blog pages or for a specific keyword such as ‘boots’. Use in combination with IF:
=IF(SEARCH(“blog”, B10)
=IFERROR(IF(SEARCH(“blog”, B10))
Advanced Sheets formulas for SEO
VLOOKUP
Example:
=VLOOKUP($A1,’Spreadsheet’!A:B,2,FALSE)
Curate data from different sources and sheets into one Sheet, the Swiss Army knife of formulas
Useful for creating a content audit or reporting dashboard. Use with IFERROR to avoid error messages when no data is available
=VLOOKUP($A1,’Spreadsheet’!A:B,2,FALSE)
=IFERROR(VLOOKUP($A1,’Spreadsheet’!A:B,2,FALSE))
IMPORTXML
Example:
=IMPORTXML(“domainname.com”,”//title”)
Import data via XML from another source into a Sheet, scrape links, titles, descriptions, emails or any data with an Xpath tag from a web page
Useful to scrape all titles from a SERP for a KW, drop the SERP url into cell A2 and add this formula:
=IMPORTXML(A2,”//h3″)
IMPORTRANGE
Example:
=IMPORTRANGE(“spreadsheet–URL”, “data-range”)
Import data from another spreadsheet into a new Sheet
Useful to import sales figures, GSC data, URLs, lists of products or any data from another sheet:
=IMPORTRANGE(“spreadsheet–URL”, “Tab!A1:A20”)
QUERY
Example
=QUERY(“range”, “SQL query”)
Import data from another list of data or another Sheet (if used with IMPORTRANGE) with a list of conditions using SQL
Useful in a content audit or to create a dashboard. Pull in URLs that are tagged with a specific intent such as BOFU, have a DA above 60, GSC clicks above 1,000 or all of these combined
=QUERY(ALL!B12:E12, “select B where E ‘BOFU’ “)
=QUERY(ALL!B12:E12, “select B where E <‘10000’ “)