April 11, 2018

Improving Our Word Count Spreadsheet

by Ryan

In the last post, I went over the basics of creating a simple word count spreadsheet. If you're not familiar with spreadsheets, it's a good place to start. As simple as the spreadsheet is, it provides a foundation for other improvements. Recording the data is only the first step. With the improvements to the word count spreadsheet in this post, we'll start to see how it can be used to provide more information about our writing.


Level Up the Word Count Spreadsheet

I'm going to pick up with the word count spreadsheet created in the last post. Right now it makes it easy to enter the ending word count after each session and it calculates the finished words written. If you wrote 250 words and deleted 200 of them, it will only show 50 words written because that was your net gain. It could even be negative if you went back and deleted words previously written! I'm focused on progress each day, so that's fine.

Still, the spreadsheet doesn't show me more information. If I have multiple projects, I can't see totals for each without going through the list. It doesn't tell me how many words are written on average. It won't track streaks. And it doesn't have any information except the title of the project. If I want to use the word count spreadsheet to gain insights about my productivity, then I need to make some changes.

I'm using the current version of Excel to create this word count spreadsheet. You can do many of the same things in other products, but the commands will be different.

Format as Table

Here's a tip to spruce up your word count spreadsheet—and add great functionality at the same time!

First, click on one of the cells where you've put in information, such as the title or word count. Next, up on the Home tab of the ribbon (the bar with the icons at the top of Excel), in the Styles section, click the Format as Table button.

A drop-down panel will give you options to pick for formatting your word count spreadsheet. Pick the scheme you like by clicking it. Excel will automatically select the information you've entered. Make sure the box is checked that says "My table has headers" and click OK.

Just like that, Excel formats your spreadsheet! This is called a table in Excel.

Even better, when you add the next entry in the row underneath, it will automatically extend the table to include the new information. You can keep adding rows simply by typing in new entries—just don't skip rows! It'll also automatically copy formulas to the new row.

Totally Your Table

Okay, now we have a table in our word count spreadsheet. Other than adding some formatting, what good is this?

To start, we can add some additional features to make the table more interesting. Let's start by adding a total at the bottom of the word count column. All we have to do is click on the Table Tools Design tab on the ribbon. Right in the middle is a section called "Table Style Options." Check the Total Row box.

Excel automatically adds a total row to the bottom of the table and adds a formula totaling the Words column for us. If we don't want to see it anymore (say we want to add a new entry), we can uncheck the box to hide the total row. After we add our entry we can check the box again to bring it back.

Filtering Your Word Count Table

You may have noticed the arrow buttons in your top row with the headings. Those buttons allow you to filter what shows in your table. You can use them to show only the current month or other date ranges. You can filter to just the project that you want to see. Combined with the total row, this gives you an easy way to see how many words you've written within a given time period or on different projects.

The filters give you different options. If Excel detects that the column contains dates, you'll see that it has a variety of predefined filters, e.g., choose "This Week" to see only items from the current week. Or "Last Week" to see entries from the previous week.

When you filter a column the other items are hidden from view—but you can clear the filter or change it at any point to see other items. That means you never have to see a big list of entries. You could set the date filter to this week and add items as you write. When the week passes it will show the current week as if there are no entries until you add more, but the whole time all of your previous entries are safe.

If you turn on the total row, it will show you the total for only those items showing.

Just like the date column, the Project column filter allows you to filter by the contents of that column. Instead of Date filters it will show Text filters. Either way there is also a search box in the dropdown and a treeview you can use to display and uncheck or check different entries. 

Average Word Counts

I want to show one more thing for this post. There's still many more ways we can enhance the word count spreadsheet, but I'll do another post for the next step. First, I want to revisit that total line at the bottom of the table. Turn it on and click the that adds up the total words written (for the displayed entries). In the formula bar above the column headings, you can see the formula used to calculate the total.


The formula uses the subtotal function. A function allows Excel to perform various types of calculations and other operations on the data entered into the cells. Subtotal can perform different actions depending on the function number, e.g. 109 in this case, which is a SUM function, meaning it will add up the items in the [Words] column. We didn't have to write the formula because Excel automatically created it when we turned on the total row the first time.

You may notice that the total cell also has a dropdown arrow button on the right side. If you click that it will open a menu of other functions you can use instead. I'm going to click "Average."

Now, instead of showing the total word count, it shows the average word count which tells me that I've written 505 words on average per day. By clicking the dropdown arrow button again and picking SUM, it will change back to the total. You'll see that you have other functions available. You can also write your own formulas to use in the total row. 

One more tip: If you want the name of the row to be something other than "Total," just click in that cell and type what you want it to say. Since I want my word count spreadsheet to show the average words written, I'll change that to Average. Excel remembers your changes, so you can still turn the total row on and off.

Next Level Word Count Spreadsheet

You can think of each of these posts as quests. This series of quests started with a look at streaks, moved on to spreadsheet basics, and now we've added formatting, filtering, and totals or averages to our tracker. It's very functional right now. We haven't maxed out the level yet! We can add even more functionality to the spreadsheet to give us greater insight into our writing and productivity. Check back for the next post. You're also welcome to sign up for my newsletter to find out when I post more content to help you in your publishing journey as a writer with a day job (or even if you don't have a day job).

Help Ryan write full time by supporting him on Patreon!


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 Publishing, and in On Spec Magazine.

Ryan M. Williams

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
%d bloggers like this: