SEO

10 Excel Functions Every SEO Professional Should Know

Many website owners use Excel on a daily basis. They might keep a list of backlinks they want to track, or use it to track keyword rankings. True SEO professionals know how to use Excel to maximize the value of their SEO strategy, manipulating the data in ways that shed new insights into their campaign. This article will outline my favorite tricks for doing so. A couple years ago, Ann Smarty wrote an excellent piece on using Excel for SEO, but I figured it was time for a refresher based on the many changes the industry has gone through since then.

First, we’ll start with some of the basics and then move to more advanced topics. Some of these can be used together to form more powerful commands.

SEARCH and FIND

These simple functions allow you to find the location of text inside of a string of text. These functions are identical, except find is case-sensitive and search is not. Search also supports wildcards. Search and find return the position of the first character in the string.

Syntax:

=SEARCH(string_to_find, string_of_text, [start_num])

=FIND(string_to_find, string_of_text, [start_num])

Search can be very helpful when you want to sort by keywords in a long list of domains. Domains that don’t contain the keyword for which you’re searching will return “#VALUE” whereas domains that do contain the keyword will return a number. An example might be:

=SEARCH(“dog”,A1)

search and find 10 Excel Functions Every SEO Professional Should Know

In this example, we’re searching for the keyword “dog” in a URL. This function will return the number 12 because the word “dog” begins at the 12th character in the URL string.

LEFT, MID, and RIGHT

LEFT returns the leftmost characters of a string.

Example:  =LEFT(A1,7) will return the leftmost seven characters in the string from cell A1.

left 10 Excel Functions Every SEO Professional Should Know

MID returns the middle characters of the string.

Example: =MID(A1,12,3) will return the characters in the string starting at position 12.

mid 10 Excel Functions Every SEO Professional Should Know

RIGHT works in a similar manner, but returns the right-most characters of the string. This can be useful for separating domain names from a domain list that contains “http://www.” before every domain.

Example: =RIGHT(A1,14) were returned the right-most 14 characters from the string.

right 10 Excel Functions Every SEO Professional Should Know

These functions can be useful for pulling specified keywords, domain names, LSI data, and other information out of a string. One good example would be to copy and paste a long list of backlinks into Excel and then create a new function that searches for a specific keyword. You can then copy and paste the function down the entire column for each URL to find out how many sites use the specific keyword.

CONCATENATE

Concatenate will allow you to combine strings. This can be useful for combining pieces of a URL into one address. It can also be used to build link building queries, site maps, and keyword lists.

Example: =CONCATENATE(A1,B1) will concatenate the cells A1 and B1.

concatenate 10 Excel Functions Every SEO Professional Should Know

RAND

The RAND function creates a random number, which is a great way to randomly sort lists of information. Randomization is one important element of naturalness, which is the key to a successful link building campaign.

Example: placing =RAND() in every row within a column will generate a random number.

rand 10 Excel Functions Every SEO Professional Should Know

Once you have created your list of random numbers, you will then need to copy the list, and paste the values into a new column on the spreadsheet using “paste as values” (see image below). “Paste as values” is required because the random numbers will be generated each time the field is sorted. By pasting the numbers as values, you ensure the numbers won’t change.

Below is an example of five URLs randomly sorted by number.

random sorted 10 Excel Functions Every SEO Professional Should Know

Randomly sorting lists of URLs/domains can be useful for split-testing various outreach templates while minimizing any biases that could be caused by non-randomization of the list.

OFFSET

Here’s a great video tutorial of how to use the “OFFSET” function. According to Microsoft, offset “returns a reference to a range that is a given number of rows and columns from the given reference.” One example of this function’s usefulness in SEO is analyzing the ranges from a Google Analytics CSV export.

Syntax: OFFSET(reference,rows,columns,[height],[width])

To continue with our dog food example, we start with exported data from Google Analytics (see below):

offset 10 Excel Functions Every SEO Professional Should Know

For this example, we have referring keyword traffic from one week compared to the previous week. If we were simply looking to compare a few weeks’ worth of data, it wouldn’t be a problem. However, when you look at data like this for thousands of keywords, it can be difficult to manage.

Using the OFFSET command, we can turn this list of keywords and date ranges into the following data:

offset example 10 Excel Functions Every SEO Professional Should Know

Getting the first row of data is simple. Afterward, we can use the OFFSET command to grab the keyword, week 1 and week 2 data using the following formulas:

offset more examples 637x108 10 Excel Functions Every SEO Professional Should Know

Note the ROW command returns the number of the cell where it’s located. This allows us to move to different rows to grab data.

INDEX and MATCH

While many Excel users like VLOOKUP, it’s limited in that your look up must be in the left-most column to lookup data. The INDEX command doesn’t have this limitation and will return a value from a specified row and column within a given range.

Syntax: INDEX (array,row_number,[column_number])

For example, using the formula  =INDEX(A1:A5,3), we’re able to retrieve the text from the third row of the array in column A.

index match 10 Excel Functions Every SEO Professional Should Know

MATCH adds to the power of INDEX. Unlike INDEX, which returns a word, the MATCH function returns a number.

Syntax: MATCH (value, array, [match_type])

  • Value is the letter or phrase that you wish to search for.
  • Array is the array of data that you wish to search.
  • Match_type specifies the type of match.

Match_type options include:

  • “1” Less than – This is the default and will find a match less than or equal to the lookup value assuming that the list is in ascending order.
  • “-1”  Greater than – finds the smallest value that is greater than or equal to the lookup value assuming that the list is in descending order.
  • “0” will return the first value that it finds that is equal to your lookup value. This is the recommended option for SEO purposes.

Using our pet food keyword example above, the following formula will return the location (row) in the array of the phrase “guinea pig food”.

=MATCH(“guinea pig food”,A1:A5,0)

match guinea pig 10 Excel Functions Every SEO Professional Should Know

In this case, it returned the number 4 since “guinea pig food” is the fourth item in the list.

Combing INDEX and MATCH

You can combine INDEX and MATCH for more powerful purposes. Combine them using the following method:

=INDEX(array, MATCH formula)

Let’s take, for example, our keyword list about dog food in our imaginary dogfoodrus.com website. In the table below, we have different keywords that are bringing in organic traffic for specific landing page URLs. Each row contains the number of visits and conversions associated with that keyword.

combine index match 637x199 10 Excel Functions Every SEO Professional Should Know

Using the INDEX and MATCH commands together, you can create a keyword lookup that will give you the number of visitors and conversions for a specific keyword. Now, if you only have four or five keywords, as in this example, looking them up manually isn’t a big deal. However, if you downloaded analytics or Google Webmaster Tools data with thousands of keywords, this can be an easy way to look up specific keywords and build a specific list.

=INDEX($B$3:$E$7,MATCH(B11,$B$3:$B$7,0),3)                 – number of visits

=INDEX($B$3:$E$7,MATCH(B11,$B$3:$B$7,0),4)                 – number of conversions

In this example, the INDEX has a range of B3 to E7 which encompasses all of the imported data. It uses the MATCH command to specify the row within the array where the match is found. The 3 and 4 at the end of the formulas are the columns where the data you wish to copy is found.

There are many uses for INDEX and MATCH together. It can be an easy way to look up information in a huge table. Unlike VLOOKUP, INDEX and MATCH does not require the lookup field be in the leftmost column of the array.

Conclusion

These are some of the common Excel functions that I find useful when working on SEO campaigns. Excel can be used for many SEO jobs including managing backlinks, tracking keywords, visits, conversions, and much more. Do you know of any other useful Excel tips for SEO? Leave a comment and let me know!

 10 Excel Functions Every SEO Professional Should Know
Jayson DeMers is the founder & CEO of AudienceBloom, a Seattle-based content marketing & social media agency. You can contact him on LinkedIn, Google+, or Twitter.
 10 Excel Functions Every SEO Professional Should Know

You Might Also Like

Comments are closed.

16 thoughts on “10 Excel Functions Every SEO Professional Should Know

  1. Thanks! Trying to learn as many new functions as possible as going to need to know them next month when start new job in data entry………BORING! But at least I only have to do it for 3-months.

    Keep up the great blog!

  2. Hey Jason,

    Thanks for the great “how to” article. Excel is so powerful and large that it is easy to feel at a loss as to where you should start or to just stick with what you are comfortable with instead of branching out (vlookup instead of index and match).

    At the risk of being told that I have made a simple task very hard and revealing myself to be an Excel NOOB, I will share something that I have found helpful.

    I quite often have to take several lists and need to filter for unique values between the lists. If you simply want to deduplicate a list than excel has a built in function, but to find the unique records I use a table combined with this formula =IF(COUNTIF([name], [name])>1, “duplicate”, “unique”); (where name is the heading of the column of data in the table that I am checking against) .

    If you have your data in a table then you can simply use the built in filters to grab all of the “unique” records. I have used it to quickly compare records from a sales database and analytics to find products that are listed that have never been sold, or to find a list of unique keywords from analytics and keyword data from Moz or Adwords.

    Thanks again for sharing.

  3. Thanks Jayson, Great tips for SEO’s.

    I have to start using OFFSET function. Every week i manually do the stuff. OFFSET function will reduce my efforts.

  4. The most powerful data manipulation tool I have used in my day-job is Regular Expression; you can do most of what’s outlined above in a text editor and a RegEx – probably do it quicker too. It’s awesomely powerful stuff.

  5. I thought I was pretty clued up on Excel but that’s a great tutorial about using the Index and Match functions. Taught me some new stuff I can use straight away, many thanks!

  6. The Excel functions is what makes it lethal and you can almost convert it to work for you any way you want it to. Nice and really helpful article jason. Thank you.

  7. Hi Jayson,

    This is such a great post! Thank you very much for sharing this. The video tutorial is great! I have learned something new today.

    Regards,
    Charles

  8. Hi,

    I am using Open Office Excel – Which is not familiar as MS Office Excel. It would be great if you suggest something for Open Office Too…

  9. Hi Jayson , Very helpful and useful post !! I had used Search, find, left, right,mid and concatenate commands before. The rest of the commands are new to me. I will share this post. Thanks for sharing Jayson !!

  10. Hey, Jayson DeMers .. Thnx Man, Have never known this function.. Actually have never known that excel could be used for SEO purpose …

  11. Bookmarking this post right now, I am also a big fan of Excel for SEO.

    I use this formula as a Keyword Grouper:

    =IF(ISNUMBER(FIND(“*KEYWORD*”,A1)),”Yes”, “No”)

    This way I can sort all my keywords into different groups and also combine them by simply filtering YES or NO.

    Thanks.

  12. Thank you Jayson, for sharing such a informative and inspiring post, it is definitely going to make the job of a SEO easy, specially for those who are not that much familiar with using excel sheet in SEO jobs. it also inspire the beginner’s in this profession, who want to explore the things and make the task easy using some innovative and technical way.i would like to add one formula as i think it quite relevant to this post. it is for checking a duplicate text in a particular column
    =COUNTIF(Range,Criteria)
    eg. =COUNTIF(A2:A235,A2)