Calculating Keyword Density in Excel
As part of an SEO project, I wanted to use Excel to evaluate keyword densities of various text. Excel doens’t offer a built-in function for counting words in a string, but I found this one online:
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))+1
A1 contains the text in question.
This works by comparing the length text before and after removing the spaces between words. This will treat hyphenated words as single words. A similar replacement comparison technique can be used to count the number of times a keyword appears in text. Dividing the number of keywords by the number of words in the text provides the keyword density. In Excel:
| Cell | Description | Formula |
| A1 | Keyword | (the keyword) |
| B1 | Text | (the text to evaluate) |
| C1 | Total Word Count | =LEN(TRIM(B1))-LEN(SUBSTITUTE(B1,” “,””)) + 1 |
| D1 | # of Non-Keywords | =LEN(TRIM(SUBSTITUTE(B1,A1,””))) – LEN(SUBSTITUTE(SUBSTITUTE(B1,A1,””),” “,””)) + 1 |
| E1 | Keyword Density | =1-D1/C1 |
Pick a feed
