How To Freelance Tuesday: How to Count Words in Excel

This ties in to my post from a few weeks ago about writing articles and posts in Excel. While it’s been a great new tool for me to use, most of my assignments have word counts attached to them, so I need to know how many words I’m writing.

The thing is, since Excel isn’t used for Word purposes, they don’t have a word count function. To get more technical on you, spreadsheets don’t actually recognize what a “word” is — it knows characters, and spaces, and can count collections of characters between spaces in some way. So having a straightforward function to pop in, like a =COUNT, doesn’t exactly work.

In my initial search, I found some really technical posts about very long formulas to use to count words in an entire document, which, despite all my years of Excel work, I couldn’t figure out how to apply.

But recently I tried again, and I started small: I got a formula for word count for one cell. And it worked. And then I repeated it, and added up the individual word counts, and there you go!

Here’s how to do it:

You’re going to use this formula: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Don’t ask me what it means, but it’s something along the lines of what I mentioned above: Taking the total characters (LEN) and configuring it with the total number of spaces (all those quotes). The spots I bolded are going to be the cell you want to find the word count for, which you’ll change according to your needs. You’re going to drop that into the cell next to the box you’re typing in, like this (yes, I was typing in B10, and dropped the formula in C10):

Screen Shot 2020-09-01 at 2.38.48 PM.png

Start typing in the cell, and once you return or move off the cell, it’ll show you your word count, like this:

Screen Shot 2020-09-01 at 2.39.21 PM.png

Keep going, for as many cells as you’re typing in (yes, I see the 1 instead of the !, but I’m not re-screencapping it!):

Screen Shot 2020-09-01 at 2.39.41 PM.png

Screen Shot 2020-09-01 at 2.39.38 PM.png

If you want to know the total count of the words, simply add up the individual cells. Do so with a =sum function like this:

Screen Shot 2020-09-01 at 2.40.01 PM.png

Screen Shot 2020-09-01 at 2.40.05 PM.png

And that’s it! Now you can write your articles in Excel AND track your word count, without having to deal with super super long formulas for the entire piece that may or may not word anyhow.

Also, let me know if you want more more Excel tricks — I got a bunch of ‘em!