SEO Excel Functions

// // July 06th 2010 // SEO

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

Character Length in Excel

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.

Rounding Numbers In Excel

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.

Change Date to Day

Change Date to Month

Change Date to Year

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.

Month To Date Calculation

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:
The Previous Post:

9 trackbacks/pingbacks

  1. Pingback: 18+ SEO Excel Functions | Blind Five Year Old :: JKP1375, Inc on July 11, 2010
  2. Pingback: 18+ SEO Excel Functions | Blind Five Year Old » SEO Update on July 13, 2010
  3. Pingback: Wednesday Internet Marketing Links – July 14, 2010 | Internet Generated on July 14, 2010
  4. Pingback: This Month’s SEO Roundup | The Blog of Echo-Stream.com on July 15, 2010
  5. Pingback: SEO Pivot Tables | Blind Five Year Old on July 24, 2010
  6. Pingback: Excel für SEOs on July 27, 2010
  7. Pingback: Dean Cruddace » 261 Thank You`s to the SEO Industry 2010 on February 9, 2012
  8. Pingback: Excel Tip: Word Count « Wrath of Robots on March 8, 2012
  9. Pingback: Excel for SEO – Must Have Tools and Resources on February 20, 2013

Comments About SEO Excel Functions

// 24 comments so far.

  1. JWC // July 06th 2010

    thank you. i needed this!

  2. Top Notch SEO // July 07th 2010

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

  3. 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

  4. 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.

  5. Glynn // July 09th 2010

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

  6. 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 🙂

  7. 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.

  8. aj // July 12th 2010

    Thanks Will.

    And, yes, you could TRIM the other half but the SUBSTITUTE replaces (and removes) every space, so it kind of takes care of itself.

  9. 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

  10. 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.

  11. 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?

  12. 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!

  13. Victor_wang // July 12th 2010

    Good,Spectacular!

  14. Amit Verma // July 13th 2010

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

    Thanks for your post.

  15. Seb Berné // July 13th 2010

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

  16. fibc // July 13th 2010

    Nice work

  17. 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!

  18. 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

  19. 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.

  20. 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.

  21. Bryan // December 19th 2011

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

  22. Daniel B. // September 19th 2012

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

  23. Chris Sanfilippo // August 16th 2013

    Awesome! Thanks for the excel awesomeness

  24. Walsall // January 09th 2014

    Great post!
    Ill have to start using excel more in SEO.

Sorry, comments for this entry are closed at this time.

You can follow any responses to this entry via its RSS comments feed.

xxx-bondage.com