Tracking Word Counts Easily With Simple Spreadsheets
Tracking Word Counts

Demystifying Spreadsheets for Tracking Word Counts

I love data and data tools. Some of my fellow librarians find this amusing—and useful! I find data very useful. This isn't usually considered odd in athletic pursuits. People routinely track and discuss data about their favorite sports teams. If you're a runner you probably know how far, how long, and at what pace you ran. There's a good chance you track that information. Wearable tech has made it easier than ever to track our athletic efforts, visualize the data, and inform our attempts to improve. In my last post, I talked about the power of streaks. Today, I want to share some tips on using simple spreadsheets for tracking your word counts.

More...

Tracking Word Counts Simply

I'll start simply. You can use whatever spreadsheet program you prefer. If you haven't used any, Microsoft Excel and Google's Sheets are both good options. I'm going to use the desktop version of Excel and keep this simple to start. I'll walk through creating a simple spreadsheet and then mention some ways that it could be enhanced.

Spreadsheet Basics

Don't be intimidated by a spreadsheet. It can do so much work for you and can be used in lots of different ways to help you with your writing. A spreadsheet can help improve productivity. It has numerous business uses. And you can use it as tool in your creative process as well. I'm going to focus on tracking word counts, but many of the points covered can be used in other ways.

Here's a blank spreadsheet in Excel (Office 365, current version):

Blank Excel Spreadsheet

The spreadsheet contains a bunch of boxes known as cells, defined by the rows (numbered down the left side), and the columns (letters across the top). You can click in any cell and type text or numbers. The spreadsheet has a bunch of ways to format the cells, to change the size, and do math based on the cell contents. We're going to start small and add functionality.

What should we track?

Okay, so we want to track word counts. What do we need to know in order to track word counts? It's up to you, but I'd want to know a few details.

  • Date. When did I write the words?
  • Project. What project was I working on? I usually have several projects going at once between stories and novels.
  • Words. How many words did I write? This one could be done in a few different ways. We could track just the number of words written and figure that out myself. I usually like the spreadsheet to calculate that for me.

That's enough for now! Let's get started on creating the spreadsheet. I'm going to click in the top left cell, in row one, column A. The address of this cell is A1. I'm going to type some headings, one per column for each of the things I want to track. The end result should look something like the picture on the right.

Entering word counts

Now all I have to do to track my word counts is type the details about each writing session in the cells below the headings.

Excel automatically formats information you type. So when I typed "4/5" in cell A2 and pressed the TAB key (moves the selection box to the next cell, ENTER will move it down one cell), Excel reformatted the date. 

Under Project, I typed "Untitled Short Story" and it looks like it is cut off. It's all in the cell still, but because the word count cell has a number in it, the full project title doesn't show. 

We can change the formatting in different ways to change the way Excel displays the information. There are different ways to change the formats. I'm not going to go into them all (and they may differ depending on what you are using). 

I do want to change the date format. To do that, I'm going to click in the first cell, hold down the mouse button and drag down to the last cell.

On the ribbon (the bar at the top with the icons and commands), I'll click in the dropdown list that currently says "Custom" and choose "Short Date" instead. There are many other formats available, but I like this one.

Next, I'll format the word counts. I want a comma in the thousandth place. I'll select the word counts and right under the dropdown list, click the icon with a comma. Then, with the entries still selected, I'll click the decrease decimal icon (to the right of the comma) twice. I don't want to show any decimal places in my word counts.

All of this is just setup. Once we have our spreadsheet ready we won't have to mess with the format again unless we want to change things.

I do want to see the entire title. To do that, I want to make column B wider. All I need to do is a double-click on the line between "B" and "C" and the column will automatically fit the length of the title. I can repeat that later if I have a longer title.

At a very basic level, that's all that is needed! I can track my word counts now. There's much more that I could do with the spreadsheet to improve how it works.

Improving Our Tracking Spreadsheet

The first thing I want to do is have the spreadsheet calculate the number of words written. I'm going to do this by replacing the word count column with a starting count, an ending count, and let the spreadsheet figure the words written. I'll right-click on the column letter "C" and choose Insert to create a new column "C." My column with the heading "Words" will move over and become "D." I'll repeat this to add one more column.

That gives me two columns without headings, so I'll type "Start" and "End" on row one in the new "C" and "D" columns ("Words" is now column "E").

As I did the last time, I'll format those cells with the comma icon and remove the decimals (I don't have to have anything typed to set the format). Now, instead of entering the total words, I'll enter the starting word count and the total word count when I stop writing—which I can easily find out in whatever program I'm using to write.

Creating Our First Formula

Let's create a formula so that Excel calculates the difference between the start and end counts in each cell. In cell E2, I'll type +D2-C2 and press ENTER. As I do, Excel highlights the cells included in the formula. When I press ENTER the formula disappears, replaced by the answer!

If you look in the address bar just above the column headings, you'll see the formula as we typed it except it starts with an "=" equal sign. You can start a formula by typing "=" and for some formulas that's necessary. In this case Excel understood we were writing a formula from the "+" symbol and added the equal sign on its own.

Now that I have the formula in one cell, all I have to do is click and drag down on the square box in the lower right corner of E2. The mouse pointer will change to a cross symbol and when you let go after selecting all the rows, Excel fills the cells with the formula! Except it has adjusted the formula addresses to match each row number. 

Next Steps in Tracking Word Counts

This simple spreadsheet is only the start for tracking word counts. We can add to it so that Excel automatically formats our spreadsheet, calculates totals, average word counts, writing streaks, and much more! That's for another post. If you have questions, ask in the comments.

I'll also point out (being a librarian), that many libraries offer courses such as Lynda.com and Microsoft Imagine Academy which can help you achieve mastery of Excel and other programs. In our library we also offer the opportunity for people to take the Microsoft certification tests for free! 

If you want to receive updates, sign up for my newsletter. 

About the Author Ryan

Ryan M. Williams lives a double life as a full-time career librarian and a multi-genre writer with over twenty books. He writes across a range of genres including science fiction, fantasy, paranormal, mystery, horror, and romance. He earned a Master of Arts degree in writing popular fiction from Seton Hill University and a Master of Library and Information Science from San Jose University. His short fiction has appeared in anthologies from Pocket Books, WMG Publish, and in On Spec Magazine.

follow me on:

Leave a Comment: