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?**

The Next Post: SEO Pivot Tables

The Previous Post: Facebook 2010 = Google 2003

## 9 trackbacks/pingbacks

## Comments About

SEO Excel Functions// 24 comments so far.

JWC // July 06th 2010

thank you. i needed this!

Top Notch SEO // July 07th 2010

Awesome – Need to get to know these well for future analysis!

coseom // July 07th 2010

Awsome post, I am working everyday with Excel to caculate stats with pivot tables etc.

Now I can even use it for meta optimization etc. Thanks, you made my life easier

Sean Weigold Ferguson // July 09th 2010

I would love to see a tutorial on pivot tables for SEO. It would be awesome if you also included some working examples in the post, or perhaps even an attached .xls file with some data so that we can follow along with you.

Glynn // July 09th 2010

Great post, love to know more about the HLOOKUP and VLOOKUP functions. Thanks

gudipudi // July 10th 2010

great stuff atleast for the guys like me who are not very comfortable wth excel..this could be a good start

Will Critchlow // July 12th 2010

Great stuff – minor point – you should trim() the cell contents in both halves of the formula for counting words (i.e. SUBSTITUTE(TRIM(A1),” “,””)) to avoid counting spaces at the ends of the string.

SEO Ludwig König // July 12th 2010

Thanks a lot for these really helpful formulas! At the moment I need those formulas very much and then you give it to me and all the others. This is timing, this is very nice.

Thanks and Greets from Germany

Jason Cook // July 12th 2010

nice summary. I’d definitely add Text-to-Columns as a vital Excel tool (although not a function). especially great for breaking down landing page and referring urls.

aj // July 12th 2010

Thanks Will.

And, yes, you

couldTRIM the other half but the SUBSTITUTE replaces (and removes)everyspace, so it kind of takes care of itself.aj // July 12th 2010

Thanks Jason. And yes, Text-to-Columns is amazingly useful. I wonder if that’s a well known tool or not?

Jason Cook // July 12th 2010

@AJ – surprisingly I think the answer may be “no”. I consider myself a bit of an excel-nerd and I only started using Text-to-Columns about 2 years ago – at most.

Then there are always pivot tables. possibly THE coolest toy in the Excel chest!

Victor_wang // July 12th 2010

Good,Spectacular!

Amit Verma // July 13th 2010

I have to really understand this, I am not so good in Excel.

Thanks for your post.

Seb Berné // July 13th 2010

Thank you so much for this post. I am also highly interested in a post about HLOOKUP and VLOOKUP.

fibc // July 13th 2010

Nice work

Darryl Taylor // July 14th 2010

Thanks, a really handy and concise little list. I’d like to hear the HLOOKUP and VLOOKUP stuff & pivot tables too!

Niels Bosma // August 02nd 2011

Hi,

I’ve built an Excel plugin with a bunch of useful functions for working with SEO. Would appreciate if you’d like to try it out and give me your opinion. Anything missing?

Regards

/Niels

AJ Kohn // August 06th 2011

Niels,

Thanks for your comment. It may take me a while to get around to your Excel plugin, but I’ll take a look at it soon.

Dave Goessling // September 08th 2011

Great stuff – thanks!

Somebody asked a while ago about pivot tables. This is a great tutorial and an all-around excellent resource site:

http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/

Dave G.

Bryan // December 19th 2011

@Niels Seo plugin is great. … tho this post was the a regular reference point since time.

Daniel B. // September 19th 2012

Great tips!

I never thought that Excel could help me with SEO and online marketing

Chris Sanfilippo // August 16th 2013

Awesome! Thanks for the excel awesomeness

Walsall // January 09th 2014

Great post!

Ill have to start using excel more in SEO.

You can follow any responses to this entry via its RSS comments feed. You may also leave a trackback by clicking this link.