SEO and Excel are like chocolate and peanut butter – great together. Here are some Excel functions (and two formulas) that can make SEO just a little bit easier.
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.
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.
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.
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.
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.