29 Google Sheets Formula for SEO

As an SEO specialist, you know how important it is to perform keyword research and analyse website performance metrics. Fortunately, Google Sheets has a few formulas that can help you process data faster. 

In this blog post, I will list out some of the useful Google Sheets Formulas that make SEO analysis easier.

LEN

=LEN(string)

The LEN formula allows you to easily determine the character count of a line or string. This formula can be particularly useful for advising copywriters on writing metadata that falls within a “safe” character limit. By using the LEN formula, you can help ensure that the metadata doesn’t get cut off due to excessive length.

For example, I usually use it with conditional formatting – so, when the copywriter is editing the copy, the cell will be highlighted in red as a warning if their edit exceeds the suggested length.

IF

=IF(logical_expression, value_if_true, value_if_false)

The IF function allows you to test a condition and return one value if the condition is true, and another value if the condition is false.

For example, using IF can help  identify which meta description is too long and then I can filter them by easily with Yes or No, and start optimising them.

IFS

=IFS(condition1, value1, [condition2, value2, …])

Similar to the IF function, the IFS function is used to check multiple conditions and return a value based on the first condition that is true. It is a more concise and efficient way to perform nested IF statements.

For example, I have a list of URLs that I want to optimise and I decide to prioritise pages with Low CTR (<1%) or is currently ranking on page 2, then I can use IFS to help me identify those pages.

IFERROR

=IFERROR(value, [value_if_error])

The IFERROR function in Google Sheets is used to handle errors that might occur in a formula. It allows you to specify a value or action to be taken if the formula returns an error.

Here are some errors you might encounter when using Google Sheets:

• #DIV/0! error: When you attempt to divide a number by zero or a blank cell.
• #VALUE! error: When a function or formula is unable to interpret one or more of the values in the cell range it is applied to. 
• #REF! error: When a formula contains a reference to a cell that no longer exists or has been moved.
• #NAME? error: When a formula contains a misspelt function name or a function that does not exist.
• #NUM! error: When a formula contains an invalid numeric value or an argument that is not valid for the function. 
• #N/A error: This error occurs when a formula or function is unable to find the value it is looking for.

Let’s take this example, I am trying to extract the meta description using the IMPORTXML formula. If the page doesn’t have a meta description, it will show an #N/A error.

To resolve that, I can use IFERROR to emphasise what value should be shown when meta title description is missing. You can also just use “” to return an empty cell when an error occurs.

SPARKLINE

=SPARKLINE(data, [options])

The SPARKLINE function is a function that can be used to create small, simple, and clear visual representations of data trends within a cell. You can also customise the sparkline’s colour, style, and size to match the theme of your spreadsheet. 

After exporting data from KWFinder, I am able to see the historical search volume of the keyword by month. Sparkline can quickly visualise the pattern and help me identify the trend and plan the content accordingly.

Take the keyword  “christmas gift” as an example, with a trend and heatmap in your keyword research document, you can quickly notice that it is a seasonal keyword and it reaches its peak in December (not surprising, but you see my point).

CONCATENATE

=CONCATENATE(string1, [string2, …])

The CONCATENATE function combines multiple pieces of text into one larger piece of text. It can be used for different SEO scenarios. 

There are several use cases in SEO:

Use Case 1: Keyword Variation

You can use CONCATENATE to create a list of keyword variation. For example, I can quickly compile a list of different keywords that expats might use when they are moving to another country.

Add “ “ between or after the keyword if you need a space. In the above example, I have both spaces added –  “ in “.

Use Case 2: Page Meta Title

This comes in handy when creating meta titles for your website since they typically contain both page title, separator and site title. Combining CONCATENATE and conditional formatting with LEN function can quickly help you understand if your meta title exceeds the suggested limit.

Another use case is when you have a bulk list of articles with similar meta title or description, you can also use CONCATENATE to create meta tags swiftly.

JOIN

=JOIN(delimiter, value_or_array1, [value_or_array2, …])

The JOIN function is used to combine the contents of two or more cells or ranges of cells into a single cell, just like CONCATENATE. Unlike CONCATENATE, the JOIN function allows you to specify a delimiter, such as a comma or space, to separate the contents of the cells being joined.

FLATTEN

=FLATTEN(range1, [range2, …])

The FLATTEN function allows you to flatten arrays into a single column or row, so you can easily copy and paste the content.

Take the keyword variation of the CONCATENATE as an example – using the FLATTEN function can help us select the keywords across multiple columns and put them into a list. Then you can easily copy and paste your list of keywords to your keyword research tool for analysis. 

For one of the clients, I created a spreadsheet to keep track of all the different localised webpages and also help translators to find pages easily. Using the FLATTEN function can help me easily get the list of URLs for other analysis purposes.

SPLIT

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

SPLIT is the opposite of CONCATENATE which allows you to split text into multiple cells based on a delimiter. The delimiter can be a comma, space, semicolon, or any other character or string that separates the text you want to split. 

COUNTIF 

=COUNTIF(range, criterion)

The COUNTIF function allows you to count the number of cells within a range that meet a specified criteria.

This formula can be very helpful when tracking how often certain terms or pages appear on your website. Simply select the range of data and replace the criterion with the value you would like to check.

For example, I can use ScreamingFrog to export a list of Outlinks and check how many times have a certain page be linked from other pages on the website:

Of course if you have a huge website, then probably this may not be the best method to audit your internal linking. But if you are just starting out a small website, this might be handy.

SORT

=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, …])

The SORT formula allows you to sort data in a range in either ascending or descending order. Although you can easily sort your data by clicking Data and then Sort range, the SORT formula could still be useful if you don’t want to mess up the original data. 

You can simply enter the range of the data you need to sort, and decide how to sort. In the third parameter, choose “TRUE” if you want the value to be sorted in an ascending order or “FALSE” if in descending order. You can also add multiple columns in your sorting parameter if you need.

UNIQUE 

=UNIQUE(range, by_column, exactly_once)

The UNIQUE function allows you to extract unique values from a range of cells. It can be useful for many purposes, including data analysis, removing duplicates, and simplifying large data sets. 

For example, if you have compiled a list of keywords from different competitors and want to find the unique keywords, you can use the UNIQUE function: 

This will extract all the unique keywords from column A and display them in the cell where you typed the function.

Some other use cases would be cleaning up unique domains for link-building, scraping raw data for programmatic SEO etc.

SUBSTITUTE 

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

The SUBSTITUTE function is used to replace a specific text within a cell or a range of cells with a different text. It is particularly useful when you want to replace a specific word, character, or string of characters within a large set of data.

For example, I have a list of keyword variations about renting apartments in London. Now I want to expand to cover renting in Manchester, I can use SUBSTITUTE to replace London with Manchester to get me a new list of keywords variation.

SEARCH

=SEARCH(search_for, text_to_search, [starting_at])

The SEARCH function is used to find the position of a specific text string within another text string. The function returns the starting position of the text string, or the #VALUE! error if the text string is not found. It is basically the same as FIND function, except it is not case-sensitive – which gives you more flexibility.

The SEARCH formula is most useful when you are combining it with IF function to group specific data. However, as SEARCH will return #VALUE! Error, you should also use the IFERROR function, so it will return “No” when the term you are searching does not exist in the cell. 

For example, if I want to know if my list of keyword contains a particular phrase, I can use =IFERROR(IF(SEARCH(“[phrase]”,A2),”Yes”,””),”No”) to quickly identify them:

There are some limitations to SEARCH, such as it is not case-sensitive and it can only handle simple text. For more complex search, perhaps use REGEXMATCH instead (which will be covered in a later section)

ISNUMBER FIND

=ISNUMBER(SEARCH(“text”, reference))

You can use the ISNUMBER function in combination with the FIND function to check if a specific substring or text is found within another cell or text string. Combined with conditional formatting, you can use this formula to highlight the entire row if a cell contains specific text.

The FIND part looks for the text within the content of the cell. If the text is found, it returns the starting position within the text, and if not found, it returns an error.

The ISNUMBER() function then checks whether the result of the FIND function is a number. If the FIND function found the text, it will return a number (the starting position), and ISNUMBER will return TRUE.

SUMIF

=SUMIF(range, criterion, [sum_range])

The SUMIF is a function in Google Sheets that adds up the values in a range of cells that meet certain criteria specified by the user. 

Use Case 1: Calculate total search volume of keyword topic

For example, you can use it to calculate the total search volume of a specific topic after you have categorised them.

To calculate the total search volume of a specific topic, you can also use the Pivot Table.

Use Case 2: Calculate total impressions or clicks of URL groups 

You can also use SUMIF to calculate the total impressions or clicks of a specific URL group. In this example, I put “*” in front and after the text “blog” to check any cells that contain the word “blog” as the cell contain a URL address that is considered as the same string of text

ISBETWEEN

=ISBETWEEN(value_to_compare, lower_value, upper_value, lower_value_is_inclusive, upper_value_is_inclusive)

The ISBETWEEN allows you to check whether a value falls between the other two values. You can use it to check different metrics, such as if your page is on page one  (position between 1-10).

VLOOKUP

=VLOOKUP(search_key, range, index, [is_sorted])

VLOOKUP allows you to search for a specific value in the first column of a table or range of cells, and return a corresponding value in the same row from a specified column.

Instead of manually searching through a table to find the data you need, you can quickly and easily retrieve specific data from a large table or range of cells in Google Sheets. 

For example, I have a master document that keeps track of my content inventory – such as title, word count, and other relevant information. I would like to understand how different attributes impact the performance. Even though the exported Google Search Console data is not ordered the same way as my content inventory, I can still use VLOOKUP to easily connect my GSC data with my existing content.

INDEX MATCH

=INDEX(reference, [row], [column]),MATCH(search_key, range, [search_type])

The INDEX MATCH function works by using the MATCH function to find the row number of the search key in the table, and then using the INDEX function to retrieve the corresponding value from the table.

INDEX MATCH is an alternative to VLOOKUP in Google Sheets, and is a powerful way to look up and retrieve data from a large table or range of cells. The main advantage of using INDEX MATCH over VLOOKUP is that it can handle more complex lookup scenarios, such as cases where the search key is not in the leftmost column of the table.

FILTER

=FILTER(range, condition1, [condition2, …])

The FILTER function in Google Sheets allows you to extract a subset of data from a larger range of data based on specified criteria. It is a powerful tool for data analysis and reporting, as it allows you to easily extract and display the data you need without having to manually sort or filter a large dataset.

In this example, I use the FILTER function to find out keywords that have more than more than 5000 search volume.

You can also use FILTER to compare your competitors’ ranking keyword and filter out if you have existing content that are ranking for the same query.

IMPORTRANGE

=IMPORTRANGE(spreadsheet_url, [sheet_name!]range_string)

The IMPORTRANGE function allows you to import data from one spreadsheet to another. The function enables you to connect two different spreadsheets and access data from one spreadsheet into another one.

The IMPORTRANGE function is useful when you need to consolidate data from multiple spreadsheets into a single one, or when you want to analyse data that is stored in a different spreadsheet.

For example, if you need to share some data from a spreadsheet to a client, you can use IMPORTRANGE to only import useful data from the document.

Don’t forget to grant access to the other spreadsheet before you can import data using the IMPORTRANGE function.

GOOGLETRANSLATE

=GOOGLETRANSLATE(text, [source_language, target_language])

The GOOGLETRANSLATE function allows you to translate text from one language to another within a spreadsheet. 

If you are handling a multilingual website and need to work on keyword research in other languages, you can use this to help you understand the keywords. I usually use this to only translate my competitors’ list of keywords that are not in English or translate the list of keywords that I already compiled and approved to English for the client to check. 

I won’t recommend using it to translate your English keyword into other languages for keyword research though. You should aim at having a native speaker check the keywords that are processed by GOOGLETRANSLATE.

IMPORTXML

=IMPORTXML(url, xpath_query)

The IMPORTXML  function that allows users to import data from an XML or HTML file on the web into a Google Sheet. The function works by retrieving and parsing data from the specified URL, based on the XPath query provided by the user.

XPath is a language used to select elements from an XML document, which is what web pages are based on. You can use the Chrome browser’s “Inspect Element” feature to identify the XPath expression for the information you want to extract.

Based on the information you want to extract from the web page, you would need a different XPath query:

Use Case 1: Extract page title

=IMPORTXML(URL,”//title”)

Use Case 2: Extract meta description

=IMPORTXML(URL,”//meta[@name=’description’]/@content”)

Use Case 3: Extract URLs in sitemap

=IMPORTXML(A2,”//*[local-name() =’url’]/*[local-name() =’loc’]”)

Some other useful IMPORTXML that might be useful

Use case	Syntax
Extract H1	=IMPORTXML(URL,”//h1″)
Extract canonical tag	=IMPORTXML(URL,”//link[@rel=’canonical’]/@href”)
Extract HREFLANG	=IMPORTXML(URL,”//link[@rel=’alternate’]/@hreflang”)
Extract internal links	=IMPORTXML(URL,”//a[not(contains(@href, ‘domain.com’))]/@href”)
Extract external links	=IMPORTXML(URL,”//a[contains(@href, ‘domain.com’)]/@href”)

TRANSPOSE

=TRANSPOSE(array_or_range)

You can use the “Transpose” function to switch the rows and columns of a range of cells. This can be useful if you want to display data in a different orientation or if you need to perform calculations on rows that are currently arranged as columns.

For example, when I’m using IMPORTXML to extract the hreflang, it will list out the hreflang into the same column – using TRANSPOSE can let me get the value in the same row without overwriting the next row of data.

REGEXEXTRACT

=REGEXEXTRACT(text, regular_expression)

The REGEXEXTRACT function allows you to extract a specific substring from a text string using a regular expression pattern.

Use Case: Extract URL slug

=REGEXEXTRACT(URL, “//[^/]+/([^/]+)”)

> This only works for the last part of the URL, if you need to extract any subfolder, you would need to combine other functions to achieve that.

REGEXREPLACE

REGEXREPLACE(text, regular_expression, replacement)

REGEXREPLACE is a function in Google Sheets that is used to replace text in a string based on a regular expression pattern – you might consider it as a more powerful version of the SUBSTITUTE function, which can only replace simple text. 

Use Case 1: Extract Root Domain

=REGEXREPLACE(A2,”http\:\/\/|https\:\/\/|www\.|\/.*|\?.*|\#.*”,””)

You can use REGEXREPLACE to extract the root domain. However, if your website also has other subdomains, such as blog.example.com, you have to specify that in your function – simply add [subdomain]\.  with | (OR operator).

Use Case 2: Change domain or subfolder name

It is also useful when you need to migrate to a new domain or you’re restructuring your website that would result in a change of subfolder name – you can easily get the new list of URLs to work on redirection.

REGEXMATCH

=REGEXMATCH(text, regular_expression)

The REGEXMATCH function is used to check whether a string of text matches a specified regular expression pattern. It returns TRUE if the text matches the pattern, and FALSE if it does not.

REGEXMATCH can be very useful for keyword research or SERP analysis when combined with the IF function. 

For example, I can quickly check if the keywords is a question keyword:

If you’re interested in question keywords, read my other blog on how to use Google Search Console to find question keywords.

ARRAYFORMULA

=ARRAYFORMULA(array_formula)

ARRAYFORMULA is a function in Google Sheets that allows you to apply a formula to an entire range of cells. Instead of entering the formula in each individual cell, you can use the ARRAYFORMULA function to automatically apply the formula to an entire range of cells at once. 

In this way, you don’t have to enter the formula repeatedly or drag down endlessly if you have hundreds rows of data.

DETECTLANGUAGE

=DETECTLANGUAGE(text) 

The DETECTLANGUAGE function identifies the language of a piece of text. If you are working on a multilingual website, this function helps you categorise the keyword or anchor text into the correct languages.

Conclusion

Google Sheets offers some powerful formulas for analysing SEO performance. Whether it’s keyword analysis or optimising on-page elements, there are plenty of ways to make use of these formulas for better understanding your website’s data. With practice, you’ll be able to quickly apply these formulas and get more out of your SEO efforts than ever before!

FAQs about Google Sheets Formulas