The Ultimate Guide to Google Sheets / Excel for SEOs and Digital Marketers

By David Westby

Working in SEO, PPC, and digital marketing more generally, requires being able to effectively manipulate datasets using programs such as Google Sheets or Excel.

Even though many key processes are now done using Big Query, Jupyter Notebook or other programs, Google Sheets will always be an essential tool.

This guide is focused on providing the ultimate Google Sheets guide for those working in SEO, and digital marketing more broadly, covering the basics, right through to more advanced techniques and formatting tips and tricks.

Note: this guide is updated periodically. 

Contents

• Google Sheets Shortcuts
• Basic Google Sheets Formulas
  1. LEN
  2. CONCATENATION
  3. IF
  4. SPLIT
  5. TEXT TO COLUMNS
  6. JOIN
  7. UPPER / LOWER / PROPER
  8. TRIM
  9. COUNTIF
  10. COUNTIFS
  11. SUMIF
  12. SUMIFS
  13. EXTRACTING A DOMAIN
  14. UNIQUE
  15. SUBSTITUTE
  16. SEARCH / ISNUMBER
  17. LEFT / RIGHT / MID
• Advanced Google Sheets Formulas
  18. VLOOKUP
  19. VLOOKUP with IFERROR
  20. INDEX / MATCH
  21. PIVOT TABLES
  22. ARRAYFORMULA
  23. REGEX EXTRACT
  24. REGEX REPLACE
  25. REGEX MATCH
• Google Sheets Power Tools
  26. QUERY FUNCTION
  27. IMPORT XML
  28. IMPORTRANGE
• Google Sheets Formatting
  29. CONDITIONAL FORMATTING

Google Sheets Shortcuts

Many of these shortcuts are used on the demonstration videos.

These will allow you to work quicker and more efficiently – especially when working on larger datasets.

• Copy:  CTRL + C (Command + C on mac)
• Paste:  CTRL + V (Command + V on mac)
• Cut: CTRL + X (Command + X on mac)
• Select All: CTRL + A (Command + A on mac)
• Find: CTRL + F (Command + F on mac)
• Find & Replace: CTRL + H (Command + Shift + H on mac)
• Select Entire Column: CTRL + Space (Control + Space on mac)
• Select Entire Row: Shift + Space (Shift + Space on mac)
• Copy Formula Down Column: Select the cell with the completed formula, hover over the small square at the lower right-hand corner of the cell and then click and drag down over the cells you want the formula to be applied to.

1. LEN 

LEN simply provides you with the length of a string in a cell.

Formula: LEN(cell)

This is particularly useful when you need to make sure that the amount of characters in a cell is a certain length such as producing title tags and meta descriptions.In this case, =LEN(A2) provides the length of the Title Tag in A2.

Note: This example also uses conditional formatting.

When to use this formula:

• Writing title tags and meta descriptions. 
• Writing PPC copy.
• Spotting lengthy URLs for tech audits, migrations or other URL work.

2. CONCATENATION

The concatenation formula is used for joining together strings or values.

Formula: =CONCATENATE ( string1, string 2, …) or =CONCATENATE (range)

See the example below of joining together the protocol with the domain.

Another common use of the concatenation formula within PPC, but also in SEO, is to speed up the process of producing a large amount of ad copy or meta descriptions.

Bonus tip: Another formula for concatenating text is using &;

=””&A2&””

For this example, I have written the keyword I am looking to target in the first cell, and on the second cell started writing a generic meta description with the keyword simply slotted in.

1. Select a cell you wish to have your results in and type “=”
2. Use quotation marks (“”) at the beginning and end of the formula.
3. Select the cell you wish to concatenate with the rest of the cell contents surrounded with “&[CELL]&” i.e. “&A2&”
4. Be sure to finish the formula with a quotation mark (“).

When to use this formula:

• Producing ad copy.
• Writing title tags and meta descriptions.
• Producing keyword research in bulk – especially if there is a list of “must include” terms.

3. IF

The IF formula is one of the logical functions that returns a certain value IF a specific condition is met, and another value IF it doesn’t match that condition.

Formula: =IF(logical_expression, value_if_true, value_if_false)

In this case, we are looking for the result “TRUE” if our title tags are over 60 characters.

When to use this formula:

• Checking the length of meta descriptions and title tags. 
• Checking whether URLs match before and after migration. 
• Sorting data with conditions i.e pages ranking in the top 5. 

4. SPLIT

The SPLIT formula is used to split text from one cell into separate fragments. Splitting cells can be done using a delimiter which could be some text, comma, space or any other symbol.

Formula: =SPLIT(text, delimiter)

In this example, we are using the SPLIT formula to break up a URL into different subfolders. 

Note: This is very similar to the Text to Columns function.

When to use this formula:

• Separating URLs into different subfolders.
• Separating comma-separated values (CSVs) into separate columns. 
• Separating mixed data into separate columns i.e. names.

5. TEXT TO COLUMNS

The text to columns feature serves the same purpose as the SPLIT formula. This allows you to separate text into separate columns using a “separator” which could be a comma, semicolon, period or space (or custom).

In this example, we are using this function to break up a URL into different subfolders. 

How to convert text to columns:

1. Select all cells in a column
2. Click Data > Split text to columns
3. Select separator or to change to custom (and type in the separator i.e. “/”). 
4. Press enter. 

When to use this formula:

• Separating URLs into different subfolders.
• Separating comma-separated values (CSVs) into separate columns. 
• Separating mixed data into separate columns i.e. names.

6. JOIN

The JOIN formula allows you to combine multiple columns of text together using a delimiter.

This formula can be seen as the opposite of the SPLIT formula – instead of separating one cell into different segments, the JOIN formula allows you to combine them together. 

Formula: JOIN(delimiter, value_or_array1, [value_or_array2, …])

In this example, we are using this function to build URLs. 

When to use this formula:

• Building URLs
• Piecing together keyword research. 

7. UPPER / LOWER / PROPER

The UPPER, LOWER and PROPER functions allow you to convert text into either uppercase, lowercase or capitalise the first letter of each word.

Formulas:

=LOWER (text) – makes all letters lowercase
=UPPER (text) – makes all letters uppercase
=PROPER (text) – makes the first letter of each word uppercase.

In this example, we are using the PROPER formula to ensure all our title tags are consistently formatted. 

When to use this formula:

• To ensure all title tags consistently formatted.
• Ensuring that data sets are consistent to avoid duplication. 
• Ensuring all acronyms are in capital letters.

8. TRIM

The TRIM function allows you to get rid of unwanted additional spaces characters.

Formula: =TRIM(text)

In this example, we are using TRIM to cut out unwanted spaces with keywords.

When to use this formula:

• Removing additional spaces at the end of URLs.
• Tidying up messy data (and avoiding issues with other formulas).
• Ensuring consistency across all URLs.

9. COUNTIF

The COUNTIF function allows you to count the number of cells that match one single criterion.

This allows you to be more specific in counting cells that contain a specific value, are greater than, less than or equal to a value or match a specific text.

Formula: =COUNTIF(range, criterion)

The range is the range of cells to count, and the criterion decides which cells should be counted.

In this case, we are only counting the number of keywords that rank in the top 5 search results.

When to use this formula:

• Getting an overview of ranking positions for different keywords. 
• Counting the number of URLs in different subcategories.
• Counting duplicates.

10. COUNTIFS

The COUNTIFS function allows you to count the number of cells that match multiple criteria.

Much the same as the COUNTIF formula, the COUNTIFS formula allows you to be more specific in counting cells that match several criteria – i.e specific values and text.

Formula: =COUNTIFS(criteria_range1, criterion1,[criteria_range2, …], [criterion2, …])

In the example, we are building on the COUNTIF formula counting just the number of keywords ranking in the top 5 to count the number of keywords ranking in the top 5 and contain the word “coat.”

When to use this formula:

• Getting an overview of ranking positions for different keywords. 
• Counting the number of URLs in different subcategories.
• Counting duplicates.

11. SUMIF

The SUMIF function allows you to add together the values in a specific range based on one specific criterion.

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

The range is the cells you want to apply the criteria against.
The criterion decides which cells to add.
The sum_range is the range of cells that should be summed together.

In this case, we are summing the total number of conversions from product pages.

12. SUMIFS

The SUMIFS function allows you to add together the values in a specific range based on multiple criteria.

Formula: =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2] ,…)

In the example, we are adding together the number of conversions based on two different criteria:

1. Product pages
2. Desktop users

When to use this formula:

• Summing traffic based on specific GA criteria. 
• General data manipulation and sorting.
• Summing keywords that match specific criteria. 

13. EXTRACTING A DOMAIN

Extracting a domain from a full URL is a fairly common practice especially when working with a large number of URLs.

I.e. Extracting the domain would look like example.com instead of https://www.example.com/category.

Formula: =REGEXEXTRACT(text, “^(?:.*://)?(?:www\.)?([^:/]*).*$”)

In the example below, we are using this formula to extract the domains from a list. 

When to use this formula:

• Backlink analysis and auditing.
• Creating disavow files. 
• Assessing link opportunities. 

14. UNIQUE

The UNIQUE function returns a list of unique values from a selected list or range.

Formula: =UNIQUE(range)

This example is providing a list of a de-duplicated list of repeated URLs. 

When to use this formula:

• Tidying up datasets with repeats.
• Getting a de-duplicated list. 
• Keyword organisation.

15. SUBSTITUTE

The SUBSTITUTE function allows you to replace any character of a cell value with another character.

Formula: =SUBSTITUTE(text, old_text, new_text, [instance_num])

The example below is replacing the word “jeans” with “shorts” in order to reproduce title tags very efficiently.

When to use this formula:

• Building title tags and meta descriptions across a large number of pages.
• Updating URL subfolders quickly and efficiently.
• Generating a list of URLs to be directed.

16. SEARCH / ISNUMBER

The SEARCH function returns the position of a substring within a string.

Combined with ISNUMBER (which provides TRUE or FALSE depending on whether the cell is a number) we can easily see if cells contain particular keywords.

Formula: =ISNUMBER(SEARCH (find_text, within_text, [start_num]))

In the example, we are searching across a list of keywords to see whether they contain the text “card”

This can be taken one step further by using the AND or OR functions which are logical functions used when there are more than one conditions at the same time, or to test more than one condition at the same time.

The following example is testing whether the keywords contain both the keywords “birthday” and “card.”

When to use this formula:

• Grouping URLs or keywords.

17. LEFT / RIGHT / MID

The LEFT, RIGHT and MID functions allow you to extract a substring from a string starting from either the left, right or middle of a string of text.

Formula: =LEFT(text, [number of characters].

Combined with the FIND function, which provides the position of a string found in the text, we can easily extract particular sections of text.

This example is extracting the first word from a string which contains [Town] + [Service].

When to use this formula:

• Extracting text from either side of a specific character. 
• Extracting the protocol or slug of a URL. 

Advanced Google Sheets Formulas

18. VLOOKUP

The VLOOKUP function allows you to combine several different datasets together.

This works by doing a vertical lookup of the value in the first column of a table and returning the value in the col_index_number position on the same row.

Formula: =VLOOKUP (value, table array, col_index_num, [range_lookup])

In the example below, we are combining two datasets:

1) The status code of a URL.
2) The number of external backlinks linking to a URL.

There are a few stages:

1. Enter the value into the lookup formula=vlookup(A2,
   
   The value is the common value that we are searching for over the two documents. In this example, this is the address.
   
2. Select the table array
   
   =vlookup(A2, ‘External Backlinks’ A:B,
   
   The table array should have the lookup value in the first column otherwise the formula will not work. Make sure this table includes the data you want to retrieve from the other columns also.
   
3. Select the column index number
   
   =vlookup(A2, ‘External Backlinks’ A:B, 2
   
   This should be the column number from which you want the value returned.
   
   So in this case, on the “External Backlinks – Reference Table ONLY” tab, the first column is the address, and the second column is the External Backlinks, so we would write number 2.
   
4. Choose which type of match
   
   =vlookup(A2, ‘External Backlinks – Reference Table ONLY’ A:B, 2, FALSE)

There is the choice to have an approximate match (TRUE) or an exact match (FALSE). In this case, we would want an exact match. 

19. VLOOKUP WITH IFERROR

VLOOKUP can also be nested within the IFERROR formula to replace the generic #N/A error message with a specific result.

Formula: IFERROR(VLOOKUP  (value, table array, col_index_num, [range_lookup]), value_if_error).

In this case, if there were URLs that did not have any corresponding external backlinks, the result would just return  #N/A – Using the IFERROR formula allows us to replace this with a specific result

When to use this formula:

• Combining different datasets for backlink audits. 
• Collating keyword data from different sources. 
• Combining crawl data with other metrics.

20. INDEX / MATCH

Similarly to the VLOOKUP function, the combination of the INDEX and MATCH formulas allows you to look up and return the value of a cell based on vertical and horizontal criteria.

This works because the MATCH formula returns the position of value in a given range and INDEX returns a specific value in a specific range.

Formulas:

=INDEX(array, row_num, [column_num])

=MATCH (lookup_value, lookup_array, [match_type]

The example below demonstrates how to pull out the number of conversions on blog pages for January.

There are a few stages:

1. Select all the data.=INDEX(B3:D14
   
2. Select the month, followed by the full range of months, followed by 0 (for exact match) in the MATCH formula.
   =INDEX(B3:D14, MATCH(G2,A3:A14,0),
   
3. Select the conversion page, followed by the full range of different conversion pages, followed by 0 (for exact match) in the MATCH formula.
   =INDEX(B3:D14, MATCH(G2,A3:A14,0), MATCH(G3,B2:D2, 0)

This will return the cell value that matches both the month of “January” and “Blog”

When to use this formula:

• Combining different datasets for backlink audits. 
• Collating keyword data from different sources. 
• Searching for specific values within a table.

21. PIVOT TABLES

Pivot tables provide a summary of your data, allowing you to group data from different columns and extract the overall trends of large and comprehensive data sets.

This example is pivoting data that consists of Keyword, Rank and Landing Page.

By pivoting this data and selecting the landing page for rows and COUNTA of keywords as values we can see the number of keywords ranking for.

In this case, we can see that dogs/clothes/coats/ is ranking for the most number of keywords.

There are several stages in pivoting data:

1. Select your data range.
2. Select Data > Pivot Table > New Sheet > Create
3. Select which columns you wish to have as rows, columns and values.
4. Order the data under the row section.

When to use this formula:

• Analysing ranking changes across the different pages. 
• Traffic analysis using GA data.
• Understanding site architecture with crawl data. 

22. ARRAYFORMULA

The ARRAYFORMULA performs an action or operation on multiple values such as into multiple rows and/or columns. This allows you to iterate the formula across multiple rows or columns by just entering one formula.

The calculation or formula to be applied should be nested within the ARRAYFORMULA.

Formula: ARRAYFORMULA(array_formula)

In this example, we are categorising the ranking position of keywords as either in the top 10 or outside the top 10. The array formula in this case means that this action is completed across the whole column.

When to use this formula:

• Applying formulas consistently across the large range. 

23. REGEX EXTRACT

REGEX EXTRACT allows you to extract matching substrings according to a regular expression (regex). This allows you to just extract parts of a strong i.e the domain name from a much longer URL.

For those new to this, regex is a matching expression tool returning specific values and is really useful for allowing you to be very specific about what text you are selecting.

Formula: REGEXEXTRACT(text, regular expression)

In this basic example, we are extracting just the subcategory from a list of URLs.

When to use this formula:

• Extracting specific pieces of information from a list i.e. log files
• Extracting emails or links from a block of text. 
• Extracting the domain for a URL.

24. REGEX REPLACE

REGEX REPLACE replaces part of a string with a different string using regular expressions (regex). This allows you to replace a specific piece of text with some other text.

Formula: REGEXREPLACE(text, regular_expression, replacement)

In this example, we are updating title tags – replacing all numbers with “2020″

When to use this formula:

• Updating metadata including title tags and meta descriptions. 
• Updating URLs for a migration. 

25. REGEX MATCH

REGEX MATCH provides a TRUE or FALSE response based on whether the text matches the regular expression. Combined with other formulas such as the IF formula, REGEX MATCH can be a powerful tool in data analysis.

Formula: REGEXMATCH(text, regular_expression)

In this example, we are using the REGEX MATCH formula nested within an IF formula to return the value “Resources Page” if the URL contains the text “resources.”

When to use this formula:

• Categorising keywords and URLs.

Google Sheets Power Tools

26. QUERY FUNCTION

The QUERY function allows you to use a form of SQL (which is more commonly used to communicate with databases) to manipulate data in Google Sheets.

There are several key commands which need to be done in order:

SELECT: Select which columns you want to pull in from the data

i.e “SELECT A, B, C”

WHERE: Select which conditions you want to set

i.e “SELECT A, B, C WHERE B > 6”

ORDER BY: Select how you want to order the data

i.e “SELECT A, B, C WHERE B > 6 ORDER BY B desc”

Formula: QUERY(data, query, [headers])

In this example, we are simply using the QUERY function to pull in just the 3 columns – A, B and G.

When to use this formula:

• Sorting and filtering large datasets. 
• Analysing crawl data. 
• Backlink auditing.

27. IMPORT XML

The IMPORT XML function allows you to import data using an XPath query from a number of data types including XML, HTML, CSV, TSV and RSS and ATOM XML feeds.

This effectively allows you to scrape data from websites without leaving the comfort of your own Google Sheets doc.

Several useful shortcuts for XPath extraction:

• All links on a page: “//@href”
• Extract all internal links on a page: “//a[contains(@href, ‘example.com’)]/@href”
• Extract all external links on a page: “//a[not(contains(@href, ‘example.com’))]/@href”
• Page title “//title”
• H1(s): “//h1”
• Meta description: “//meta[@name=’description’]/@content”
• Canonical: “//link[@rel=’canonical’]/@href”
• Robots – i.e Index or No Index “//meta[@name=’robots’]/@content”
• Hreflang attributes “//link[@rel=’alternate’]/@hreflang”

Formula: IMPORTXML(url, xpath_query)

In this example, we are simply scraping all the links from a blog page.

When to use this formula:

• Scraping data from webpages.

28. IMPORTRANGE

The IMPORTRANGE function allows you to import a range of cells from a specified spreadsheet – this data will then automatically update across all linked sheets.

Formula: =IMPORTRANGE(“spreadsheet_url” , “range_string“)

There are two parts:

The URL of the spreadsheet to be imported. 

The string specifying the range to be imported.

In this example, we are pulling in an Ahrefs keyword research export.

When to use this formula:

• Combining different Google Sheets. 

Google Sheets Formatting

29. CONDITIONAL FORMATTING

Conditional formatting makes it simple to highlight cells that match a particular criterion, which is really useful for spotting outliers or specific variations.

To use conditional formatting:

1) Select the cells you want to add conditional formatting to (usually a data range).

2) Go to Format > Conditional Formatting > Select the rules you wish to implement.

Conditional formatting gives the option to set a wide range of criteria; such as numbers over and under certain values, duplicates and the top and bottom 10% of figures.

In this example, we have some title tags and their length. 

I have used conditional formatting to create a number of rules so that;

1)  If the number of characters in the cell is over 60, the cell will go red
2) If the number of characters in the cell is below 61, the cell will go green.

This makes it simple to identify any title tags that are over 60 characters.

When to use this formula:

• Spotting outliers
• Presenting data to clients
• Making sheets look pretty

Conclusion

This guide has demonstrated how to use some of the most commonly used Google Sheets formulas for those in SEO and digital marketing. We will be periodically updating this guide over time, so watch this space.