I also love writing. Consequentially, I really wanted to find a way to better track my writing progress. I wanted more than the “X words out of XX,XXX” tickers that you see on message boards or the chapter-level or novel-level targets that Scrivener gave you. I wanted to map my work daily, showing exactly how much work I was doing and what kind of work it was. I wanted data that I could slice, dice, and display in different formats.

I might be a little nuts.

So I kept a record of everything I wrote all year. When I first started tracking my work, I recorded everything I did in a very simple Excel file. But last week, something happened. Something magical. Something wonderful.

I discovered pivot tables. And now I’m going to show you what I used them for.

(It gets more exciting in a moment, I promise.)

Step #1: Record Every Time You Do Anything Related to Writing

This is the only step that took any work. I logged an entry in Excel every time I did anything related to writing–so, basically, any time I wrote, edited, or outlined something.

I tracked everything. I didn’t just track words I wrote for stories. I tracked words I wrote for query letters, synopses, and outlines. Basically, if I was being productive and it was related to a story, I recorded it.

Here’s what my tracker looks like:

Screenshot showing what stories I worked on by day.
(January was a horrible month for me, but whatever. You can see what I’m tracking.)

It’s pretty simple. I track:

  • The Date: When I worked on anything writing-related.
  • The title of what I worked on.
  • The type of product. This can be a novel, short story, outline, query, or synopsis.
  • The total word count. This is automatically generated from the next two fields–“New Word Count” and “Original Word Count.” If I started a chapter or story from scratch and wrote 2,000 words, that “2,000” would go in the “New Word Count” and 0 would go in the “Original Word Count.” If I later picked up that piece and added 1,000 words, I’d have “3,000” (the new final word count) in “New Word Count” and 2,000 in the “Original”… Which would allow me to track how many words I added or removed that day.
  • The type of work. I use four categories here: writing, editing, outlining, and administrative (which I use for queries and synopses.)
  • The total words worked. This is a quirky and possibly not useful field. I hate it when I do a really good job editing, remove 10,000 words from a novel… And this file shows my monthly word count as -10,000 words. That’s what shows up in the “total” field. And, as you can tell from February 5, sometimes that looks nasty. So I made this silly field, the “total words worked” field. It’s a duplicate of the “total” field, except everything is a positive number.


Step #2: Use that PivotTable Magic!

Everything else is done automatically. Using the data you’re tracking in step #1, you can ask Excel to create a whole slew of beautiful tables. Like this one!

Screenshot showing everything I worked on by month in 2015.

(Yes, these are my real numbers. Yes, I had an incredibly bad start to the year, including an abysmal April. It got better, though!)

This beautiful table shows exactly how many words I wrote each month, broken up by what I worked on. The only real quirk is that I didn’t track how many words I wrote during my outlining period between April and June, so my actual total is a lot higher than the zeroes you see there.

All I had to do was:

  1. Create a new tab in Excel.
  2. Go to Insert > PivotTable
  3. Put “Date” in the Row Labels.
  4. Put “Work Type” and “Story Type” (in that order) in the “Column Labels.”
  5. Put “Total” in the “Values” section.
  6. Click the little arrow next to “Total,” select “Value Field Settings,” and set “Summarize value field by” to “Sum.”
  7. By default, it displayed all this data broken up by individual days. To get the month-by-month view, right click any day, select “Group,” then make sure it’s grouped by “Months.”

That’s it! Now it shows all the words I wrote this year, organized by the type of work I did and the type of product I created.

I have a different tab that shows this same data broken down by week. It’s glorious!

Step #3: Experiment with Different Data Sets!

There are all kinds of ways to display data! How about month-by-month breakdown of what projects you worked on? That’s another fun one!

Screenshot showing what products I worked on every month vs. the number of words I produced.

Isn’t that awesome?!

So data is awesome. Awesome. I could graph this stuff. I could look at it a zillion different ways. I’m addicted to numbers.

But I also find these numbers soothing. It shows me that I’m getting work done. It helps me see, in a very easy-to-read format, just how productive I’ve been this year.

Also, it looks really cool. What more could you want?