BLOG
Learn google sheet formula for seo
Click to hear an audio recording of this post

Whether you’re an in-house SEO, freelance SEO, or SEO agency, you know you have an intimate relationship with Google sheets. 

You need Google sheets for analysing and manipulating datasets for every important SEO task. 

Still, many SEOs don’t make the best use of Google sheets since they don’t know how to play with spreadsheet formulas. So they end up wasting a lot of time on performing manual tasks that can be easily automated with formulas. 

Are you also one of them? 

If yes, then read on. This guide is going to change how you’ll be using Google sheets for your SEO tasks in future. 

Here we go. 

Google Sheet Basic Formulas for SEO

Let’s start with some of the most basic Google sheet formulas.

1. LEN

You can use the LEN formula to count the number of characters in a cell. 

Formula: =LEN(insertcell)

SEO use cases:

  • Writing or sorting meta descriptions and title tags
  • Identifying excessively long URLs
  • Evaluating PPC and ad copies, etc. 

For example, we’re analysing the title tags and meta descriptions of our web pages in a Google sheet. And we want to ensure that they’re aligned with the recommended character length — below 60 characters for title tags and below 160 characters for meta descriptions. 

Now, manually checking the character length of these two on-page SEO elements for hundreds of pages can take up a lot of time. 

Instead, we can use the LEN formula as shown below. 

Google-sheet-formulas-seo-len

As you can see, Column E contains the page titles. Next, we entered the formula =len(E2) in cell F2 which is next to E2. So it’ll calculate the length of the text in E2. And then, we just dragged down from F2 to F6. 

That’s it. Now, we have the length for all the page titles. 

You can follow the same process for the meta descriptions in the Column G. Thus, you can get the length of these on-page SEO elements in seconds. 

2. IF 

The IF formula helps you check whether a condition is met or not and returns a value based on that result. 

Formula: =IF(condition, value_if_true, value_if_false)

SEO use cases:

  • Determining whether keywords are worth targeting based on their search volumes
  • Checking if the URLs match
  • Labelling the titles and meta descriptions’ character length as Yes/No or Good/Bad, etc.  

So let’s continue the above example of meta descriptions. 

Now that we have the character length of the meta descriptions i.e. Column H, let’s check whether they meet the recommended character length — less than 160 characters. 

For this, we need to enter the formula =if(H2<160, “Yes”, “No”) in the next cell. For our case, in column I2

Google-sheet-formulas-seo-if

So, if the value in the cells of the H Column is less than 160, it’ll label the corresponding cells in Column I as “Yes”. And if this condition is not met, it’ll label the corresponding cells as “No”.

Thus, we can quickly identify the meta descriptions with “No” labels, make a separate sheet for them and start optimising them one by one. 

3. CONCATENATE 

The CONCATENATE formula can help you merge data from multiple cells. 

Formula: =CONCATENATE(range)

SEO use cases:

  • Can create a complete URL if the domain name and path are in separate columns
  • For the email outreach list, if the first and last names are in separate columns, you can merge them with this formula
  • Doing keyword research in bulk

Let’s say, we want to create the final URLs for the new blog posts to be published on our site. We have the domain names, categories, and slugs in different columns in a sheet. But we want complete URLs. 

Let’s see how to do it with the CONCATENATE formula. 

Google-sheet-formulas-concatenate

As you can see, the domain name, category, and slug are in different columns. Now, we want to combine all three ColumnsA, B, C — to create final URLs. This can especially be helpful while planning new content for your website blog by giving an overview of the existing content.

In this case, we have used the CONCAT formula twice as shown in the above image. We entered the =CONCAT(CONCAT(A2,B2),C2) formula in cell D2. 

Let’s break it up for better understanding:

CONCAT(A2,B2) would merge the values of cells A2 and B2 i.e. Website and Category columns.

And =CONCAT(CONCAT(A2,B2),C2) would merge the combined data of A2 and B2 with C2 (Slug column) to generate a final URL. 

Then we dragged down from cell D2 to D6. And we have the list of complete URLs. 

4. SPLIT

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

Formula: =SPLIT(text, delimiter) 

SEO use cases: 

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

Now, let’s say we have a list of complete URLs. But we want to split and save them into different columns — Protocol, Domain, Path. 

So we entered =SPLIT(A2, “/”) formula in cell B2 (the cell next to A2). 

Google-sheet-formulas-SEO-split

Since you’ve used forward-slash (/) as a delimiter, it would split the text after every forward-slash and save them in different columns as shown in the image. 

5. UPPER LOWER PROPER 

The UPPER LOWER PROPER functions help you convert the selected text or data sets into uppercase, lowercase, and title case (Capitalising the first letter of each word). 

Formula: 

=UPPER(text)

=LOWER(text)

=PROPER(text)

SEO use cases: 

  • To maintain consistent formatting in title tags
  • Capitalising acronyms 
  • Avoiding duplication with consistent data sets

For instance, here’s a page title in lowercase faceted navigation for seo: what, why, and how”. Now if we apply the PROPER function it’ll convert to the title case like this:

Faceted Navigation For Seo: What, Why, And How

Let’s see it in action for multiple titles. 

Google-sheets-formulas-seo-upper-lower-proper

Thus, if you have a huge list of title tags with inconsistent formatting these formulas can convert them into your preferred format at scale. 

Thus, your SEO reports would look neat, well-formatted, and easy-to-interpret. At the same time, it’ll reflect your clarity as well as expertise in SEO reporting and analysis.  

6. COUNTIF 

The COUNTIF formula helps you count cells with specific properties quickly. 

Formula: =COUNTIF(range, criteria)

SEO use cases: 

  • Filter keywords on the basis of their search volume or ranking difficulty
  • Counting URLs according to their categories
  • Counting duplicate keywords or URLs, etc. 

For example, we’re conducting keyword research to identify new content ideas. We have listed some keywords in Google sheet and we want to count the number of keywords with a search volume greater than 100. 

So we entered the formula =countif(B2:B22, “>100”).

Google-sheet-formulas-seo-countif

The result shows that there are 9 keywords that match this condition. 

Now, it’s easy to count it manually for such a small list. But if your list is in hundreds and thousands, this formula can be a blessing. 

7. UNIQUE 

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

Formula: =UNIQUE(range)

SEO use cases: 

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

Most of the time when you export keywords or URLs from SEO tools, you may get many repetitive terms. 

Here’s an example:

Google-sheet-formulas-seo-unique

As you can see, after the 22nd row, the data is repeating. 

So we used the UNIQUE formula to eliminate the duplicates. This can save you a lot of time when you’re dealing with large volumes of data. 

If you’ve selected multiple columns — for instance, A to E — then all the values in the row should be unique for the formula to identify the dataset as unique. 

It means that if the value is different in only one cell and the rest of the cells in that row are repeated, still the entire row will be considered unique. 

8. SUBSTITUTE 

You can replace any character or word with another word by using the SUBSTITUTE formula. 

Formula: SUBSTITUTE(cell, search_for, replace_with)

SEO use cases: 

  • Editing title tags or meta descriptions at scale
  • Updating the URLs or their subfolders quickly 
  • Changing the URLs during the migration process, etc.

Being an SEO agency, we also keep fine-tuning our SEO strategy periodically. Let’s assume, we’re reviewing our title tags and decided to replace “Supple” with “Supple Digital” in each of our title tags.  

And here’s how we changed the titles in our sheet by applying the SUBSTITUTE formula like this:

=SUBSTITUTE(A2,”Supple”,”Supple Digital”) 

Google-sheet-formulas-seo-substitute

As you can see, all the titles are re-written with “Supple Digital” at the end of the title. 

Needless to say, if you’ve been creating content for years, you could end up with hundreds or thousands of web pages. And when you want to implement such site-wide changes, this formula can be a great help. 

9. SEARCH 

The SEARCH function helps you search strings for specific values and sort the database accordingly.  

Formula: =SEARCH(search_for, text_to_search)

SEO use cases: 

  • Sorting keywords 
  • Grouping URLs, etc.

For instance, we have a list of URLs and we want to mark “Blog” next to the ones that belong to the blog category. Therefore, we used the SEARCH formula in conjunction with the IF formula like this: 

=IF(SEARCH(“/blog/”,A2),”YES”,””)

Google-sheet-formulas-seo-search

Thus, it searched for the word “Blog” in the URLs and wherever the condition matched, it marked the corresponding cell as “YES” — cells D5 to D8, in this example. 

Since we didn’t add a value to be returned when this condition is false, the remaining cells show an error (#Value!). We can avoid this error by using an IFERROR formula which means that if the condition doesn’t match, the corresponding cell will be marked as “No”. 

We’ve covered the IFERROR formula for SEO later in the post. 

10. SUMIF

The SUMIF function lets you add up the numeric values from different cells if they match the condition in a specific range or criterion.

Formula: SUMIF(range, criterion, [sum_range])

SEO use cases: 

  • Adding up the website traffic data based on specific criteria
  • Data sorting and manipulation
  • Adding up the search volume of matching keywords, etc. 

For example, we have a list of parent keywords and keywords along with their difficulty score, search volume, and CPC. 

Now we want the estimated total search volume for keywords that belong to a specific parent keyword. 

We did this in two steps. 

Step 1. We applied the UNIQUE formula — =unique(A2:A29) — in cell H8 to identify the unique parent keywords from Column A and make a separate list in Column H.  

Google-sheet-formulas-seo-unique.png

As we drag down, we have a list of unique parent keywords in Column H

Step 2. Then we inserted the SUMIF formula — =sumif(A2:A29,H8,D2:D29) — in cell I8 to add up the search volume of keywords that belong to the parent keyword in cell H8. 

Google-sheet-formulas-seo-sumif

And once we dragged down from cell I8 to I21, we got the estimated total volume for each of the keywords that belong to parent keywords in Column H

This can help us identify the Parent keyword that is more popular and we can thus keep that keyword as the focus keyword of the content/page we are planning to optimise for SEO.

Google Sheet Advanced Formulas for SEO

Now, it’s time for some advanced stuff. Let’s take a look. 

11. ARRAYFORMULA 

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

Well, the drag down function also does the same, you might say! But manually dragging the function across the datasets may work for small data. If it’s a large dataset, it’s not very convenient. 

Besides, whenever you change the formula, you’d need to apply them manually again by dragging. ARRAYFORMULA solves this challenge. 

Formula: ARRAYFORMULA(array_formula)

SEO use cases: 

  • Applying formulas across the rows or columns in Google sheets. So technically you can use this formula with any other formula for a myriad of use cases. 

Let’s say, we’re conducting an SEO audit for our site. And we want to sort the URLs according to the type or category of the content assets to manage our website taxonomy better. 

Hence, we entered =Arrayformula(IF(SEARCH(“/guides/”,A2:A10),”YES”,””)) in Column B to search if the URL contains the word “guides” and if it matches the condition then label the corresponding cell as “YES”.

Google-sheet-formulas-seo-array1

As a result, the first three cells in Column B are marked “YES” which means they’re guides. But as we can see, the rest of the cells that don’t match the condition are marked as “#VALUE!” — or error. 

To avoid this error in Column C, we added the IFERROR formula to the ARRAYFORMULA. 

Here, we entered =IFError(arrayformula(IF(SEARCH(“/tools/”,A2:A10),”YES”,””)),”No”) in Column C. It means marking the cells as “No” if they don’t match the condition instead of showing an error. 

Google-sheet-formulas-seo-array2

Thus, it labelled the cells as “YES” if the URL includes the word “tools” and “No” if it doesn’t. 

12. VLOOKUP 

VLOOKUP is arguably the most popular formula amongst SEOs. It helps you pull the data from one spreadsheet to another instead of copying and pasting them manually. 

Moreover, it also automatically updates the other sheet whenever the shared data of the original sheet is edited. 

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

SEO use cases: 

  • Pulling or sharing keyword data between different sheets
  • Combining different datasets to make a master sheet
  • Combining data from multiple sheets for SEO/Content/Backlink audit, etc. 

For instance, we’re researching and mapping the keywords for the middle and bottom-funnel content. In one sheet, we have all the keyword data except for the Keyword Difficulty (KD) score which is in a separate sheet named “Difficulty”. 

Google-sheet-formulas-seo-vlookup1

So we pulled the KD score from the above sheet by inserting this formula in cell G2:

 =VLOOKUP(B2,Difficulty!B:C,2,false)

Google-sheet-formulas-seo-vlookup

And then dragged down from cell G2 to G29 to get the KD score for the rest of the keywords. If it’s a large database you can also use ARRAYFORMULA along with VLOOKUP so that all the corresponding cells are automatically populated with the required data. 

13. INDEX MATCH

Combining the INDEX and MATCH formulas is a smarter way to perform a two-way lookup in Google Sheets. It covers up for the below limitations of VLOOKUP:

  • VLOOKUP cannot return a result or value that falls to the left of the lookup value in the source array. 
  • The lookup values in the source data must be in ascending order when you accept approximate matches while entering the last argument in the formula. 

Formula:

INDEX(reference, [row], [column])

MATCH(search_key, range, [search_type])

SEO use cases: 

  • Collating keyword data from different sheets or sources
  • Combining data from the same or different sheets for backlink audits 
  • Searching for specific values like volume, traffic or difficulty score in keyword dataset, etc. 

Again for this example, we have keyword data in one sheet (let’s call it Sheet 1) and KD scores in a separate sheet (Sheet 2). 

Here’s Sheet 1:

Google-sheet-formulas-seo-indexmatch1

And here’s Sheet 2:

Google-sheet-formulas-seo-indexmatch2

Now observe the order of keywords in both sheets. It’s not in the same order. And we want to pull the KD score from Sheet 2 into Sheet 1. 

In such cases, VLOOKUP would only fetch the correct values if the order of keywords were the same — as it was in the VLOOKUP formula example.

But here, if we use the VLOOKUP formula it’ll return errors for most of the cells since we have changed the order of keywords. 

Instead, we used the INDEX MATCH function —

 =INDEX(Difficulty!$A$1:$C$30,Match(B2,Difficulty!$B$1:$B$30,0),3) like this:

Google-sheet-formulas-seo-indexmatch3

Thus, we have the KD score values pulled into Sheet 1 and placed in the KD Column according to their matching keywords. 

You can also go through this video for a detailed explanation of the INDEX MATCH formula.

14. REGEXEXTRACT 

As an SEO, you would be using Regex in Google Search Console and Google Analytics. You can also use the Regular Expressions (Regex) in Google sheet to analyse and manipulate the data in your SEO reports. 

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

Formula: REGEXEXTRACT(text, regular_expression)

SEO use cases: 

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

Let’s say we’re conducting a backlink audit for our website and we have a long list of URLs. And now we want to extract the domain names from the URLs to keep a separate database of all the referring domains. 

We did this by applying the below formula in cell B2: 

=REGEXEXTRACT(A2,”^(?:https?:\/\/)?(?:[^@\n][email protected])?(?:www\.)?([^:\/\n]+)”) 

Google-sheet-formulas-seo-regexextract

If you want to use this formula for the same use case, you can copy and paste the above formula. But if you want to use it for a different purpose, you’d need to learn a bit more about Regex. 

15. REGEXREPLACE 

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

Formula: REGEXREPLACE(text, regular_expression, replacement)

SEO use cases: 

  • Updating metadata like title tags or meta descriptions
  • Updating URLs for site migration purposes e.g. switching to a different CMS or platform, etc. 

For now, let’s use the same example that we used in the SUBSTITUTE formula. 

To highlight our brand positioning as Digital and SEO services, we want that all our title tags read “Supple Digital” at the end. 

So we entered the formula =REGEXREPLACE(A2,”Supple”,”Supple Digital”). Here, we’ve used “Supple” as the regular expression and “Supple Digital” as the replacement text.  

Google-sheet-formulas-seo-regexreplace

And Boom! We’re ready with a list of new title tags. 

Although we’ve demonstrated REGEXREPLACE with a simple example, remember it’s an advanced formula and can be used for other more complex use cases too. 

16. REGEXMATCH 

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

Formula: REGEXMATCH(text, regular_expression)

SEO use cases: 

  • Categorising the URLs according to content types
  • Categorising keywords according to funnel position and SERP features, etc. 

Say, we’re reviewing our content and SEO strategy to target a specific SERP feature — People Also Ask (PAA) box — with FAQs. We’ve extracted keywords and the corresponding SERP features they trigger.

Now, we want to highlight the keywords that have an opportunity to feature for the PAA box and mark them “Yes” if they match the condition and “No” if they don’t. 

Here, we’ve used REGEXMATCH along with the IF formula like this:

=IF(REGEXMATCH(C2,”People also ask”),”Yes”,”No”)  

Google-sheet-formulas-seo-regexmatch

Thus, the formula got all the corresponding cells marked as “Yes” and “No” based on the regex condition. Here, we’ve used “People also ask” as a regular expression. 

Similarly, if you’re well versed with regex applications, you can use this formula for advanced analysis and reporting purposes too. 

17. IMPORTRANGE

The IMPORTRANGE formula lets you import data from different files or workbooks into your master database. This helps you save time in two ways:

  1. You don’t have to manually copy and paste from various files.
  2. You don’t have to edit the imported data manually. When the data changes in the source file, it’s automatically updated in the master sheet. 

Formula: IMPORTRANGE(spreadsheet_url, range_string)

SEO use cases: 

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

For illustration purposes, let’s take the same example that we used for the VLOOKUP formula. The only difference is, for the VLOOKUP function, we pulled the Keyword Difficulty (KD) score from a different sheet within the same workbook. 

Now, we’ll import the data from a different workbook altogether. Here’s the screenshot of the separate file. You can copy the below sheet using this link.

Google-sheet-formulas-seo-importrange1

And then we applied the formula like this: 

=VLOOKUP(B2,importrange(“https://docs.google.com/spreadsheets/d/1W9AOt5EFLD7_oBZ8Xd2HhbQgr-pckanw4LguwIY8c3I/edit”, “Difficulty!$B$2:$C$54”),2, False)

As you can see, the formula contains the URL of the source spreadsheet and the range for the string to be imported.

Google-sheet-formulas-seo-importrange

Thus, all the cells of the KD score column are populated on dragging down from the first cell. Now, if the variables in the external file change, they would be updated in this sheet by default.  

18. IMPORTXML

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

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

Formula: IMPORTXML(url, xpath_query)

SEO use cases: 

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

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

So we just inserted the formula =IMPORTXML(A2,”//title”) where the data in A2 cell is the URL and “//title” is the XPath query. Now the Google Sheet algorithms will scrape the on-page data of the said URL and import the title tag into the corresponding cell. 

Google-sheet-formulas-seo-importxml

Here’s another example, where we imported all the external and internal links from one of our web pages by using the IMPORTXML formula. 

Google-sheet-formulas-seo-importxml1

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

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

Final Thoughts

There you have it. 

Basic and advanced Google sheet formulas for SEOs. 

Some of them are simple to understand and apply, while others may require a bit of learning and practising. But all of them can save you a ton of time when you’re dealing with large SEO datasets. 

So learn and practice your way around them to enhance your productivity. 

Also, you can make a copy of this Google sheet that we’ve used for this post. It’ll help you understand the execution of all the formulas we’ve discussed.

That said, boring Google sheets can be fun when you see these formulas working their magic on your data. 

At the same time, if you need any help with SEO strategy or analytics, feel free to contact us. We’ll be happy to help 🙂 

 

Enter Your Website & get an instant SEO Report for FREE