How to Make Your Own NaNoWriMo Word Count Tracker Using Microsoft Excel

Posted by Unrepentant Escapist

December 4, 2012 -- 6:39 a.m.

Since I found the NaNoWriMo website's tracker to be such a helpful motivator, I decided to create my own using Microsoft Excel. This is what it looks like:


It doesn't look as pretty as the NaNoWriMo version, but it only took me ten or fifteen minutes to do. It's been a while since my linear programming classes, so there's probably a simpler way to do this, but here's the instructions if you want to copy what I did. I tried to keep the instructions really simple for those of you who aren't as familiar with excel:

You'll need four columns.

1) The first is for days of the month. If you don't manually want to type in the numbers, you can type 1 in the first box. Then, in the next box down, write =A2+1 (or whatever box you used). Select that box. You should see a little black square in the lower right corner, almost like one of the dark pixels from one of the old video games. Click on that square and drag down to fill your column up with your formula. If you've done it correctly, each box should have numbers rising consecutively (day 1, 2, 3, 4, 5) etc. Stop when you've reached the appropriate number of days in the month.

2) The second column is your daily word count. I like being able to see how much I write a day besides how much my total word count is, so I can monitor whether I'm being consistent and look for patterns that might help me figure out how to increase my productivity. Leave this column blank for now.

3) The next column will be your total word count for the month. It's a running total. If you're writing all in one document, you could just be recording this yourself using Word's automatic word count feature, but I like to write every chapter in a new document so I'm less tempted into endless revisions on my book. This can make keeping track of total word count a little difficult, since you're looking at totals over multiple documents.

For that reason, my total word count is going to be a sum of all my daily wordcounts. To create this, I start by setting the first box of column C to =B2. In this case, that's zero, because I took a break after NaNoWriMo to celebrate finishing a winner. For the next box down, you want to sum the old total (zero) with the new total (anything you added in day two.) So, an easy way to do this is to type a formula summing the two cells. In this case, SUM(C2, B3).

Now click on the corner of the cell and drag the formula down the column. Excel will automatically change the cell number for you, so that cell 3 (C4 in this example) will automatically read SUM(C3, B4).

4) The final column is your daily target. You don't have to follow NaNoWriMo's 50,000 word target. This can be anything you want. But I figured I might as well stick with 50,000. 50,000 divided by 31 (the days in December) means I should be approximately doing 1613 words a day. So for these cells, you start with 1613 in the first cell, and every cell thereafter should be +1613. In this case, I wrote =D2+1613. Click and drag down.

5) Test to make sure everything's working by putting random numbers in the daily word count boxes. If your word count total changes appropriately, you've done everything you needed to do.

Voila! You have a bunch of columns with numbers in them. If you don't want the graph, you can stop now.

But you want the graph, don't you? I decided to use a scatterplot graph because it's easy to make and easy to read.

1) Highlight columns A, C, and D. You don't need column B for this one. To highlight multiple, non-adjoining columns, hold the control key down and click on the column letter at the top. This should highlight the entire column.

2) Go to the tab menu at the top and click "Insert." In the middle of the menu, there will be a variety of graphs to choose from. I chose a scatterplot with smooth lines. If you chose the right columns to highlight, it should create a graph with days on the bottom and number of words running up the side with a smooth slope of your goal in the middle and another line with your progress.

3) The default this produces is an ugly graph. I like mine neater, so I right clicked on the axis and hit "format axis", then used it to format the maximum on each side (so I wouldn't have extra days or words along the sides) and also set my word count axis to thousands. If you get to the axis format menu, that stuff's fairly self-explanatory, so I'm not going to walk through the steps with you. I also changed the graph's format from the default to one with axis titles and a main titles, but your personal preference may vary. I deleted the axis title on the left by clicking it and hitting backspace because I wanted the graph nice and big instead of wasting space on a redundant label.

Like I said, this is not especially pretty or fancy, but it's functional. The graph looks wonky now since the total's so flat, but by the end of the month, my plateau should be more of a jagged blue mountain. And it's really easy to do. My instructions probably make things look way more complicated then it actually is. And if you want to make it cooler, you can make every column a different color, or use the conditional formatting button in the home tab to make your boxes red when you're below your goal and green when you're above it. (Hmm, that's a good idea. I'll go do that now. *Click*)

If you run into any problems or have any questions, feel free to leave a comment. There's a moderator queue to stop the spambots, but I do glance and sort through the comments every once in awhile. I'll do my best to help.

Edit: Oh, I just remembered. If you want to make copies of your sheet for next month, make sure to do it now, while the sheet is clean. If you try to copy the sheet at the end of December, you'll have to delete all your word counts. Easier to do it now while your cell values are still blank.