Excel functions for SEO
On this article we will comment some Excel functions used to perform SEO analyses.
Excel is a powerful tool that we can use to analyse data and process it, but it can also be used to save time on repetitive tasks.
Rather than telling how the function works (which is explained in Excel help or one of the many online tutorials) what we want to do here is show the practical uses for each function and how we can make the best of them.
This (very simple) Excel provides an example of all the functions that we talk about in this article.
vlookup()
This is one of the most used functions. Its main purpose is to find values on other spreadsheets (or even other Excel workbooks) to cross data. To do this, the data from these sources have to have a column in common, which on SEO it is normally a URL (or a keyword). This column has to be on the first place.
Let’s imagine that we have an Excel with three different spreadsheets with databases, some with the average URL ranking (obtained with GSC), another with the data about visits (obtained with Google Analytics) and another one with the metatags, internal links, etc., (obtained with Screaming Frog).
With the function vlookup() we can generate a new summary sheet with all the URLs, adding on the different columns the data about ranking, visits and, for example, title (or h1) to identify it more easily.
The different sources of data can have a different amount of URLs. On the summary tab, what we have to do is add them all up and extract duplicates. If some data does not exist, an error will appear. We can fix this using the following function: iferror().
iferror()
To avoid the infamous #N/A and similar errors, we can use this function to replace the error code by another value (this can be a number or a chain).
When using the function vlookup() we may find that when we cross some data, some values do not exist, appearing instead the error #N/A.
In this case what we cannot do is give an alternative value as 0 if we are talking about visits, or “ranks not” (or 99) if we are dealing with a URL or keyword, or “non defined” if it is a metatag.
nested if()
This is a very simple function, but it has an advanced use which is to nest several if(), what can help us when classifying data.
Let’s imagine that we want to generate some text indicating on which position is a word located. For example:
Top 3, for those words located in a position lower than 4.
Top 4 to 10, for words in a position lower than 11.
No good ranking, for the rest of words.
Note that when the set requirement is not met (highlighted in green), instead of providing a value like in the case of if(), what it does is include another condition (highlighted in yellow).
=IF(C2<4;”Top 3″;IF(C2<11;”Top 4 to 10″;”No good ranking”))
search()
This formula is for finding text or chain inside another. Its use is simple, but we can refine it using iferror() and if() to boost it.
The function search() gives an error #N/A if it cannot find the chain or a number indicating the position in which the chain is. If we add to this formula the iferror() with the value “0” for when an error is displayed, we will have two possible values: values >0 mean that the chain has been found, values =0 mean that the chain does not exist. If we then use if() so that it displays “brand” or “generic” depending on the value, we will have all the words sorted in brand and no brand.
For example, if our brand were named “risfanilla”, the formula would be:
=IF(IFERROR(FIND(“risfa”;B2);0)=0;”no”;”yes”)
We do not have to put the full name of the brand. We write a chain that we know is not going to classify as a generic word, and then in this example it would count both “risfanilla” and “risfaniya” as a brand name, foreseeing a possibly common spelling mistake.
To give it even more power we can chain up several iferror(find();0) with the different variations for the brand and add them up. If the brand does not exist it will keep delivering a 0.
sumifs() or countifs()
Although it is more normal to see sumif() and countif(), the functions sumifs() or countifs() allow to add up or count values meeting certain criteria.
This is useful to make data aggregations, for example:
how many words with more than 1,000 monthly searchers are at the Top 3?
how much traffic generate words of less than 100 monthly searches which are at the Top 3?
or even:
how many words not pertaining to the brand with more than 1,000 searches are on the first page?
Remember that on this Excel spreadsheet (which is very basic and has very few rows) you can see the practical application of these formulas.
There are of course many more formulas used in SEO. These were just some examples we can use. For more, the sky is the limit.
Internet República
Latest posts by Internet República (see all)
- New Instagram update: reel achievements - 19 October, 2023
- Elon Musk has bought Twitter. What does this mean? - 27 April, 2022
- NFTs ARE ARRIVING ON SOCIAL MEDIA - 21 February, 2022
