Even More Fun Ways to Use Excel to Track Your Writing Process

Way back in 2015, I wrote an article called “Fun Ways to Use Excel to Track Your Writing Process.” It’s been getting a lot of visits lately, so I thought it was time to do a new, improved, and updated version of it.

Let’s talk numbers! Writing numbers.

Tracking your daily word count is awesome.

I love knowing how much and how often I write.

Writing is so very, very slow. Sometimes the amount of work you’re doing is obvious–it’s pretty hard to not feel proud when you’re staring at 10 new chapters or 30,000 new words. But what about when you’re brainstorming, outlining, or editing? Or when you’re rewriting chapters? They can take tons of time, and most of it’s invisible. You could spend a whole season editing and come out feeling like you did absolutely nothing at all.

So I track my work. I keep track of every time I sit down at the computer, how many words I write, what kind of work I did, and how many hours I spent on it.

It sounds complicated, but it’s not. It takes me less than a minute. (Literally.) In exchange, I know:

  • How many words I wrote in a day, week, month, or year
  • How much time I spent doing writing-related tasks (and how much time was specifically spent writing, editing, or whatever.)
  • My average words per hour
  • The average amount of time it takes me to finish a novel or short story.

It’s fun. No, really! I promise!

So here’s what my current Excel tracker looks like.

If you checked out my 2015 post, you’ll notice that my current tracker’s a little different. let’s go through it!

Screenshot of an Excel spreadsheet with a month's worth of work recorded.
Click to see the full image. Yes, January was a lousy month for me.

It’s simple, but it works. Here’s what it tracks:

  1. Date: When I wrote.
  2. Title: The name of the piece.
  3. Chapter: I only use this field for novels. (It makes it easier to keep track of new and old word counts for the “Words Written” section.)
  4. Story Type: Short or Novel.
  5. Work Type: Can be anything. Usually this is writing, editing, or outlining. But I’ll talk more about that in a second.
  6. The “Words Written” Section: I enter the word count this piece or chapter had when I started (and “0” if it’s a new chapter or short story) and the number of words it had when I finished. Excel automatically calculates the number of new words.
  7. The “Time Spent” Section: This includes the time I started, the time I finished, and the number of minutes I spent writing. Excel automatically calculates my words per hour.

And I track everything. Absolutely everything.

I track everything I do that’s related to writing. Everything. I track:

  1. Writing
  2. Editing
  3. Outlining
  4. Worldbuilding (i.e., writing character profiles or theme files)
  5. Anything else I feel like tracking. For example, in January of this year, I logged a bunch of stuff under the super-unclear term “Analysis.” I was reading the first draft of my novel and taking notes about what to change. I wrote several thousand words of notes, so I recorded them.

If I’m working on my story, I track it. If I stop to outline a story for a couple of weeks, I’m not “doing nothing,” so I don’t record it as such. Everything counts. It’s all work, and it’s all helping me prepare to write a novel.

That’s great, but why do I do all this?

Let’s get one thing straight: I’m not doing this so I can stick to some arbitrary words-a-day habit. (Heck, I don’t even think “write 1,000 words a day” literally means “you must write 1,000 new story-related words a day.”) My goal is to know how often I work on my projects and to measure how much work I’m doing.

I know exactly how I’m using my writing time, when I’m being productive, and when I’m slacking off.

This is especially fun after you’ve done it for a year. I’ve been tracking my writing for nearly two years now, and I now know all sorts of stuff–how long I usually take to write a novel, for example, or when my biggest lulls in activity are.

I don’t know if I’ve ever made major decisions or changes based on this data, but it’s been invaluable in learning and refining my writing process.

Now that you’re (hopefully) convinced, I’d like to tell you about pivot tables!

COME BACK. I PROMISE IT’S INTERESTING.

Once you have all this cool data in Excel, its easy-peasy to make some cool tables that help you see your data. Here are some of the pivot tables I’ve used:

Number of Words by Month

Screenshot of my word tracker, showing the number of words written per month.

This fun one takes the number of new words I wrote, the type of content, and the type of story, and organizes them by month. This lets me see how much work I did each month and what kind of work it was.

Number of Words by Month and Title

Screenshot showing the number of words I wrote, by month and title.

This is similar to the above graph, but it organizes them by title. This way, I can see what projects I worked on each month and how much work I did for each.

Number of Words by Week

Screenshot of my word tracker showing the number of words produced by week.

And, of course, you don’t have to track anything by month. This is the same as the “number of words by month” graph, above, except it breaks it down by week.

There’s no limit to the type of data you can track and the ways you can display it. If you’re interested in learning more about how you write, give it a shot! You might learn something interesting about yourself.

7 thoughts on “Even More Fun Ways to Use Excel to Track Your Writing Process

  1. This is fantastic! I wish I was as good at Excel as you are. I can create a basic spreadsheet, but have not done Pivot Tables, etc. How did you learn to be a superstar Excel user?

    1. Well, thank you! I promise this isn’t nearly as scary as it looks, though. You really just have to fill a table full of numbers, and then Excel does all the hard work for you!

      But, er, I didn’t actually explain any of that in this, did I? That’s probably a good topic for a future blog post. :)

      1. Yes, because I can tell you are a GREAT instructor. I saw in one post, you did give clear instructions on adding a Pivot Table, but I still need to Google it, because I am not clear on “what” a Pivot Table does. STILL, I’m glad that you introduced me to using a spreadsheet — it surely helps in being “goal oriented.” GREAT IDEA! Data is good, data is our friend!

        1. Well, thank you! That’s very kind of you.

          And data is the best! :D

          But thanks for leaving comments and reading through my blog! I don’t get an awful lot of traffic over here, but I’m always delighted to hear that someone enjoyed my posts.

          1. Traffic is a tricky thing, right. We must also make the effort to read, Like and Comment on other people’s posts and then they reciprocate. Also, we need to find “like minded” Bloggers that share our interests. I enjoyed reading your posts, because I am writing some children’s picture books that I want to get published, so your posts gave me some good ideas. So, thanks for sharing. I look forward to seeing new posts from you. Arghhh, NOW, I just need to become more of an expert as Excel! I worked for a manufacturing company and they always had the Excel spreadsheet already set up, all I had to do was add, delete or change data. That was easy. Now, I have to actually create it from scratch, so you are pushing me out of my comfort zone — THAT’S GOOD! Happy Blogging!

  2. A google search brought me here, so I signed up for your newsletter so I could get a version of your Excel document described above, but sadly, the document provided to me is broken. You can’t add a day and have the pivot table work. When you try to select “Editing” or “Short” the data validation freaks out. I have Excel 2016. You also don’t provide the Time Spent column in your download like you show above. It’s a shame, because I really like what you’ve done here. I hope you fix it, but in the meantime, I’ll leave this comment here to save people the frustrating hour of trying to get the document to work.

    1. First off, I’m sorry that you had so much trouble with the template! You’re totally right, I accidentally broke the data validation in it and didn’t realize it. Yikes!

      I’ve uploaded a new version here. If you’re not super frustrated with it already, it shouldn’t have any rules for what you can put into the Story Type/Work Type columns. I’ve also not personally used the Duration section for a while, which is why it wasn’t on this version of the template, but I dug it out of one of my old templates and tossed it back on.

      This version also explains on Slide #1 how to update the pivot tables, even though it’s… extremely fiddly. Whenever I start a new template at the start of the year, I usually have to wait until I have several days of data, refresh all those tables, and set the correct start and end dates in the Group settings (sometimes repeatedly) before it sticks. Once I’ve got about 3 or 4 items in the list everything populates properly when I hit Reload, but it’s really bad when you’re just trying to start it out.

      Also, when you clear out the demo data, be sure to not delete anything in the Total, Duration, and WPH fields, because those have formulas in them. (And if you start a new row and those fields are empty, copy/paste them from a previous row where they’re working.)

      That said, the fact that I have to say all this means this an extremely fragile template, and I absolutely agree I need to make a more robust one. Anyone downloading this thing is definitely not going to want to set a bunch of fields and kick Excel until it works. I’m thinking of moving it to Google Sheets or something more sharable (especially since I have a REALLY old version of Outlook). I need to get around to that!

      Thanks for your patience, and I’m sorry again that this was such a headache!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s