Blind Five Year Old

SEO and Excel are like chocolate and peanut butter – great together. Here are 18 Excel functions (and two formulas) that can make SEO just a little bit easier.

SEO Excel Functions

LEN

The LEN function returns the number of characters in a cell. It’s particularly handy in creating the right title and meta descriptions. Remember to keep your titles to ~60 characters and meta descriptions to ~150 characters.

TRIM

Phantom spaces at the beginning or end of a cell can be maddening. So before you go Office Space on your keyboard use the TRIM function which gets rid of any spaces before or after text. It’ll also get rid of any extra spaces between words. Think of TRIM as a vacuum cleaner for spaces.

SUBSTITUTE

This is just what it sounds like. Using the SUBSTITUTE function you can find specific text and substitute it for different text. It doesn’t sound all that interesting but it turns out to be vital in creating useful formulas.

Word Count Formula

There’s no out of the box word count function. But with a little creativity you can create a useful word count formula using the three functions above.

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

The first part of the formula returns the number of characters in the keyword phrase. We’re using TRIM to ensure extra spaces aren’t included in the LEN calculation.

The second part of the formula returns the number of characters in the keyword phrase without spaces. We’re using SUBSTITUTE to remove the spaces.

The difference between the two numbers tells you the total number of spaces in the keyword phrase. That’s where the +1 comes in. If there are three spaces, that means you actually have four words. Confused? Here’s an example.

This is three spaces

There’s a space between This and is, between is and three and between three and spaces. The number of words will always be one more than the number of spaces.

INT

The INT function is excellent if you’re downloading the new Google Webmaster Tools Top Queries report. You might see that your average position is sometimes not a whole number. This level of detail can be useful, but sometimes you want to aggregate. That’s where INT comes in. The INT function will return just the integer from that number.

You can actually do this in a number of ways including ROUND, ROUNDDOWN and ROUNDUP. If you use any of the ROUND functions you’ll simply specific that the number of digits you want to round to is 0. Here’s a look at how each one behaves in a real world example.

Once you have your whole number rank you can use a number of methods to analyze it, my favorite being a pivot table.

LOWER

The LOWER function turns all the text in a cell into lowercase letters. This appeals to my symmetrical obsession when working with text. It can also be handy in dealing with a lot of malformed keywords, particularly if they’re coming from internal searches on a site.

PROPER

This function has more application to the PPC world but deserves a mention here. The PROPER function capitalizes every word in a cell. You might want to use this when creating titles. Warning! PROPER treats apostrophes as a space. So contractions will look strange. Don’t will turn into Don’T. You’ll need to run some standard find and replace scripts to weed out these errors.

CONCATENATE

This powerful function lets you combine text from several cells. I used the CONCATENATE function to create my phrase match and exact match generator. CONCATENATE can also help you create a meta description template, which can be useful for long-tail sites or product pages.

TEXT

How does your traffic look on Tuesday versus Saturday? The first step in figuring this out is transforming your dates into days. The TEXT function does that and more. You can transform dates into days, months or years.

IF

The IF function is a simple logic test. If the result of that test is true, you do something. If the result of that test is false, you do something else. This function can be helpful if you’re creating titles or meta descriptions with keywords.

The problem? Long keywords can wreak havoc on character length. The solution? Determine the maximum character length that will fit into your template. Use a LEN function to count the character length of each keyword. Then use IF to insert only those keywords that meet your template criteria. Of course, you’ll need an alternate term (or perhaps completely different text) for those keywords that are too long.

SUMIF

If you’re doing any type of research or analysis you’ll likely need the SUMIF function. An example would be if you wanted to know the traffic volume of keywords which contained a certain word, handy if you’re looking for root keyword modifiers.

COUNTIF

Sometimes you don’t want to add things, you want to count things. If that’s the case, use COUNTIF instead.

SUMIFS

Back in the day you had to combine an IF function with a SUMIF function when you wanted to sum something based on two conditions. Yeah, it’s about the equivalent of walking to school and back, uphill, both ways, in the snow. Today you can use the SUMIFS function.

The SUMIF, COUNTIF and SUMIFS functions are often used on an ad hoc basis. When it’s time to construct reports or do thorough analysis you’ll likely be using LOOKUP functions or pivot tables.

WEEKNUM

When you have a data set that spans a number of months you might want to look at it by week. The WEEKNUM function returns a numeric week of the year based on a date. So, daily data can be quickly aggregated and analyzed by week. Of course, it’s easier to pull data on a weekly basis. But if you ever find yourself with a daily data set, use WEEKNUM and then create a pivot table.

TODAY

The TODAY function will return the current date. You can use it to create trailing week trends or develop rudimentary monthly projections in conjunction with the EOMONTH and DAY functions.

EOMONTH

The EOMONTH function returns the total number of days in a month based on a date.

DAY

The DAY function will return the day of the month as a number.

Month To Date Percentage Formula

To create a rudimentary* monthly projection based on month-to-date traffic you need to calculate the percentage of the month already in the books.

=DAY(TODAY()-1)/DAY(EOMONTH(TODAY(),0))

The first part of the formula returns a number that represents the number of days that have gone by in the month based on today’s date. The -1 ensures you’re not including today in the calculation.

The second part of the formula returns the number of days in the month based on today’s date. The 0 is telling the EOMONTH function to return the number of days in the current month.

The rest is simple division.

Today is July 6th, 2010. That means that 16.13% of the month has already passed. If you received 10,000 visits between July 1 and July 5 you simply divide that number by 16.13% to see you’re on track to rack up 62,000 visits in July.

*This doesn’t account for fluctuations in day of week or holidays which are notoriously bad for many sites. That’s why it’s a rudimentary projection.

HLOOKUP and VLOOKUP

LOOKUP functions are powerful advanced functions that are vital in creating report dashboards. I’ll cover these and pivot tables in another post if folks are interested.

This list isn’t exhaustive by any stretch of the imagination. Hopefully it covers most of the more useful functions needed for SEO.

What did I miss? What Excel functions or formulas do you use?

Postscript: Leave A Comment // Subscribe (RSS Feed)

The Next Post: SEO Pivot Tables
The Previous Post: Facebook 2010 = Google 2003