Google Sheets Hacks for SEO

Google Sheets has been an essential tool for many SEOs for years now. Apart from it being free (woohoo!) it comes with a host of cool features to save a bunch of time on tasks, some of which are exclusive to this particular app. Here is a list of some of our favourite Google Sheets tips for every SEO to take advantage of.

Analysis

Since analysis is one of the most important jobs for an SEO, there are many many formulas and ‘hacks’ to help with this. I won’t go through every possible option, but here are a handful of my favourite functions that we use regularly.

Finding data

The vlookup() formula is brilliant when we need to find specific information from a large set of data. It looks to find the value of a cell within a column based on the contents of a cell in another column. Sounds complicated? Here’s an example of it in use, where we want to find the search ranking of a specific keyword based on a list:

The structure of of the vlookup formula looks like this:
=VLOOKUP(lookup_value, range, column, [range_lookup])
Or alternatively:
=VLOOKUP(What we’re looking for, where to look, which column, false)
In this example, the formula we’ve used is:
=VLOOKUP(G3, C:D, 2, false)
Where G3 is the keyword we’re looking for, C:D is the keyword ranking list and 2 is the column number within the range where the ranking can be found.

Compare two cells

Want to check if a list of changes have been made to your exact specifications? Sometimes there may be subtle differences, such as uppercase and lowercase, which can be hard to spot. See below for an example:

The formula used here is:
=IF(EXACT(B3,C3), “Yes”, “No”)
Remember that this formula is case sensitive. If you want a non-case sensitive version, use the following instead:
=if(B3=C3,”Yes”,”No”)

Review whole columns

Want some quick insights on what a column of data means? Here’s a snapshot of some how this can be done with some super simple formulas:

These insights use the following formulas:

• =COUNTIF(D:D,1) – Counts the number of or cells in a column equal to a particular value, in this instance it is “1”. You can replace this figure with a cell value for deeper analysis.
• =SUMIF(D:D,1,E:E) – Returns a total of a column E where the value of the corresponding cell in column D is equal to “1”.
• =AVERAGEIF(D:D,1,E:E) – Returns a total of a column E where the value of the corresponding cell in column D is equal to “1”.

There’s a wide range of tasks where these formulas are useful. Have a play around with them and see what you can use them for.

Pivot tables

Pivot tables are a great way to segment large amounts of data to find detailed insights in seconds. For example, say we want to know how many keywords rank in each position, its possible to use the countif formula mentioned above, but a quicker way to do this en masse is to highlight the data, then go to ‘Insert > Pivot Table’ and select the right options. See the example below:

The possibilities go way beyond what we have space for in this article, but the best way to learn is to just create one for a set of data you have and see what insights you can find.

Combine columns

Got several columns of data that need to be merged into one? Want to be able to ignore any empty columns? Easy, just use the textjoin() formula. Here is an example to combine address lines into a single cell:

The example formula is =TEXTJOIN(“, “,true,B3:D3). The “true” part of the formula allows you to ignore any empty cells, making the result a lot cleaner than other concatenation formulas. This also adds a comma between each cell value, but this can be changed to whatever delimiter you like.

Do you want to split out data that’s already joined? Even easier! Just use the split() formula. Here’s an example:
=SPLIT(E3,”, “,false)
This will split the joined up address into its individual elements again.

Review web pages

Reviewing large numbers of web pages can be time consuming. Crawling tools like Screaming Frog can make the process a lot quicker, but Google Sheets can make things quicker still.

Extract information from web page

Wouldn’t it be great if we could extract information from a website directly into a spreadsheet for analysis? What’s that? You can? Yup, the importxml() formula can be an invaluable time save with a multitude of possibilities. Below is an example of this formula:
=importxml(“https://www.example.com”,”//title”)
This will import the page title directly into the sheet. The bit at the end of the formula is a piece of code called xpath (for more information on xpath, here is a handy guide).

Here’s some other simple ways that importxml() can be used:

Image

Extracting text from a web page is great, but wouldn’t it be REALLY great if you could show an image from a site in a Google Sheet? What’s that, you can? Awesome! The formula to do so is as simple as =image(“https://example.com/image.jpg”). Here is an example of it in use:

This can come in really handy when you want to plan the anchor text for a list of images. But wouldn’t it be really really useful if you could simply have a list of page URLs and extract the images from them straight into the sheet? For example, if we want to review the anchor text of a bunch of product images without having to find the image URL? That can be done as well. By combining the importxml() formula mentioned above above with the image() formula you can review the primary images on a list of pages quickly and easily like this:

In this example, we have a list of product URLs and have automatically extracted the primary image for those products and their anchor text for quick review.

Google translate

Sometimes as an SEO, you need to work on sites that aren’t in your native language. Translation tools like Google Translate can save a lot of time when it comes to analysis, although should not be seen as a substitute for a qualified translator. Google Sheets can quickly translate a list of text using the formula:

• =GOOGLETRANSLATE(text,current_language,new_language)

I once worked at a large, multinational retailer where many of the product descriptions on foreign sites were written in English. In order to rectify the issue, we had to get a full list of all the products which were in English and those in the correct language for every single country. Fortunately, we were able to shortcut this with the DETECTLANGUAGE() formula. This simply and quickly estimates what language a piece of text is written in.

Integration

One of the major strengths of Google Sheets is the ease with which data from 3rd parties can be integrated into your spreadsheet for quick and detailed analysis. Here are a couple of examples of this.

Import from other sheet

When working on large projects, it makes sense to have your data saved across multiple spreadsheets. However, sometimes one spreadsheet requires the data from another and it doesn’t make sense to copy and paste it in case the source data changes.

For this, the IMPORTRANGE() formula can instantly import data from one spreadsheet to another. Just use it in the following way:

• =IMPORTRANGE(“spreadsheet_url”, “range”)

One thing to be aware of is that if the data in the source sheet changes, so will the data in the imported sheet. Therefore, it’s important to keep everything up to date to make sure that mistakes aren’t made.

Integrating with other apps

There are many apps available for Google Sheets which can make it an incredibly powerful tool. 2 of my favourite apps for SEO are:

• Search Analytics for Sheets – This is a brilliant little app that imports search data directly from Google Search Console. Unlike the main GSC interface, there is no limit to how many rows you can import and you can filter that data in a multitude of ways.
• Supermetrics – This is an amazing (albeit, pricey) tool that is a bit like the above app on steroids. It can connect to a wide range of data sources like Google Search Console, Google Analytics, Google Ads, Facebook, Instagram and more. This is easy to implement and filtered to your heart’s content.

Sharing

Collaboration is another key feature in Google Sheets, with multiple users being able to work on a sheet with full version control. Here’s some examples of the sharing options available.

Sharing and protection

To allow others to work on your sheet, simply click on the “Share” button in the top right of the screen. As well as showing who has access to the document, this will provide a list of options, including sharing via a link so anyone can see it or sharing only with specific people by typing in their email address like in the screenshot below:

You can control what level of access users have here too by selecting Viewer, Commenter or Editor options.

It’s important to protect your business by controlling who has access to the document. It is not recommended to share any confidential information by copying a link unless that link is restricted to members of your organisation. It’s also worth checking who has access periodically to ensure it’s not been shared with anyone it shouldn’t have been.

If the sheet has been changed and no longer works, you can go to ‘File > Version History’ to see what was done and who did it. This will help to recover important work and prevent mistakes in the future.

Leave comments

Comments are a really useful way to quickly leave feedback on a spreadsheet without altering any of the information on a page. Simply right click on a cell and leave a message like in the example below:

Any other users of the document will then be able to see the feedback and take any action necessary as well as leaving a nice reply.

Create QR codes

Imagine you have 100 locations and you want to increase the number of positive reviews each branch gets. One way is to ask your happy customers to scan a QR code which takes them to a URL to leave a lovely review. If you need to do this for a large number of pages, there is a way to do this REALLY easily in Google Sheets. Just use the following formula:

• =image(“https://image-charts.com/chart?chs=150×150&cht=qr&choe=UTF-8&chl=”&ENCODEURL(A2))

Whilst it might look like a handful, it’s literally a copy and paste job whilst updating the cell location at the end. Below is an example of this in action:

Presentation

It’s all well and good to be able to do cool stuff in a spreadsheet, but it needs to be presented in a tidy way that’s easy to understand. Here’s some examples of how.

Visualise with colour

Colour can be a powerful way to show what data means within a spreadsheet. For example, if we wanted to show ranking changes for a list of keywords and visualise the overall ranking shift, we can do this easily by highlighting the data and going to ‘Format > Conditional Formatting’. The we can set up rules for below and above zero to show positive or negative movement like in the following example:

Alternatively, we may want visualise which keywords are currently performing the best and the worst like in the following example:

This can be done quickly by going to Conditional Formatting and selecting Colour Scale as shown here:

Create checklists

You can even use Google Sheets to project manage your task lists. This can be useful for all sorts of situations and is really easy to do. Just go to ‘Insert > Checkbox’ and you can copy and paste to as many cells as you like. You can also use formulas to calculate your checklist progress. Here’s a super simple example that I created with some typical technical SEO tasks:

You can colour code them too by adding conditional formatting with the conditions of ‘Text is exactly TRUE/FALSE’. The best bit, you can automate it to make full use of the spreadsheet functionality. By using an IF() formula, you can get your checkboxes to automatically tick when a task is complete.

Miniature graphs

Graphs have been a feature of spreadsheets for a long time, but Google Sheets now has the ability to insert charts directly into cells to visualise data using the SPARKLINE() formula. See below for an example:

There’s a ton of customisation options available too, such as chart type, colours, line thickness and axis options. Visit here for a full list of customisation options for this super cool feature.

Tidy up data

Sometimes when you get data from an external source, the formatting just isn’t quite right. Random spaces, capitalisation or line breaks can mess with your data and make it difficult to review. Luckily, we have a few formulas to help:

• TRIM() – This removes any spaces from around the text string.
• LOWER() – Makes everything lowercase.
• UPPER() – Makes everything lowercase.
• PROPER() – Make the first letter of every word uppercase. Particularly useful if you want to automate writing title tags.
• CLEAN() – Removes unprintable characters from the cell. The most common example I come across is random line breaks.

Below is an example of of each of these in action so you can see what they do for yourself:

There are a lot more

We’ve only scratched the surface on what is possible through Google Sheets here, but hopefully we’ve given you a good platform to find even more amazing features. Get stuck in and see what is possible, the chances are the only limit is your imagination. For everything else, there’s probably a Google search for it.