Google Sheets at SEO specialists’ service – part I

Google Sheets, i.e. a cloud version of the well-known and liked Excel, is every SEO specialist’s essential work tool! Not only does it offer multiple features and excellent performance, but it also allows you to easily automate daily processes. Oh, and one more thing: it’s free

I’ll show you some examples of how to apply this tool so that you become more aware of its potential. Here are my top 5 functions that, I believe, every SEO specialist should know

Let’s begin!

JOIN function

=JOIN(delimiter; value1; value2; …)

This function connects texts/values from different cells and separates them using a delimiter; it’s called the CONCAT function. To put it more simply, you can connect texts from two columns, let’s say Service and City within one cell and delimit them using a ’space’ We can separate them using some punctuation marks, text, or digits as well. Take your pick!

What do we need it in SEO for?

• making lists of potential keywords, e.g. by connecting City and Location values

• creating meta titles and meta descriptions, e.g. by connecting Category Name and City or adding a ’call to action’ at the end;

• creating URL addresses.

Tip: You don’t need to use any delimiters – just make sure you leave the inverted commas empty in the Delimiter box.

Tip 2: “What the formula has joined together, let the formula break it apart”, as and old Excel saying goes SPLIT function is the reverse function of JOIN and allows you to delimit any text within a single cell, with the use of a certain delimiter.

=SPLIT(text; delimiter)

LEN function

=LEN(text)

A short but powerful function! It’ll help you calculate quickly how many characters with spaces there are in a certain cell. It’s a known fact that an accurate calculation of characters with spaces is essential!

What do we need it in SEO for?

• creating meta titles and descriptions to increase their chance of being displayed in full in search results – surely, it’s also up to ’pixels’ and a variable called “Google”. However, the use of the LEN function allows you to assess on an ongoing basis how many characters you can still use. Combining this formula with conditional formatting will take you to the next level!

Conditional formatting

This time it’s not a function; yet, it can include formulas. Conditional formatting allows you to highlight those cells that meet certain numerical, date range or text criteria. You need an example? We kindly request from Excel that the sheet should mark in red all key phrases from our list that contain the phrase “Warsaw”. In green, however, will be marked those cells that contain the phrase “Cracow”. Conditional formatting allows you to add some colour to your worksheets, but a colour that makes the most sense, emphasising and grouping the most important values.

What do we need it in SEO for?

• making the process of meta creation more seamless when this function is joined together with the LEN function; by applying suitable conditions, a Google Sheet marks in red a title which has exceeded the maximum character limit already at the stage of creating meta tags;

• fast identification and highlighting of the low hanging fruit keywords in the report that occupy positions 4-10, which we want to focus on in our strategy. We can also additionally highlight those that include specific phrases. In the end, digits are digits and Excel cells can start blurring at some stage of data analysis Conditional formatting and assigning colour to particular groups of values makes our life significantly easier;

• Eliminating duplicated keywords, H1 heading or meta tags.

TIP: In Google Sheets, the group of formatting rules gets defined right at the start, but we don’t have to limit ourselves to them. By selecting the “Custom formula” option, we are able to apply more complex formatting rules. It’s a bit like a hidden level in an obsolete computer game. One such additional option is the ability to identify cells that contain the same values. All you have to do is use the COUNTIF function in the custom formula field and indicate the area that it should check for duplicates.

VLOOKUP function

=VLOOKUP(lookup value; range, column number; [sort])

The Queen of all functions It allows Excel to search for a certain value in a different column/tab to return a value from a different column in a selected cell… Sound complicated? Let me show you an example.

Your content plan has got a URL, H1 heading, and a key phrase. The report you have extracted from Ahrefs into a separate tab contains, i.a. a key phrase, CPC rate, your current position, or number of searches for a certain phrase. The binding element for both reports is … the key phrase. This single common point is enough for your function to do what it was created for. At this stage, you add the column with your value to the content plan. The function will track the phrase you have marked in the Ahrefs report and return a current value. Next month, the only thing you have to do will be to update data in the Ahrefs tab and the function will do the rest

What do we need it in SEO for?

• combining reports from different sources, e.g. GSC, Ahrefs, and crawler, assuming the common point is the URL;

• updating reports with current values;

• analysing our competition by listing our competitors positions for our key phrases.

Bear in mind, it’s only a few possible options. Sky is the limit

And what IFERROR?

=IFERROR(value; value_if_error)

Reports should be clean of all #N/A!s ! For UX sake! These are virtual documents we’re talking about here, and UX is the new black, ladies and gentlemen … in the reports as well The IFERROR function returns a user-friendly text when a certain formula does not return what we expected. For example, “No data found”, “No item found”, “Something went wrong”.

What do we need it in SEO for?

• Reports should be clean of #N/A!s !

Summary

We got it! Top 5 Google Sheet functions! Please remember that the ways you can use the functions I’ve listed above are only a few for starters. Rack your brains for more, apply and combine certain formulas to obtain a fully customised solution If you’re not 100% convinced  that Google Sheets are that useful, then I’m on it like a car bonnet Another five functions coming up! We’re starting this year with a bang Stay tuned!