Excel For SEO: Bring Out Your Inner Spreadsheet Nerd!

Whenever somebody asks me what my favorite SEO tool is, Microsoft Excel is usually the answer they least expect. Sure, it’s not technically an SEO tool the same way that Moz, aHREFs, or Screaming Frog are, but Excel is the one tool that I truly use every single day. It’s not just to view data exports from Google Analytics or Screaming Frog either; Excel has functions and formulas to automate boring, repetitive tasks and get things done faster.

A lot of you may have a love-hate relationship with Excel (or even a hate-hate relationship), but I’m hoping to change your mind. In this post, I want to give a quick tutorial on the functions that I use the most and show how you can combine them to do some really cool things.

Excel Functions to Know for SEO

First I need to explain a few important Excel functions that I will later bring together in a larger, SEO-relevant formula. Bear with me. I will bring all the pieces below together.

=Len(string)

The Len formula (short for length), gives you exactly that: the length of a given text string in the number of characters. Chances are you already know this formula because it’s an easy way to check if a title tag or meta description is over the character limit in Google. However, the LEN formula is great to set up other functions and formulas, which we’ll discuss later in this article.

Left and Right

=LEFT(string,[number of characters])

=RIGHT(string, [number of characters])

The LEFT and RIGHT formula, similar to LEN, is a great function that helps set up more advanced formulas. This function retrieves a specified number of characters from the left or from the right. For example, let’s look at the following formula:

This formula retrieves 6 characters from the left of the string “Scones are amazing.” The result of the formula will be “Scones.” On the flip side, RIGHT will do the same thing but will take a specified number of characters starting from the right side of the string:

This formula is saying that it will retrieve 8 characters from the right side of the string. The result of this formula will be “potatoes.”

Find and Search

FIND and SEARCH functions literally find and search for a specified character or string in another string. The formula outputs the number of characters that mark where the character or string starts. Here’s an example of the two:

For both formulas, you’re telling Excel to find the position of the letter “a” in the string “Scones are amazing” starting from the left side. The output of this formula is 8 because the first “a” is located 8 characters in from the left side. One thing to note is that Excel counts the positions in front of the character. So that the first “S” in the string is at position 1.

So you may be asking, what’s the difference between find and search? Well, FIND is case-sensitive whereas SEARCH is not case sensitive. So rewriting the example above, if you have the formula =find(“A”,”Scones are amazing”), the formula would error out because the capital “A” is not in the string. But if you did =search(“A”,”Scones are amazing”), it would return 8 because it does not distinguish between the upper and lower case version of the letter “a.”

Substitute

This formula tells Google to take a specific character or string from a different string and substitute it for another character/string. Let’s look at the following formula:

The formula is telling Excel to substitute the word “amazing” with “delicious” in the string “Scones are amazing.” You can also tell Excel to replace a specific instance of the character or string. See below:

Notice the “2” at the end of the formula. Here, the formula is telling Excel to substitute the second instance of the letter “a” with “@” in the string “Scones are amazing.”

What can you use this for? For one, you can use this formula with LEN to count the number of words there are in a string. To do this, you would use the following formula:

Stay with me! I know it looks complicated. This is what the formula says: Take the length of “potatoes recipes” and subtract it with the length of “potatoes recipes” without the spaces and add 1. What this basically does is it takes the difference in length between the original string and the string without spaces. You add one to the formula because we want the number of words (because a single space separates two words).  

Still confused? Here’s a play-by-play breakdown:

  • LEN(A2) – Get the length of “potatoes recipes”
  • SUBSTITUTE(A2,” “,””) – Substitute all of the spaces “ “ with no spaces “” in the string “potatoes recipes.” This will give you “potatoesrecipes.”
  • LEN(SUBSTITUTE(A2,” “,””) – Give me the length of “potatoesrecipes.”
  • +1 – Because I don’t want to just count the number of spaces, I want to count the number of words in the string (i.e. one space separates two words, two spaces separate three words, etc)

 

Let’s try putting it all together!

You can only do so much with above mentioned formulas, but when combined you can do some really cool things. Say, for example, you have a list of URLs and you want to remove everything but the domain name. Of course, it would be easy just to manually pull out the domain names for a handful of URLs, but what would you do if you had 2,000 URLs? It’s a huge waste of time to go through each URL one-by-one and copy just the domain name. Instead let’s figure out how to do it easier in Excel. Let’s first examine the following URLs:

https://www.siteA.com/product/abc

http://www.websiteB.org/solutions/def

Because we’re trying to extract just the domain name, we know that everything before the third instance of “/” is what we want and that we want to get rid of everything to the right. So, using the formulas we went over earlier, we have to figure out a way to just get everything to the left of that third slash. We’ll need to use a combination of FIND, SUBSTITUTE, LEN, and LEFT to do this. First, let’s find the location of the third slash. We’ll use the SUBSTITUTE function first:

As you can see, I used the SUBSTITUTE formula to replace the third slash with an asterisk. Why did I do this? Well, both the SEARCH and FIND function only finds the first occurrence of a character. However, with SUBSTITUTE, I can specify which instance of a character to change. So, by changing the third slash to a unique character (I chose an asterisk, it can be any unique character), the FIND or SEARCH function will return the position where the third slash originally was. Now I can use the FIND feature to find the asterisk:

As you can see, I used the SUBSTITUTE formula to replace the third slash with an asterisk. Why did I do this? Well, both the SEARCH and FIND function only finds the first occurrence of a character. However, with SUBSTITUTE, I can specify which instance of a character to change. So, by changing the third slash to a unique character (I chose an asterisk, it can be any unique character), the FIND or SEARCH function will return the position where the third slash originally was. Now I can use the FIND feature to find the asterisk:

Here, this formula tells Excel, “get the first 22 characters from the left side of the string in A1.” And that’s it! You now have a formula to retrieve the domain name from any URL.

Conclusion

This article went through a lot but we’ve only scratched the surface on all the great things Excel can do. Don’t worry if all of this is still confusing. It takes time and practice to fully use Excel and all its functions. Play around with the formulas and over time you’ll be able to own them. In closing, I’d like to leave you with these points:

  • If it’s highly manual and repetitive, you can most likely do it easier in Excel
  • If you’re not sure how to do something, even if it’s really specific, search for it in Google/Bing, because likely somebody asked for it before
  • Once you’re good at Excel, you can do anything in it – sky’s the limit!

 

Good luck and happy Excelling!

 

No Comments

Post A Comment