Eight Useful Excel Functions for SEO

36 comments

It’s probably not a secret anymore that I love Microsoft Excel for data crunching. Nothing makes me happier than getting a whole mess of data and making sense of it. While I’m definitely not an Excel ninja on the level of Mike Pantoliano, I’ve learned a number of tricks in the past couple of years to help out a lot with my work. Whether it’s keyword research, building a sitemap, or cleaning up URLs and finding duplicates in rows, here are eight of my favorite Excel functions that make my life easier every day.

For all of these, the data is in cell A1, unless otherwise noted.

Stick these in your list of functions!

Proper

This is a really simple function that makes first letter of each word uppercase. I’ve found it to be extremely useful when writing page titles and keeping everything uniform:

=proper(A1)

So this:

becomes this:

Concatenate

Concatenate is another one of those simple queries that can be used for many different purposes. I use it frequently for the following:

    • Create linkbuilding queries
    • Create keyword lists
    • Create sitemaps (!)

It’s pretty simple. Here’s one example. Say you have a list of URLs that you need to create ‘site:’ queries for to check indexation of different parts of a website.

Put site: in A1 and your list of URLs in column B:

Note: if you want to keep pulling from the same cell in one column even as you are working your way down another column, use the $ symbol. In the above example, site: is in A1, and I want to keep using the cell, so the cell in my formula becomes $A$1.

Here’s how the sheet looks after I drag down:

Vlookup

Vlookup is one of my favorite functions, partly because it took me forever to figure out and partly because it’s so awesome. There are 4 parts to this function:

      • lookup_value – the cell on the sheet you are on that you want to look up within a range on (usually) another sheet.
      • table_array – the range that you are finding the value in on the other sheet.
      • col_index_num – the number of columns to the right of the column that your matched data is in.
      • [range_lookup] – TRUE or FALSE. Select FALSE if you want to see data.

Basically, a VLOOKUP helps you to match the value in a cell to a value on another sheet, then pull out data from the range of data on that other sheet. Let’s look at an example:

Let’s say I’ve got these values on Sheet4:

And these on Sheet5:

I want to get the values for visits 2011 (column D on Sheet5) into the 3rd column of Sheet4.

I write this function:

=VLOOKUP(B2,Sheet5!A2:E9,4,FALSE)

and put it in column C of Sheet4. The number “4” in the third variable above tells the function to pull the value from the 4th row of the range in Sheet5, when the value in the first column of Sheet5 equals the value in B2.

So we end up with this:

Clean Domain Names

Another common issue that SEOs run into when cleaning data is finding the unique domain name for link prospecting. Especially with sitewide links, tools like OSE and Majestic will pick up multiple pages with the same link. While this is value information to have, as it gives insight into your competitor’s link profile, you also probably want just the domain name.

It’s a simple as putting the below formula into Column B, when the long URL is in cell A1:

=left(A1,find(“/”,A1,8))

Like so:

Everything to the right of a domain

Conversely, sometimes you need to have just the slug (aka everything after the domain name). This little formula does the trick:

=MID(A1,FIND(“/”,A1,16),100)

Like so:

Get just the domain from an email address

Ever need to get just a list of domain names from email addresses? This one does the trick. Once again, the email address is in cell A1, so put this formula to the right of it somewhere. Drag down to get all the domain names in a list:

=MID(A1,FIND(“@”,A1,1)+1,100)

Find duplicates

When cleaning data, we often run into duplicate values. When you’re trying to get a clean list of domains for prospecting, we need to eliminate values. Also, if you have a team of linkbuilders working together and are using Excel or Google docs, you may find the below useful so that people are not putting in the same domains and thus wasting time.

Find if the value is in a column

This formula is a bit tricky to explain. Basically what it does is see if a value (in this case, the value is in J4) is already in a list (in this case, Column A). If so, it is marked as “Dup”. If it is not, it is marked as “Unique”.

=IF(COUNTIF(A:A,J4)=1,”dup”,”unique”)

Find if the cell above is a duplicate

This formula tells you if the value above the cell is the same. In the below case, we are seeing if A3 is equal to A2. If so, we are marking it as “dup”. If not, we are marking it as “Unique”.

=if(A3=A2,”dup”,”unique”)

What are your favorite Excel formulas that you use frequently? Leave them in the comments!

36 thoughts on “Eight Useful Excel Functions for SEO”

  1. {=INDEX(E:E,MATCH(1,(H2=A:A)*(I2=B:B)*(J2=C:C)*(K2=D:D),0))}

    Outputs E, when it finds H2 in column A, I2 in column B, J2 in column C, etc. You can just keep adding criteria.
    Very easy way of finding very specific rows in massive amounts of data. Fun one to play with.

  2. Nice round-up, John!

    I’d throw the closely related cousin of countif, sumifs into the ring. Great for pulling info from a raw list of keyword data into a neater format (especially useful when using to power dashboards and the like!)

  4. Thanks for the writeup, John.

    These are all super useful. A good deal of them (especially the Find Duplicates for link prospecting) I’ve been trying to figure out.

    Another one that is super basic (but very helpful) that I like to use is the =LEN(cell) function – really helps for character counts when building meta descriptions, title tags, or AdWords ads. Or, just generally anything that has a set character ceiling.

    Pretty sure this will save me a TON of time. Thanks again, John!

    – Scott

  5. Great post John, it’s good to see simple posts to help people speed up in excel as it can help us all spend longer on the important work rather than faffing with the data. I wasn’t aware of proper so that will really help me.

    Concatenate can sometimes confuse people as it sounds confusing, so I’d also recommend using & to combine information, ie. =a2&a3&”text here”&a5

    The function for finding duplicates is also very useful, but I prefer using conditional formatting to highlight cells red if they exactly match the contents of the cell above (once sorted alphabetically), that way you don’t need to add extra rows and you can clearly see where you need to take action.

    And as Scott said, =len() is very useful!

    So many things in excel that are so useful to us! 🙂

    2. Hi Anna,
      I’m doing SEO for my site, and have just pulled from awstats a bunch of keyword phrases from different months. I’ve placed all the phrases on one excel sheet, and would now like to highlight every occurrence of one particular word. Do you know of a way to do so?

  10. Pingback: Recap / Review Liste & Übersicht SEO CAMPIXX 2012

  11. Good post John.

    My fave is SUMPRODUCT. So useful for pulling all sorts of information from large data sets. Took me about 2 weeks to figure out how it worked, but when the penny finally dropped, I was a happy man 🙂

    The ‘everything to the right of the domain’ example is new to me – I’ve been doing it with really long formulas containing ‘REPLACE’ and ‘FIND’. Yours is much simpler, thanks!

  12. Nice helpful post, but some of the formulas are not totally robust.

    For example,
    Everything to the right of the domain– you have 16 as the starting point for the find which is fine if the string is formatted as http://www….etc but if the string starts out as http://abc.com (no www) then your formula will return an error. You could nest some ifs/finds/if(iserrors) for this.

    You also arbitrarily choose 100 characters to extract when the string could be much longer and the anything beyond 100 would be cut off. A better solution would be to find the len() of the entire string and then subtract out the the position where you find the slash+1 (the position before which contains the part of the string you don’t want). The formula would then it would work in all cases

    The basic rule of thumb is if you have constant numbers in your string formula, there may be a chance that the formula will return an error for exceptional cases. Better to substitute those numbers with additional formulas so the thing will work in ALL cases

    1. Thanks for the comment, dude, but when will the domain be longer than 100 characters? I’ve never seen one.

      Great point on the first example you give. Duly noted.

  14. Using CONCATENATE to check site indexation? That’s an amazing idea. Thanks John.

    And to make this more scalable, we can add Niels Bosma SEO tools fx “Google Results Count.” We never have to leave excel, except to check out a few outliers.

  15. Nice post, very helpful!
    I will add another, this formula is for counting words in a cell =IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),” “,””))+1) where A1 is the cell with the text or keywords.
    Hope this helps 🙂

  18. Good post. I study one thing tougher on completely different blogs everyday. It will always be stimulating to read content material from different writers and observe a bit of one thing from their store. I’d prefer to make use of some with the content on my blog whether or not you don’t mind. Natually I’ll provide you with a link on your web blog. Thanks for sharing.

  19. Thanks a LOT John, just like everything you write very VERY valuable.

    I recently had to jump back to a project where I needed to cleanup some URLs. I couldn’t remember the exact syntax off the top of my stupid head, and I knew you wrote something about this a while back. So I do a quick google search and blammo theres your rich snippet right there. Awesome exactly what I needed.

    I noticed two issues with the “Clean Domain” formula. One was if the string starts with https:// then you need to pad it by 9 instead of 8 otherwise it results in “https://” in the cell. And if the string doesnt end in a / your formula gives and error. I just added a / at the end by doing this =LEFT(A2&”/”,FIND(“/”,A2&”/”,9)) and it works like a champ.

    Thanks again, cant wait for the eBook 😉

  20. Pingback: Quick and Dirty Citation Finder for Local Business « Stoked SEO & Internet Marketing

  22. I want to throw in an Excel 101 tip myself. SUPER Basic, but so useful.

    =Len(A1)

    I use this all the time when writing ‘s and Meta Descriptions in Excel, handy to see the length right there. I also use conditional formatting to shade the cell red if I exceed my max desired length, makes it faster and easier for generating this stuff, and ensuring no errors.

  23. Hmm, last comment should say title’s and meta descriptions. not sure what happened there. If you could fix it and delete this comment that would be cool.

  24. When I was young, I’d listen to the radio, waiting for my favorite songs.These shoes don’t fit right.The price just covers the cost.I’ll have to ask my bosswife firstHe lacks courage.There being no one to help me, I had to do it all alone.There being no one to help me, I had to do it all alone.Brevity is the soul of wit.Not bad.Is there any sugar in the bottle?

  25. Pingback: SEO Excel Function for Clean Domains | Beetleweb Blog

  26. Pingback: Quick and Dirty Citation Finder for Local Business

  28. Pingback: [Tip] Cool Excel Add-Ons for SEO

Comments are closed.