Google Sheets Tips & Tricks for SEOs: Basic, Advanced and New Features

The journey to a successful SEO strategy begins with a single spreadsheet. Clichés aside, any digital marketer should know just how valuable and handy certain SEO tools can be in terms of efficiency and streamlined workflow, regardless of the sub-niche. However, some tasks, both menial and convoluted, are best done using simple and seemingly outdated tools – like Google Sheets. 

Google Sheets is a flexible, user-friendly, and intuitive piece of software that lets you easily manipulate (import/integrate/merge/validate) any type of data using its built-in functions, add-ons, and templates, with an option to develop your own custom scripts. Though it is not designed specifically for Search Engine Optimization purposes, this tool can be an ideal solution for various SEO tasks and activities including project coordination and overview, quick analysis, validation, and real time collaboration with clients and other team members, etc. 

Regardless of whether you are an experienced SEO mage, or you are only now entering the digital marketing arena, knowing how to maximize the utility you can get out of Google Sheets will surely boost your performance and help your workflow become more systematic and methodical. 

Let’s warm up with some of the basics, while new and advanced SEO tips are featured later in the article.  

Basic, Must-Know Google Sheets Features and Functions for SEO 

1. Learn Google Sheets Keyboard Shortcuts to Speed Up Your Game 

Much like it is the case with pretty much any other piece of software out there, taking advantage of handy keyboard shortcuts can save you a ton of time, especially if you are a heavy Google Sheets user. To navigate, format and use formulas more efficiently, try using Google Sheets keyboard shortcuts as frequently as possible.  

You can access a complete list of GS shortcuts through the following page. 

2. Filter Data in Cells Using Google Sheets Filter

For those dealing with substantial datasets on a daily/weekly/monthly basis, the option to filter data that way you see fit is truly a lifesaver.

To create a filter for your data, simply:

1. Select the cells you want to filter
2. Click on “Data” 
3. Click on “Create a filter” 

You will also have the option to choose how to filter your data from the drop-down list. To access these options, go to the top of the range and click “Filter”. 

When you want to turn the filter off, simply click on “Data” and “Turn off filter”.

3. Use Paint Format for Fast Clone Formatting 

You can easily clone your formatting without the use of manual copy-paste. To use this nifty option, you need to:

1. Highlight the cells that feature the formatting you want to copy

2. Click on the Paint Format icon located within the toolbar

3. Drag the cursor over the other cells you want to apply the highlighted formatting to.

4. Using The IMPORTRANGE Google Sheets Function

The IMPORTRANGE Google Sheets function is extremely useful when you need to import data that is located in a completely different spreadsheet. The IMPORTRANGE formula is as follows:

=IMPORTRANGE(“spreadsheet_url”, “range_string”)

To apply this function, you need to:

1. Go to your destination sheet and type in the IMPORTRANGE formula.

2. Insert the first parameter “spreadsheet_url.” To do this, simply copy and paste the URL of the spreadsheet from which you want to pull data into the IMPORTRANGE function.
Pro Tip: Instead of using the entire URL, you can also use just the unique “key” part of the URL as shown below:

3. Insert the second parameter “range_string” which is the range of the cells containing the data you want to transfer.
Important: As the source spreadsheet may contain multiple sheets, first insert the name of the sheet followed by an exclamation point, then the range of the cells. Here’s an example with the screenshots below: 

“Data! A1:B17”

The entire formula for our example should look like this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Cybp8N_rxCLddcI_HOcUgxDefxV3TEOO8VGxHAa0Vfo/edit#gid=0", "Data! A1:B17”)

4. Once you are positive your formula is correct, hit Enter. Now, you will be prompted with the reference error, which looks like this: 

This happens because the target sheet doesn’t yet have the permission to fetch the data from the source sheet. To get your data imported, simply hover over the #REF! symbol and click on Allow Access. 

5. Leverage the Power of “ARRAYFORMULA” 

Being able to group the cells in a particular order is extremely useful. This can be done by using arrays – which are, simply put, tables of values. When applying the ARRAYFORMULA, Google Sheets processes your data in a single batch, and the changes you want to make can be done in just one place, while the effect will be taken across the entire data range so you don’t have to do it manually.

The formula is the following: 

=ARRAYFORMULA(array_formula)

Here, the “array_formula” can refer to two things: 

• a range – a mathematical expression that uses either one cell range or multiple ranges of the same size
• a function – returning a result greater than one cell

Here are some screenshots to help you out:

6. Use Simple Google Sheets Formulas to Format Text  

Those who like to work quickly and leave ironing out the formatting for later, are likely using the copy/paste option a lot. If this is the case, the best way to deal with messy text formatting in the cells is by using Google Sheets Formulas. 

3 functions to help you with this include: 

• UPPER – change all to uppercase
• LOWER – change all to lowercase
• PROPER – capitalize each word
• TRIM – remove extra spaces within the data text

To use these Google Sheets Formulas, simply type in the following (we’ll use the UPPER formula in our example): 

=UPPER(text)

NOTE: Instead of “text” in the brackets, you can also insert the cell number, for example (A1). Or you can simply type in =upper() and while your cursor is placed within the empty brackets, you can just click on the desired cell(s). The same principle goes for other Google Sheets Formulas. 

Pro Tip:   

You can also combine PROPER and TRIM formulas into the same function and get two functions for the price of one, like this: 

=PROPER (TRIM(A21)) 

Once you hit Enter, the results will look like this:

Pro Tip 2: 

You can also combine the aforementioned ARRAYFORMULA with PROPER:

=arrayformula(proper(A1:A))

This way you can copy the messy text from column A, correct it and place it nicely formatted in column B. 

To find out more on handy SEO-specific Google Sheets formulas, check out this informative in-depth article by BlueClaw. 

7. Learn How to Make a Graph in Google Sheets

In order to make your relevant data come to life so it is easier to convey what your SEO efforts are all about, you will want to create and customize graphs (or charts) in Google Sheets. This can be done in 3 easy steps:

1. Select the cells you want to include in your graph

2. Click “Insert” from the dropdown menu and select “Chart”

3. Once the chart pops up, you can customize it via the Chart Editor located on the right-hand side of the screen.

The Chart Editor window appears each time you create a chart, and has two main tabs: 

• Setup – here you can choose the chart type and the data range you want to include in your visualization 
• Customize – here you can modify your chart’s appearance

If all the data you want to show adds up to 100% – we suggest using a pie chart; if you want to compare data over a certain period of time – use histograms or line graphs, etc. 

8. Embed and Publish Charts/Graphs 

Once you’ve created a cool-looking data-packed chart or graph, you can easily embed them and publish them to a website. 

To make this happen, click on the chart you’ve created, click “More” located at the top right-hand corner, then click “Publish chart”, and then “Embed”. Your chart’s HTML code is ready to be used on your website. 

Friendly Tip:

If you are looking for a tool specially designed for quick and user-friendly client-reporting, we suggest you check out Reportz – an intuitive piece of software made for digital marketers (by digital marketers) who need informative, KPI-based and data-driven customizable dashboards, charts and graphs. 

9. Using Pivot Tables (Pivot Chart)

Summarizing large sets of data is best done via the Pivot Chart (or Pivot Table) option through which you can take your standard two-dimensional table and add a third dimension by pivoting it around data aggregation. 

You can create Pivot Tables in 3 easy steps: 

1. Go to the desired sheets and highlight the cells that contain data
2. Click on “Data” located in the drop-down menu and then hit “Pivot Table”
3. Click on “Create” in the pop-up window

To customize your Pivot Chart:

1. Locate “Rows and Columns” on the far right, click on “Add” next to it and choose the data you want to analyze
2. Locate “Values” and click on “Add” next to it in order to choose the values you want to display within the rows and columns
3. Click on “Filters” to display the values you want 

More on how to use Pivot Charts HERE. 

10. Find and Replace 

Google Sheets has a neat find and replace option that is quite helpful when you need to – you guessed it – find a particular word/value and replace it with another one, on several different levels. You can do it within a single sheet, all sheets, or a custom range. 

To perform this command, you need to:

1. Click Edit and then Find and replace (the shortcut for this is CTRL+H). The following window will pop up.

2. In the “Find” bar, type the word/value you want to replace, and in the “Replace with” bar, type in the new word/value. 

3. To choose the level for your Find And Replace command, click on “All sheets” drop-down menu and choose between: This sheet, All sheets, or Specific range. 

4. You can also fine-tune your search via:
– Match case: Makes your search case-sensitive.
– Match entire cell contents: Searches for cells that are exact match.
– Search using regular expressions: Searches for cells that match a pattern.
– Also search within formulas: Searches include formulas as well.

5. Finally, click Replace to replace the desired word, or go with Replace all if you want to replace all the instances of the highlighted word. 

Bonus Tip: Find and Replace Google Docs

If SEO is your niche of choice, chances are you’re using Google Docs on a daily basis as well. This piece of software also features the Find and Replace option. The procedure is pretty much the same, except there are fewer fine-tuning search options:

Where “Match case” is used to tracks down words with the same capitalization only, while “Match using regular expressions” is used to tracks down words based on regular expressions.

11. Fetch Images from URLs

Handling images is an important aspect of SEO. Likely, fetching images to your Google Sheets is a rather simple task. To do this, type in:

=IMAGE(“URL”) 

Replace the “URL” with an actual image URL, and you’re good to go. 

12. Utilize The Almighty VLOOKUP Google Sheets Function

This function can be used to track down and retrieve matching data from another table located either on that same spreadsheet or a completely different one, which is quite a nice time-saving workaround. 

The VLOOKUP Google Sheets command works like this: it first performs a vertical lookup through which it searches for a key value (aka a unique identifier) within the first column of the range you specified, and then fetches that value (located in another column) to the desired row. 

The VLOOKUP Google Sheets formula is as follows:

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

Here’s what each of the parameters in brackets refer to:

• Search_key – refers to the value you want Google Sheets to look up for. This can be either a word (like “Price”), a number (like 100), or the entire cell (B2 for example). 
• Range – refers to the range (columns of data) you want to search. Google Sheets will always search within the first column of specified range.
• Index – refers to the column index of the value you want to fetch, where the first column in range has index 1.
  Note: If the index value is less than 1, the formula will show: #VALUE! error. If the index value is greater than 1, VLOOKUP formula will return the: #REF! Error.
• Is_sorted – determines if the Vlookup Google Sheet formula should return the exact match (FALSE) or the nearest match (TRUE). In most cases, FALSE is the recommended option. 

If the first column of the specified range (the lookup column) contains multiple exact matches (the values exactly equal to search_key), the formula will return the 1st value it finds. If there are no exact matches, the #N/A error will appear. 

If you omit the is_sorted parameter (which is the default) or set it to TRUE, the formula will return an approximate match, while the lookup column must be sorted in ascending order (either from smallest to largest, or from A to Z). 

The approximate match Vlookup formula still searches for the exact match first, and if it cannot find an exact match, it looks up the closest match – a value that is less than or equal to the specified search_key. In cases where the values in the lookup column are greater than the specified search_key, the formula will return the #N/A error. 

To find out more on the VLOOKUP Google Sheets formula and what it can do, visit this detailed blog post from AbleBits. 

Moving onto:

Cool New Features in Google Sheets

1. Remove Duplicates 

Great news is that now you can find duplicates in Google Sheets – and remove them – very easily. Before the team over at G Suite introduced this option, you could technically do this via some workaround options (manually with CTRL+F; or via Google Sheets highlight duplicates through Conditional Formatting), but now with the official Remove Duplicates option, you can do it in mere seconds. 

1. Highlight the cell range from which you want to remove duplicates
2. Click on “Data”
3. Click on “Remove duplicates” and then hit “Ok”

During the third step, you can once again revise (check/uncheck) the columns you want to include. 

2. Trim Whitespace

SEO typically requires adding massive amounts of data to your sheet, which may result in messy sheets with inconsistent formatting. For those who like to keep their spreadsheets tidy and their formatting standardized (which we hope is a majority of you), Google Sheets has recently introduced the Trim Whitespace feature that will help you easily remove any leading, trailing, or excessive whitespace (within one cell or a whole array of data).

To do this, simply:

1. Highlight the cell range you want to trim
2. Click on “Data”
3. Click on “Trim whitespace” and then hit “Ok”

3. Compatible Spreadsheet Shortcuts 

In an effort to make their platform even more user-friendly, the UX team behind Google Sheets made sure we can now use popular and familiar keyboard shortcuts from other spreadsheets in Sheets for quicker and more streamlined workflows. 

To enable Compatible Spreadsheet Shortcuts, you need to:

1. Go to “Help”, then “Keyboard shortcuts” 

2. This Keyboard Shortcuts window will pop up:

3. Toggle the Compatible Spreadsheet Shortcuts switch located at the bottom

4. Click on “View compatible shortcuts” located at the bottom right.  

Advanced, SEO-Specific Google Sheets Tips and Tricks

1. Extract Data From Strings Using the REGEXTRACT Formula 

The REGEXTRACT formula is a nifty one for those who seek a quick way to extract sub-strings from a single cell or a string of cells.

The syntax is as follows: 

=REGEXEXTRACT(text, regular_expression)

This function is best used for the following tasks: 

• Take out domain names from hefty URL lists (example below)
• Quickly check if URL uses HTTPS or HTTP
• Extract URLs without root domains 
• Quickly track down and extract email addresses from big blocks of text
• Filter out the URLs according to specific words and phrases they contain. For example, you can extract only the URLs that contain the “guest-post” in their slug.

Example: 

Say you need to fetch root domains from your list of guest post opportunities (“write for us”) opps. Here’s how to do this in just a few steps:

1. Place the list of original guest-post opps URLs in column A
2. Make the B column “Domain (REGEXEXTRACT)”
3. In column B write your REGEXEXTRACT formula with the following syntax (this is for one row only):

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

4. To perform this function for the entire column A, we need to incorporate the ARRAYFORMULA and IFERROR formulas as well, so the syntax will look like this:

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

2. Quick Validation of 301-Redirects Implementation

Typically, whenever there’s web migration/redesign/web update scenario that results in many a URL changes, chances are you will need to perform 301-redirects validation implementation via an old URL list-crawl in order to see if they’re properly redirecting to new URL versions following the specified mapping rules.

This can be done by comparing the implemented HTTP status by using the IF function, redirect destination function, while also through the metadata of destination pages extracted from the list-crawl compared to the ones you recommended. 

The syntax should look like this:

• HTTP Status: 

=IF(D2=301, “correct”, “incorrect”)

• Redirect Destination: 

=IF(B2=E2, “correct”, “incorrect”)

• New Title Implementation: 

=IF(C2=F2, “correct”, “incorrect”)

3. Use IMPORTXML to Scrape Data From Any Website 

This handy feature allows you to scrape the web without having to leave Google Sheets by letting you to import data (via XPath query) from various structured data types like HTML, XML, RSS, etc… 

The syntax is as follows: 

=IMPORTXML(url, xpath_query)

Where URL refers to the URL of the page you want to examine, including protocol (e.g. http://). 

The URL value should be enclosed in quotation marks, but you can also enter the cell mark (eg: B2). The “xpath_query” refers to query to run on the structured data. To find out more about XPath, visit this XPath Tutorial. 

Some of the main IMPORTXML benefits include: 

• Extracting metadata (e.g. title, description, h‑tags) from URL lists
• Scraping web pages for email addresses 
• Scraping web pages for social media profiles 
• Scraping RSS feeds for lastBuildDate (which is a great workaround to see how recently a website was updated without actually loading the page)

4. Use IFS Function to Compare Search Console Performance Metrics from Two Time Periods 

The majority of SEOs who use Google Search Console probably know how useful comparing GSC performance metrics from two different time periods can be. Especially when you want to compare metrics before and after a major SEO event (like a crucial web release, latest Google update, or a new SEO related implementation) and then use that data to track changes in the performance metrics. 

This Google Sheet function is quite handy (and oftentimes necessary) as the Google Search Console Performance report UI doesn’t allow you to directly compare the difference in performances of two time periods. 

You can easily do this via the Google Sheets export option provided within your report, but you need to add a “comparison” column after both time periods, and then properly utilize the IFS function to compare them. For this, you will need to set “increased”, “decreased” or “didn’t change” as outcomes within the comparison columns cells. 

The syntax is as follows: 

=IFS(B2>C2, “Increased”, B2<C2, “Decreased”, TRUE, “Didn’t Change”)

You can also deploy Conditional Formatting to make the increased/decreased cells easily spottable by changing the cell color according to the text they contain. So, for example, the table for clicks comparison would look something like this:

5. Leverage The Power of Search Analytics for Sheets 

Search Analytics for Sheets is a rather powerful add-on that can help you obtain valuable data from Google Search Console. You can filter and segment the fetched data the way you see fit, while you can also create automated backups on a monthly basis.

Here are some of the most beneficial, SEO-related functions this neat add-on offers: 

Automated Search Analytics Backup

To enable Search Analytics for Sheets add-on to perform automated search analytics backup, you need to:

1. Select the website 
2. Select the parameters like Period and Search type
3. Choose Grouping and Filtering (we suggest you include Query, Page and Country grouping to easily separate wheat from chaff) 
4. Click on Enable backup.

Once all the necessary steps have been completed, your current spreadsheet will feature the enabled backup that will (from that moment on) run automatically and return the data from the previous month into the sheet. 

Getting Insights into Queries and Their Associated Landing Pages

This simple trick can help you diagnose traffic changes, track down content optimization opportunities, check out appropriate landing pages, and obtain other useful data related to queries/landing pages. 

You can request this data in just a few simple steps: 

1. Select the desired website
2. Choose the preferred date interval (the default is set to fetch the minimum and maximum available GSC dates)
3. Select “Query” and “Page” in the group field
4. Hit “Request Data”

Here’s an example:

You can use this handy data to:

• Find keyword opportunities
• Boost CTR
• Find out why your traffic dropped, etc…

Using Grouping/Filtering Options to Attain Higher Granularity 

Country and Device grouping allows you to deepen your data analysis and better understand who your target audience is and where they come from, while the filtering options provides you with an opportunity to get specific data to one or more dimensions. Date grouping gives you insight into the actual day when the impressions/clicks/CTR/position were recorded. Those who target audiences from multiple countries can use the Country grouping to see how a certain website fares internationally.

As for filtering options, you can choose between query/page/country/device to select what data to retrieve. 

To find out more about Search Analytics for Sheets, visit this great piece from Moz. 

6. Learn To Conduct Easy Competitor Content Research 

You can deploy Google Sheets to assist you in conducting competitor research, getting data for more successful content topic planning, and performing content gap analysis. Of course, you will need additional reinforcement in the form of potent SEO tools.

The way we like to do this type of competitor analysis is to use Ahrefs to obtain a list of best performing content pieces from a competitor’s website and blogs, then export that list into Google Sheets, while assigning separate spreadsheet columns to metrics like:

• organic traffic
• content length
• referring domains
• social shares
• publish dates…

This way you will have a quickly accessible overview of what type of content performs best and which topics resonate the most with your target audience. 

7. Use Google Analytics Add-On to See How You Can Improve Engagement/Conversion 

Leveraging certain Google Analytics metrics can help you get valuable insight into potential opportunities to boost conversion rate and engagement levels on your top ranked pages. 

To do this, you need to:

1. Add the Google Analytics add-on to your Google Sheets account (for those who don’t already have it)
2. Utilize the CONCATENATE function to generate the full URL of every page 
3. Use the VLOOKUP function to directly include the metrics (like Users, Sessions, Bounce Rate, etc) into your sheet.

8. Track Down Any Potential Content Cannibalization 

You can use the metadata integration within the ranked queries/pages information fetched by the Google Search Console to see if certain pages are currently ranking for the same queries. This can be done by examining the metadata relevance, the indexing configuration of the pages that are currently competing, and the number of backlinks.

Here’s how to do this:

1. Create a filter for your Queries column
2. Click on Filter By Condition, and then choose Text Contains
3. In the Value or Formula bar type in the query you want to check for content cannibalization

9. Uber-Granular Data Querying/Retrieval with =QUERY 

The QUERY function works like VLOOKUP, but it allows you to query data via SQL, making your data querying and retrieval efforts easy and providing you with highly granular results. 

The basic syntax looks like this:

 =QUERY(range, sql_query)

Some of the most beneficial deployments include:

• Ability to acquire specific, fine-tuned link prospecting opportunities, for example – guest posting/DR above 50/contact details present… 
• Opportunity to create highly granular, client-facing docs fed with the data from a “master” spreadsheet
• Track down pages that need attention through querying a massive on-site audit 

Example:

Let’s say you want to fetch all “blog post” tagged URLs and transport them into a new spreadsheet. The QUERY function would look like this:

=QUERY(DATA!A:B,”select A where B = ‘Blog Post'”)

To fine-tune your search and pull, for example, a list of referring pages with specific parameters like: dofollow link/DR>50/Backlink status = active/external links count < 40. 

The QUERY function will look like this:

=QUERY(‘DATA – site explorer export’!A2:R,”SELECT E where D > 50 AND H < 40 AND M = ‘Dofollow’ AND N <> ‘REMOVED'”)

Friendly Tip:

If you are a fellow SEO warrior who relies heavily on link building, we strongly suggest you check out Dibz – a link prospecting and influencer finding tool built by industry experts who know just how exhausting and tiresome seeking link building opportunities can be. With Dibz, you will be able to fine-tune your link prospecting searches to extreme details and finish the entire process in under 15 minutes. 

10. Perform Change Detection Through Screaming Frog and Google Sheets

Being able to track and keep record of key changes to the website is quite useful, regardless of whether your niche is SEO or, for example, Software Development. In certain scenarios, if these changes (that often include updated internal links, on-page content, external plugins, etc) go unnoticed, it can result in messing up months of your hard work. 

The changes Google Sheet can detect after looking at your new crawl data and old crawl data include: 

• Newly found pages – detects new crawl URLs that don’t exist in the old crawl
• Newly lost pages – detects old crawl URLs that aren’t found in the new crawl
• Indexation changes – detects non-indexed or canonicalised URLs
• Status code changes – finds any redirected URLs that are now code 200
• Meta description/URL-level Title Tag changes
• URL-level H1/H2 changes
• Newly added or missing keywords on a website level 

To perform this change detection, follow these simple steps: 

1. Use Screaming Frog to run a crawl of the pages you want to check for potential changes
2. Wait for a couple of weeks
3. Perform another Screaming Frog crawl 
4. Export the internal_all report for both (old and new) crawl and paste the two sets of data into separate tabs 
5. Wait approximately half an hour 
6. Check changes in the result tabs 

Pro Tip:

You can also import the data from Google Search Console to get “value lost” data for removed keywords.

To find out more about this handy Screaming Frog and Google Sheets collaboration, check out the following article: How to Do Change Detection with Screaming Frog and Google Sheets.  

Your Two Cents

We hope this article will help you in your future SEO endeavors. In case we forgot to mention some useful SEO-specific Google Sheets functions, feel free to contact us with your suggestion(s) and we’ll update the article accordingly. In the meantime, check out Freelancer’s Playbook: How to Land Perfect Clients for your Service.