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!