Word count Excel for authors

I never started writing after a conscious decision. Tulagi Hotel, my 134,000 word novel, came about after I chanced to write the first chapter at the office, over an extended coffee break, and with no intention of making it into a full-fledged book.

But when it was published, I found myself writing short stories. In a span of six months, I wrote 16 stories. At this time, I became interested in tracking the word counts of the stories. This is interesting for two reasons: most short story competitions have word limits, and it is nice to know the progress of your Work in Progress (WIP) files. Of course you can just note the word count and manually copy it to an Excel sheet, but I thought to make that process automatic. I will explain how, and also, share my file under the CC-BY-NC license.

To do this, you need Excel, and its VBA (Visual Basic for Applications) editor. Microsoft has built in a system in all its application software that allows you to programmatically glean information about a file, without actually opening the application that you used to write the file. The links to the files can be refreshed every time you open the summary Excel file, and you can manually refresh the word counts if you want to, by selecting Data – Refresh All in Excel. Let’s look at the Excel file structure first.

I have two worksheets in the workbook. One is called FINISHED, and it has no live links to files. This is because every link you have slows the opening of the file, and if a file is finished, there’s no need to check its word count. The other is called WIP, and that one has the live links. Of course there is no reason for not having them in one worksheet, but I preferred this for clarity.

I first decided to have the columns CODE, NAME, GENRE, WORDS and PAGES. Code is my way of telling whether the story is finished or not: the story F18, Yessirree, is finished, and it is the 18th story I wrote. Name is just the story name. Genre is a handy column, because for competitions, genre is important. Words is the item I pull from the Word file, and Pages is just Words divided by 526.

I also have the columns COMMENTS AT, PUBLISHED AT, COMPETITIONS, and SENT TO. These I use to track the life of the story, because I used to lose sight of where I had entered the file to competitions or magazines. This is how the Finished worksheet looks:


The color codes are used merely to identify similar items by color too, and are not at all essential to the operation of the file. The important thing is to have the columns named like this, because that enables you to sort the file by any column. There is one noteworthy piece of Excel on the sheet, and that is the totals of word counts and files on the top of the sheet:

109917 words in 34 finished stories and 206 pages with 33993 words in progress

This is a handy way to see at a glance what is your total word count, and how it is divided into stories. The formula that calculates it is as follows:

=D38&” words in “&COUNTA(A3:A52)&” finished stories and “&E38&” pages with “&wip_words&” words in progress”

The cell D38 contains a sum of the values of the WORDS column, ie. =SUM(D3:D37). The function COUNTA counts the number of cells that are not empty in a range. The ampersand is a handy way of combining text in quotes with formulas, values, or other text.

As for the WIP where the information is likely to change as I add stories and edit the other WIP stories, there is only one rule. I have to use a naming convention that enables the custom function to work. For this, I have one named cell, PATH, which contains the folder under which all the WIP files are in their respective folders. Every folder has the same name as the story, and is preceded by the sequence number I have for each story idea. Therefore a story may reside in a folder called 47 Nueva Congo, and the story name is Nueva Congo.rtf ( I work in RTF due to ease of sending files to readers; everyone can read RTF).

So, the PATH cell has the value C:Usersheikki.hietalaSkydriveshorties, and the setup of the worksheet looks like this:


The function that gets the WORDS column values is the only tricky part in the whole system. It is called GetWords, and I found something like it on the Internet and then crafted it to suit my needs. It is called like this:

=GetWords(path&""&A3&" "&B3&""&B3&".rtf")

So, you see the function call is sending the path, the file folder, the file name, and the extension .rtf to the function, and it is returning the word count. In fact, this is the way you always work when you craft a custom function. The cell references are relative references, so we can copy the formula down as we add new WIP stories.

Let’s create the custom function. First, open the VBA editor by pressing Alt+F11.


This blank editor needs to have a code module added to it. For that, open the dropdown under the menu, then select Module from that list:


This gives you a blank module:


After this, you can paste the following piece of VBA code into the module. Remember to get the entire snippet of code, else it will fail.

Public Function GetWords(strInFile As String)
Dim objWord As Object, objDocProps As Object
Dim i As Integer
 On Error Resume Next
 Set objWord = CreateObject("Word.Application")
 With objWord
 .Documents.Open strInFile
 Set objDocProps = objWord.ActiveDocument.BuiltinDocumentProperties
 For i = 0 To objDocProps.Count - 1
 If i = 15 Then
 'Debug.Print objDocProps(i).Name, objDocProps(i).Value
 GetWords = objDocProps(15).Value
 End If
 Next i
 End With
 objWord.Application.Quit savechanges:=False
 Set objWord = Nothing
End Function

You should have it looking like this:


When this is done, click on the Save icon in the toolbar, and then select File – Close and Return to Excel. Now you have a custom function in your Excel that you can use. At this point, save the Excel file as Excel Macro-Enabled Workbook.

As I suggested, you could have a “master folder” under which you can have all your stories. The only consideration is, Excel must know the path to the file. Therefore you can either have the master path in a cell you name Path, and then add the folder name for each of the files. Or if you have all your files in one folder, you can just change the filename.

In my solution, I call the function this way:

=GetWords(path&""&A3&" "&B3&""&B3&".rtf")

This corresponds to my Excel structure, and combines the path with the contents of cell A3 (folder name), then a space, then the cell B3 (file name), a backslash, and the file name again, topped with “.rtf” (file format). However, you can use the GetWords function in any way you like, because the only thing is, it needs to be passed your file location as a string. Therefore, if you have all your files under one folder, and have all of them in the .doc format, you could insert your path into the cell that is named Path, and then edit the function call to be like this (assuming the cell A3 contains the file name):


In the extreme case you want to check just one file, it can be done with this function call:


Of course, you would need to edit the path to suit your own setup, and make sure you have it in quotes, so it is passed on as a character string to the function. When you decide on the columns you need, all you then have to do is to update the function call so it combines the column contents in a way the gives the function a valid path to the file.

In case you are not entirely sure how to edit custom functions, I have provided the working file for you as is, and you can take it into use merely by editing the path cell. To test it, I placed a test file in the path you see in the file, and entered its name in the cell A3. As you then see, it gets the word count. When you change the path and file name to suit your needs, the system updates itself. I have given the sum cells a range of 100 rows, so it will fit quite a few stories. Also, do not delete the cell that calculates the words in the WIP files. It is called wip_words and its presence is needed for the Finished worksheet’s summary clause.

Contact me if you have problems. I hope this works for you as well as it has served me.

Get the Excel 97-2003 format file from here: Wordcounts

UPDATE: here is a slightly edited and easier to use Office 365 version of the file: 

0 thoughts on “Word count Excel for authors”

  1. Hey Heikki,
    Just trying to do this and did the word count no problem, but how do you do the page count? I looked for the answer and even went to page 2 on Google, but no luck.

    Thank you in advance

    1. Hi! Glad you liked it. There’s no direct way to add page counts into the macro, due to font size and margins, but if you figure out your average word count per page, it’s a simple division then.

  2. Ah! That explains why I couldn’t find the answer anywhere, good to know. I don’t know if you can answer this, but I’ll try my luck as my Excel is a bit rusty.
    I’m trying to make it so that I write the title of the word document in the first cell, so in the formula I can just reference it rather than having to change the formula every cell, but I don’t seem to be having that much success with the &s. If I spell out the filename it works, but not if I try to sum it up like you did in yours and I’m just wondering what I’m doing wrong. Do you need the & at each side?
    Don’t worry if this is too involved, I’ll figure out a way, it might just take longer

    Thank you again for the article, it was an absolute game changer, I’m just hoping I manage to crack this formula to speed up the process. And thank you so much for the prompt response!


    1. Hi again,

      I have to say that I was surprised to see this old blog post receiving a comment, but I will have a look at the file and try to help you out. And I am very happy to have helped you a little!

  3. Hi, sorry for the delay,

    Here’s an example:

    Here’s the complete path of a file:
    C:\Users\AC\Documents\Current AC\Writing\Abstract ideas and excerpts\fic.rtf

    I wanted to break it so that like you said I could have the different bits, so I would’t have to individually rename each file, and for example I can copy the name in the title cell and just use that for the final word count formula.

    I guess my question is, how do you split them and then reconnect them? Because trying your above:
    =GetWords(path&””&A3&” “&B3&””&B3&”.rtf”)

    In any way shape or form didn’t work.

    Do you need more info or does that make sense?
    Thank you

    1. Hi! I uploaded a new version for you into the blog post.

      The idea is that you put the path, ie. C:\Users\AC\Documents\Current AC\Writing\Abstract ideas and excerpts\ into cell F1, which is also named “path”.

      Every file goes into column A, just the file name and extension as such, ie. fic.rtf

      This way all you need to do to add files is to copy the formulas down as you go, and add your files to the column A.

      I am not sure I understand splitting and reconnecting things, as the macro does it for you.

      Let me know if I can be of any more help 🙂

  4. hi Heikki,
    I am trying to produce a progress report spreadsheet for a couple of books I am writing. The sheet works great but it would be fabulous to have it update itself by only running a macro. My problem is the publisher wants every chapter in a separate directory. I need to get the info from each chapter document and insert it in a seperate cell in the spreadsheet from there it will work out the chapter word count, total word count, chapter number of pages and total number of pages . The spreadsheet is working fine i just need help getting the info into it from the word document. I have been told to assume 600 words per page. Any help would be gratefully appreciated.

    1. Hi Mike, glad you like it. It’s a rather strange requirement from your boss to want every file in a directory, but I think you can work out how my sheet works.

      When you are in the sheet, press Alt+F11 to see the module. It has one single piece of code in Module1:

      Public Function GetWords(strInFile As String)

      Dim objWord As Object, objDocProps As Object
      Dim i As Integer
      On Error Resume Next
      Set objWord = CreateObject(“Word.Application”)
      With objWord
      .Documents.Open strInFile
      Set objDocProps = objWord.ActiveDocument.BuiltinDocumentProperties
      For i = 0 To objDocProps.Count – 1
      If i = 15 Then
      ‘Debug.Print objDocProps(i).Name, objDocProps(i).Value
      GetWords = objDocProps(15).Value
      End If
      Next i
      End With
      objWord.Application.Quit savechanges:=False
      Set objWord = Nothing
      End Function

      This is the GetWords function. You can either try to modify this, or, add the path to each file before the Chapter names. I believe you would then see the number of words in documents as you see now in my example, then just work out the other numbers from that.

      I can try to assist you further, if necessary. Good luck.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.