11 Google Sheets Formulas SEO Professionals Must be Aware of

Search engine optimisation (SEO) professionals rely on Google Sheets to manage various processes within content marketing. Keyword research, competitor analysis, and technical audits are some of the many SEO tasks that require Google Sheets.

Sometimes, the datasets related to these tasks could be huge. As you may have already experienced, working with large datasets can be difficult, leading to slow processing times, formula errors, and difficulty in extracting actionable insights.

In this article, let’s look at 11 key Google Sheets formulas for SEO to automate repetitive tasks, speed up data processing, and enhance the accuracy of your analyses.

Google Sheets Formulas for High-Level SEO Tasks

1. QUERY

The QUERY SEO formula filters, sorts, and manipulates data efficiently, a bit like SQL. This allows you to perform nuanced SEO spreadsheet operations without any complex formulas.

Syntax: QUERY(A1:D100, "SELECT A, B WHERE C > 50", 1)  

The above example retrieves entries from the A column if their corresponding value from the C column exceeds 50.

The key applications for this Google Sheets formula for SEO include:

• Extracting specific keyword rankings
• Filtering backlinks based on domain authority
• Segmenting SEO data (like organic traffic per page)

We frequently leverage the QUERY functionality to extract specific information from in-depth content audit sheets. For instance, here is what a typical content audit sheet may look like:

Imagine you have to extract the articles that need to be rewritten. Doing so manually can be exhausting and error-prone. QUERY solves that issue with one command: 

=QUERY({'Content Audit'!A4:AO}, "Select Col1,Col2,Col3,Col4,Col5,Col11,Col21,Col22,Col28,Col15,Col25,Col26,Col27 WHERE Col38 = TRUE order by Col1 Desc",1)

Here’s what the above expression does for you:

• Extract specific columns from row 4 onward in the "Content Audit" sheet.
• Filters only the rows where Column 38 is TRUE.
• Sorts the results in descending order of Column 1.
• Returns the selected columns in the specified order.

The resulting sheet may look like this:

2. REGEXMATCH

The REGEXMATCH SEO formula allows you to see if the condition matches the regular expressions and provides the YES or NO response accordingly.

Syntax: REGEXMATCH(text, regular_expression)

This Google Sheets formula for SEO can be used for:

• Classifying the URLs based on content type
• Segmenting keywords according to funnel position and SERP features, etc.

Consider you have a list of keywords on your SEO spreadsheet. Each of those keywords triggers certain SERP features, such as Sitelinks and People also ask. 

Let’s say you want to identify the keywords that only trigger the People Also Ask SERP feature.

You can use REGEXMATCH with the IF function as follows: =IF(REGEXMATCH(C2,”People also ask”),”Yes”,”No”).

source

This is better than leveraging VLOOKUP for SEO as the formula could get more complicated:

=IF(NOT(ISNA(VLOOKUP("People also ask", C2, 1, FALSE))), "Yes", "No"). 

Additionally, VLOOKUP will malfunction most of the time as the cells in the C column contain multiple values such as “Sitelinks” and “People also ask.” VLOOKUP for SEO works efficiently only when there is an exact case match.

3. REGEXREPLACE

The REGEXREPLACE formula allows you to replace parts from strings or cells using regular expressions.

Syntax: REGEXREPLACE(text, regular_expression, replacement)

Use cases for this SEO spreadsheet formula: 

• Refreshing metadata like title tags or meta descriptions
• Updating URLs for site migration purposes e.g. changing CMS or platform, etc.

We used REGEXREPLACE to update our page titles by replacing “Supple” with “Supple Digital” with the formula: =REGEXREPLACE(A2,”Supple”,”Supple Digital”).

source

You can also leverage the SUBSTITUTE Google Sheets formula for SEO but REGEXREPLACE is better when dealing with patterns, complex rules, and multiple replacement instances.

4. REGEXEXTRACT

The REGEXEXTRACT SEO formula lets you extract matching substrings from strings or cells using regular expressions. 

Syntax: REGEXEXTRACT(text, regular_expression)

SEO use cases:

• Finding specific information from log files
• Extracting email IDs or URLs from blocks of text
• Pulling domain names from a list of URLs, etc.

A great example could be extracting domain names from the list of your backlinks. Just mention the pages that link back to your website and use the following SEO spreadsheet formula: =REGEXEXTRACT(A2,”^(?:https?://)?(?:[^@n]+@)?(?:www.)?([^:/n]+)”).

source

5. SPLIT

As the name suggests, the SPLIT formula helps you split the data of one cell/column into multiple cells/columns. 

Syntax: =SPLIT(text, delimiter) 

This SEO formula’s use cases include: 

• Breaking URLs into multiple parts such as protocol, domain, path, parameters, fragments, etc.
• Splitting full names into first and last names
• Putting comma-separated values (CSVs) into separate columns, etc.

We’ve broken some of our blog’s URLs into their constituent parts with the SPLIT SEO spreadsheet formula: =SPLIT(A2, “/”).

source

6. UNIQUE

The UNIQUE formula allows you to identify and remove duplicate data from Google Sheets. 

Syntax: =UNIQUE(range)

The Google Sheets formula for SEO can be used for: 

• Organising keywords
• Creating a list of unique datasets by removing duplicates
• Identifying duplicate keywords or URLs, etc.

During keyword research, for example, you may discover multiple repeating keywords. The UNIQUE function eliminates the duplicate instances to give you a crisp list of key terms to target: 

source

7. ARRAYFORMULA

The ARRAYFORMULA allows you to apply one formula to multiple cells in a row or column.

While the drag-down action achieves the same effect, the ARRAYFORMULA technique offers two additional benefits:

• Convenient for large datasets
• No redo if the formula is changed

Syntax: ARRAYFORMULA(array_formula)

This SEO spreadsheet formula can be used with other Google Sheets expressions across multiple rows or columns. We frequently leverage this function to segment content based on their type or category for managing the site taxonomy better.

You can identify guide pages among others, for instance, by using the formula: =Arrayformula(IF(SEARCH(“/guides/”,A2:A10),”YES”,””)).

source

The ‘#VALUE’ error can be avoided easily. We did so for the next column that marks whether the page contains an SEO tool with the updated expression: =IFError(arrayformula(IF(SEARCH(“/tools/”,A2:A10),”YES”,””)),”No”).

source

8. IMPORTRANGE

The IMPORTRANGE SEO formula lets you import data from different files or workbooks into your master database. This is an efficient method to bring all your crucial SEO data to one place.

Additionally, the master database updates automatically if you make any edits to the source file, enhancing transparency across the content team.

Syntax: IMPORTRANGE(spreadsheet_url, range_string)

Use cases for this SEO spreadsheet expression include:

• Combining multiple Google Sheets into a single sheet
• Importing data from different files into a master sheet, etc.

Consider the following Google Sheets doc  that highlights the keyword and its difficulty:

source

We can move the keyword difficulty scores to another sheet, where just the keywords are mentioned by using IMPORTRANGE (VLOOKUP): =VLOOKUP(B2,importrange(“https://docs.google.com/spreadsheets/d/1W9AOt5EFLD7_oBZ8Xd2HhbQgr-pckanw4LguwIY8c3I/edit”, “Difficulty!$B$2:$C$54”),2, False).

source

As long as the URL in the formula remains intact, any edit to the root file will be reflected in the master sheet. This will help you collaborate more effectively with internal and external stakeholders.

9. IMPORTXML

With the IMPORTXML SEO formula, you can import data from web pages using an XPath query from various structured data types like XML, HTML, CSV, RSS, etc. 

Put simply, you can scrape the data from the web without leaving your spreadsheets. 

Syntax: IMPORTXML(url, xpath_query)

SEO use cases: 

• Scraping data from different web pages
• Import on-page data without using SEO tools, etc.

For instance, you have a list of URLs and want to get the titles in the adjacent cells without leaving the spreadsheet. 

Just insert the SEO spreadsheet formula: =IMPORTXML(A2,”//title”). 

Now the Google Sheets algorithms will scrape the on-page data of the said URL and import the title tag into the corresponding cell.

source

Similarly, you can import other on-page data like:

• Meta descriptions
• Headings and subheadings
• Meta robots data
• Hreflang attributes
• Canonical tags, etc.

10. GOOGLETRANSLATE

GOOGLETRANSLATE, as the name suggests, changes the language of the contents of one cell to another. This helps global brands to make their content more accessible to a wider audience through localised SEO.

Syntax: =GOOGLETRANSLATE (A2, “en”, “fr”)

The above example command translates the content from cell A2 from English to French.

Below we tried to translate to Spanish with the SEO formula: =GOOGLETRANSLATE (B2, “en”, “es”)

You can use this formula to translate meta descriptions, titles, and keywords to ensure consistency across languages and regions.

11. SPARKLINE

Data is easily understandable when visualised. That’s exactly what SPARKLINE does. 

The function creates data visualisations within specific cells within Google Sheets for SEO to help you gain actionable insights in a glimpse.

Syntax: =SPARKLINE(A2:A30, {"charttype","line"}) 

Our favourite way to leverage this SEO formula is to visualise the monthly search volume trends for keywords using the expression: =SPARKLINE(G2:R2,{"charttype", "column";"color","brown"})

The monthly volume data are present from column G to R. Additional details customize the chart.

You can also combine it with VLOOKUP for SEO to compare ranking trends across multiple URLs or keywords. The formula may appear as: =SPARKLINE(VLOOKUP(A2, KeywordData!A:R, 7, FALSE), {"charttype", "column"; "color", "brown"})

The VLOOKUP searches for the keyword in the A2 cell within the dataset from the A to R column to return the search volume trend.

Additional Tips to Leverage Google Sheets to Its Fullest

1. Leverage Add-ons

These advanced tools help you do more on Google Sheets without typing out long and complex expressions. We’ve consolidated five powerful add-ons you can start using now:

1. Keywords in Sheets

Keywords in Sheets allow you to conduct keyword research, analyse competitors, and access search volume data without leaving Google Sheets. This expedites SEO data analytics and reduces the administrative burden on content marketing teams.

2. NoDataNoBusiness

NoDataNoBusiness provides ImportFromWeb, a Google Sheets add-on to retrieve information from websites directly into the spreadsheet. Just use the =IMPORTFROMWEB() function to pull data from over a million websites, including JavaScript-rendered content.

It also offers proxy rotation and data cleansing capabilities. You can streamline competitor analysis, keyword insights extraction, and market trends research, enhancing the capabilities of your SEO spreadsheets.

3. Apipheny

Apipheny is a no-code Google Sheets API connector that helps you to import and export data between spreadsheets and various sources such as marketing platforms, SEO tools, and other services.

You can send multiple kinds of request methods, including GET, POST, PUT, PATCH, and DELETE. This unlocks versatile data interactions. 

Additional features of this Google Sheets SEO add-on are pagination handling, custom OAuth 2.0 integration, and =APIPHENY() custom function for advanced data management.

4. API Connector

The API Connector, as the name suggests, facilitates data exchange between Google Sheets and other sources such as marketing solutions and analytics software. Its capabilities include automatic data refreshes, customised API requests, and pagination for large datasets.

One advantage of leveraging this option over Apipheny is that the API Connector offers a library of ready-to-use APIs, speeding up its adoption. This allows even non-technical users to make the most of Google Sheets formulas for SEO.

5. Authoritas SEO

The Authoritas SEO Google Sheets add-on brings three core features from the Authoritas SERPs API that enables you to:

• Perform in-depth keyword research
• Analyse SERPs
• Track keyword rankings directly from the spreadsheet

The solution makes the SEO formulas for Google Sheets more impactful by simplifying the process of gathering and analysing keyword data.

2. App Scripts Assistants

Google Apps Script (GAS) is a cloud-based scripting language that allows you to create custom automation and functions that extend your SEO spreadsheet’s functionality.

However, writing and editing scripts can be challenging, especially for SEO experts with limited technical experience or during tight deadlines.

App Scripts assistants aid in this process by helping you write code instantly.

1. Google Apps Script Copilot

Google Apps Script Copilot is a Chrome extension that writes code through AI integration. The tool offers real-time chat functionality within the GAS IDE, making the scripting process collaborative and efficient.

You can simply share your requirements in natural language and receive Google Sheets app script to elevate your SEO spreadsheets.

2. GAS Code Generators

There are several other AI assistants that can write working app scripts to help you automate tasks or run custom SEO formulas. Here are some great options for you to choose from:

• FormulaBot: Best for automating tasks in Google Sheets, such as data manipulation and custom function creation.
• YesChat: Performs well on administrative action items like responding to emails and managing events straight from SEO spreadsheets.
• Ajelix: Offload tasks like competitor data analysis and content marketing report generation through AI-assisted scripting.

3. Automation Capabilities

There are various other ways you can extend the capabilities of your SEO spreadsheets by mechanising ‘boring’ tasks through automation that doesn’t require coding. There are two quick ways you can do so now:

1. Sheet Automation

Sheet Automation is a Google Sheets add-on that can take over tasks like form response handling, office work automation, content monitoring, and bulk data processing. Since this doesn’t require scripting, you can get these benefits without much effort.

2. Connect with ChatGPT

ChatGPT can help you do more than simple data operations in your SEO spreadsheets. 

You can, for instance, generate content, review existing work, and extract specific information from rich metadata.

The broad steps to integrate ChatGPT with Google Sheets include:

• Obtain an API key: Generate a unique OpenAPI to authenticate your SEO requests from Google Sheets.
• Access Google Apps Script: Navigate to Extensions>App Script in a spreadsheet to open the editor.
• Write a custom script: Based on your needs, input code that sends prompts to ChatGPT and returns responses to Google Sheets.
• Implement custom functions: These formulas will execute the script to interact with ChatGPT through the unique API.
• Test and deploy: Run the script through the custom function on dummy data to ensure it gives you useful output.

Final Thoughts

Google Sheets formulas for SEO enable you to automate administrative tasks, analyse data, generate reports, and make bulk edits. Some of them are quite simple to understand and apply, while others may require a bit of trial and error.

On top of these SEO formulas, you can leverage Google Sheets add-ons for SEO, app scripts, and custom integrations with external tools like ChatGPT.

These extensions enhance the capabilities of your SEO spreadsheets, boosting productivity.

Want to level up your SEO efforts?

Contact us today to book a free consultation.