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’ “)