Welcome to the first in a series of articles designed to teach quick tips and tricks related to web development, online marketing and web analytics. We will primarily focus on providing non-technical advice to help as many people as possible improve their online presence. So without further ado, we’ll start with a Microsoft Excel tip that can help with your search engine optimization and hopefully other tasks as well. Be sure to leave a comment if you’ve found other uses for this technique.
Count Words Using Excel
Here’s a helpful formula that will count words in a cell:
=IF(LEN(A2)=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)
How to Use the Excel Word Count Formula
- Create a new column next to the column containing words you want to count.
- Click the cell next to the cell you want to count, paste the formula as shown in the screenshot below and press Enter. The formula calculates the word count and writes it to the cell. **Replace A2 with the cell reference you want to count if it does not match the image below.
- Duplicate the formula in the remaining cells by “dragging” the formula down the spreadsheet. With the cell still highlighted, hover your cursor over the square dot in the lower right corner of cell B2 (shown in the screenshot above) until it turns into a plus sign. Then simply click and drag until you reach the last item in the spreadsheet as shown below. Release the click and the formula will fill in the word counts for the respective cells.
- Now you can sort the spreadsheet by word length.
Excel Word Count Formula and Search Engine Optimization
Counting keywords can help you find long tail keywords (more descriptive phrases that tend to have a higher probability of conversion) and keywords that fit certain specifications. For example, dmoz.org likes 25-30 words in a site description. Having a list of keywords by word count can help you massage the description and remain within spec.
Keyword Research Tools
Tools like the Google AdWords Keyword Tool are a great resource for finding keywords to optimize your site for. While it’s recommended that you use multiple keyword research tools, it’s hard to beat a free online tool offered by the top search engine.
The AdWords Keyword Tool not only gives you keywords, but levels of competition and estimated amounts of monthly search traffic as well. However, it does not currently have a way to export the number of words for a particular key phrase. This is where the formula comes in. I plan to cover the AdWords Keyword Tool in a future post, so for now let’s assume that you understand how to search and export a list of keywords.
- Apply the word count formula to your exported Google AdWords Keyword Tool spreadsheet.
- Sort the spreadsheet by word count.
- Create a new spreadsheet and copy over all the cells for the 1 word matches. The new sheet should only contain keywords with a 1 in the Words column.
- Continue by creating spreadsheets containing sets of “same count” keywords.
- Now you have groups of keywords arranged by word count to analyze.
Analyzing Keywords
With the grouping out of the way, you can start sorting the individual sheets. In general, we are looking for keywords that have high amounts of traffic (monthly searches) and low competition.
- Use the sort dialog box to sort by competition smallest to largest and then by monthly searches largest to smallest.
- Go down the list and find words relevant to the page you are optimizing for.
- Keep a list of the good keywords and use them in your content.
Why is Keyword Research Important?
Keyword research can make a huge difference in potential traffic. Consider the following results:
The keyword phrases are nearly synonymous and Google shows almost the same amount of competition, but the amount of monthly searches is not even close. So “real estate lawyer” could be a good candidate for optimization. However, it doesn’t necessarily mean it is…
A Word to the Wise
I mentioned earlier that you should not take one tool’s word for it. Check your results using multiple tools and do not take the numbers as if they were gospel. They are approximations at best. In fact, some tools use data from partner engines, so results can and often are skewed. Use them as a guide toward making more informed decisions. And as always, be sure to monitor your keywords and adjust them depending on how they are performing for your specific content.
