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
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:
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:
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
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:
Advanced Sheets formulas for SEO
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
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:
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:
=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’ “)