Formulas for Success – Google Sheets & Excel for SEO

As an SEO I spend large parts of my day reading and analysing large sets of data from all kinds of sources, which inevitably means exporting to an excel document or Google sheet. Knowing how to filter the data quickly get to the answers to the questions you are asking is vital. As the collection and manipulation of data shouldn’t be the longest part of the decision-making process when making decisions on optimising a website.

The Fundamentals of Finding, Filtering and Formatting

Filtering

These formulas and processes are for when you want to manipulate data to bring back a list of specific results.

The standard way of filtering large data sets, whether it be numbers, dates, or text would be to use the in-built column filters, where you can sort and filter based on a specific criterion.

However, there are times where we need to filter by multiple conditions, and others where we would like to retrieve multiple filtered results from the same data sets. So using the built in filters isn’t ideal.

This is where Google Sheets is extremely powerful, as we can use the =UNIQUE, =FILTER and =SORT functions.

Although Excel does now have these formulas, Google sheets is (in my opinion) far simpler to use.

The best part of using these functions rather than column filters is you can combine them together to create extremely powerful formulas that extract very specific results.

Real world example

Let’s say that you have exported all the backlinks for your site and wanted to know the number of unique webpages that are pointing at a specific page with “follow” links. And then only show pages from sites with a Domain Rating of 40 or above.

The “normal” / non-formula approach, would involve setting a filter in Column C to only show pages that match X, then adding a second filter in Column B to only show Domain ratings of 40 or over. Finally, you will have to then remove duplicates in Column A.

The formula option however, is really quite straight forward, brings back the results we need and can be controlled by the 2 conditional cells in Column E, which means we can quickly change up the conditions to see different results.

Looking at the formula in detail, we can see that we are asking Google Sheets to do the following:

Finding

Slightly different to filtering, finding is when we are looking to get an answer to a specific question, whether it be the total numbers of X or the sum of all results that meet a certain criterion.

Some of the most common stand-alone formulas for these tasks are =COUNTIF(S) and =SUMIF(S)

One of the formulas I use most day to day is INDEX,MATCH, as it allows me to quickly grab data from one source and match it to another.

Very similar to VLOOKUP INDEX,MATCH is extremely powerful either used on its own or nested into complex formulas.

Real world example

In this example, we have data from 2 different sources.

The first sheet is an export from Google analytics, showing us traffic through to the/blog/ section of the website.

The second image is an export from a website crawler (in this case Screaming Frog) showing the word counts on each page.

By using the INDEX,MATCH formula, we can get the spreadsheet to bring back the word counts from the second sheet and match them with the page URL in the first.

The formula would look something like this

=INDEX({the range containing the wordcounts},MATCH({the cell with the page URL},{the range of page URLs next to wordcounts},0)

Now although we do have an error message returned, it’s not a “bad” error, meaning the formula is correct, just there was no match. This is because different tools present data in different ways (we will discuss this in more detail under formatting), and Google Analytics does not include the website domain part of the URL.

So, we need to make a slight adjustment, options are, adding https://www.example.com to every page URL, or in the formula, add a wildcard to the beginning of the MATCH criteria. Essentially telling it to find /blog/article-1 even if it has other text before it.

And the result is

Formatting

Whether it’s extracting part of a cell, or changing a whole number to a date, making the data easy to read / easy to use, is incredibly important.

When it comes to formatting, there are endless situations where the data type doesn’t match up to what you need, and if not spotted could result in incorrect numbers being added to calculations.

A really good example of this is in Google Analytics, when exporting page metrics.

Below is a screenshot of the top 10 landing pages on the Google Merchandise Site.

The issue that we are faced with here, is the standard export of average session duration is in seconds. Which for a report can be difficult to understand. So, the logical next step would be to change the cell format to “Duration”. However, this will not work and will results in numbers that are massively out.

This is because the Duration format considers 1 to be a day, and therefore, any time less than a day needs to be a decimal.

What we need to do to convert this correctly is convert the seconds into a decimal day, this can be done by dividing your Avg. Session Duration by 86,400 (seconds in a day). And then reformat those cells as duration.

Excel can also be your friend when conducting keyword research with lare data sets, our Simple Keyword Research Excel Tips to Help With SEO expands on this even further.

The benefits of knowing your Excel tricks extend to not only SEO but also to your paid activity, findout our thoughts on the top tips to make your PPC simpler and more efficent.