#word tracking

This spreadsheet was designed to allow you to set monthly goals for your writing, and easily track your words on different projects each day. It totals up your words for the day, your words for the month, and your words for the year. As you carry it along year to year, you can even track trends in your writing habits (for example, I’ve learned that I really suck at writing in May/June/July).

I’m going to go through this tab by tab to show you what’s what, and how to set yourself up and work daily.

We are actually going to begin with the second tab: Monthly Totals. This is where your totals are tracked, and where you set your goals. You can either set all your goals ahead of time, or month by month.

On this tab, you can fill in your pledges per month, and your goal for the year in the cells marked in grey. The monthly pledges (see column D) will transfer to other tabs as needed, and will be used to calculate a daily expected word count for each month. If you set an annual goal (you don’t have to!), you’ll be able to see how close you are to making that goal, and whether your pledges add up to the annual goal.

New: You can see your words to goal, and average needed per day to get to the goal.

You can see my usual pledges here. I've lowered my goals recently because I've found that around 500 words expected per day actually encourages me to make more, and doesn't make me feel bad if I make fewer.

The Actual column (B) is calculated automatically for you based on what you fill in on the Wordcount sheet, so let the spreadsheet do the work for you!

The next tab we need to take a look at is the first one. This is where you’ll be doing your work, on the tab titled Wordcount.

It may look complicated, but I swear it’s not! It calculates pretty much everything you need, as long as you just keep copying information from row to row.

It’s set up so it’ll work whether you start a brand new project of 0 words, or carry forward a WIP that had words before the new year started. All you have to do is put in the title, the purpose (fest, community, this is all just for your notes), and the due date, then put in your starting word count. When you add a new project, make sure to fill that starting word count (0 or otherwise) down through ALL the prior days so that your totals stay consistent. The way I do this in Excel is to select from the starting total down to the current date, then choose Fill Down. All rows MUST be filled in or else your words will go negative (see above, where the cell G6 has a value, even though the word count did not change that day).

When a day begins, it’s easy. Just copy down the prior day (except January 1st, just start with the one in place) by selecting from column F through the last column after your current WIP and then copy it down to the next row.

For example, if I were ready to start Jan 3rd, I select from F6 through I6 (one after my last project) and copy those cells down (I do it by using the little square in the bottom right to drag it down, but you do what's best for you.

When you finish working on a project, enter its current word count.

Now, let's take a closer look at what's above.

I started two projects on Jan 1st: Title and Title2

Title started with 0 words

Title2 came in from last year with 20 words already written

On January 1st, I recorded 100 words for Title and 1200 for Title2, and the sheet calculated that I wrote 1280 words that day

On January 2nd I didn't do anything on Title, but Title2 increased to 1300 TOTAL words (NOT new words), so I added 100 words that day.

On January 3rd, I added Title3 with 0 words to start, and I made sure that every row in my new column showed those 0 words.

The sheet color codes for days where you are below target or above target in the Actual Words/Day column. It’ll be white on zero days. You can see at a glance your trends for writing.

It also marks projects that have been added to as green on that day in their column, which is nice if you do a weekly or monthly round up. Again, you can see at a glance which projects you've worked on, versus the ones still waiting.

When you finish a fic, simply hide the column (do not delete it). That’s why copying the entire row from F to after last down is important, so you copy the hidden columns too.

Some information about the other columns.

Target Total is where you hoped to be that year by that date. If you’re not there, don’t worry!! Zero days are OKAY and give yourself a chance to catch up another time. If you need to adjust your pledges, go do that on the Monthly Totals tab and everything will update.

The Daily Target is the total you are hoping to reach to stay on target toward the Monthly Pledge for that month–it’ll be different each month depending on what you pledge.

Now let’s move on to some of the fun tracking pieces. Next up are Daily Graph and Monthly Graph, which are exactly what they sound like. I’ll show examples from my 2017 spreadsheet (note, the daily graph will look very up and down–that’s OKAY! Again, let yourself have zero days…).

Daily Graph

Monthly Graph

Please note that the monthly graph has changed slightly. The pledged words are a line, but the actual words are now an area graph so you'll be able to see that fill in, and see your pledge as a line over it. That will give something good to look back on at the end of the year, but I don't have a new example yet!

There is a weird little blank tab for tracking AO3 stats, if you want to do so like I do. Feel free to modify as you need, based on your own person fandoms. I just carry data through from year to year so I can see how things change.

The last two tabs are my favorite! The Annual Comparison tab becomes useful after you’ve been using the spreadsheet more than a year, because by keeping track of totals year to year, you can start to see what your writing tendencies are. This tab has changed this year!

In the main data, I have added a Range (difference between the current month and the average) and a Median. In both cases, it won't calculate (just like the average) until you're actually in that month. Prior calculations are against the prior year's data.

Just copy in your data from prior spreadsheets, then let the new column for this year calculate on its own. No work needed once you've put in the old data!

Sample data included solely to be able to make it make sense. Just delete it from the sheet and put in your own instead!

The chart has changed! The area chart is your average across all your data. The colored lines are all the years. The current year is in black, with markers, to make it really stand out. So you can see both how you are doing against your prior years and your typical average per month.

And the last tab helps you track your progress toward your pledges. This tab is why I created the spreadsheet in the first place. I did NaNo back in 2011 and realized that being able to see my progress helped keep me writing. I like visuals! It lets me see at a glance how I’m progressing toward my goal each month.

You don't need to fill in a thing on this sheet! It pulls in the pledge from where you set it on the Monthly Totals tab, and calculates your wordcount based on that sheet, and does everything for you. Just sit back and enjoy the charts. Red columns are the expected totals, and blue will be your actual progress. You can see whether you're ahead or behind (and DON'T WORRY if you're behind, it is OKAY).

Yes, that’s my actual horrible progress December 2018. It was a rough month.

Anyway, that’s it! Hope you like it, and if you’ve used it before, thank you for coming back. Feel free to ask if you have any questions!

This spreadsheet was designed to allow you to set monthly goals for your writing, and easily track your words on different projects each day. It totals up your words for the day, your words for the month, and your words for the year. As you carry it along year to year, you can even track trends in your writing habits (for example, I’ve learned that I really suck at writing in May/June/July).

I’m going to go through this tab by tab to show you what’s what, and how to set yourself up and work daily.

We are actually going to begin with the second tab: Monthly Totals. This is where your totals are tracked, and where you set your goals. You can either set all your goals ahead of time, or month by month.

On this tab, you can fill in your pledges per month, and your goal for the year. The monthly pledges (see column D) will transfer to other tabs as needed, and will be used to calculate a daily expected word count for each month. If you set an annual goal (you don’t have to!), you’ll be able to see how close you are to making that goal, and whether your pledges add up to the annual goal.

You can see my usual pledges here. I always try to reach high and pledge higher than I think I’ll make.

The next tab we need to take a look at is the first one. This is where you’ll be doing your work, on the tab titled Wordcount.

It may look complicated, but I swear it’s not! It calculates pretty much everything you need, as long as you just keep copying information from row to row.

It’s set up so it’ll work whether you start a brand new project of 0 words, or carry forward a WIP that had words before 2018 started. All you have to do is put in the title, the purpose (fest, community, this is all just for your notes), and the due date, then put in your starting word count. When you add a new project, make sure to fill that starting word count (0 or otherwise) down through ALL the prior days so that your totals stay consistent. The way I do this in Excel is to select from the starting total down to the current date, then choose Fill Down.

When a day begins, it’s easy. Just copy down the prior day (except January 1st, just start with the one in place) by selecting from column F through the last column after your current WIP and then copy it down to the next row. When you finish working on a project, enter its current word count. In the example above, I ended day 1 with 1 words on TITLE and 1450 words on OLD FIC. The spreadsheet calculated that I only wrote 1 word total for the day. I copied that row down, and the next day I had 2 total words on TITLE and 2500 on OLD FIC, and the sheet calculated that between the two, I wrote 1051 words for the day.

The sheet color codes for days where you are below target or above target in the Actual Words/Day column. It’ll be white on zero days. It also marks projects that have been added to as green on that day in their column, which is nice if you do a weekly or monthly round up.

When you finish a fic, simply hide the column (don’t delete it). That’s why copying the entire row from F to after last down is important, so you copy the hidden columns too.

Some other columns. Target Total is where you hoped to be that year by that date. If you’re not there, don’t worry!! Zero days are OKAY and give yourself a chance to catch up another time. If you need to adjust your pledges, go do that on the Monthly Totals tab and everything will update. The Daily Target is the total you are hoping to reach to stay on target toward the Monthly Pledge for that month–it’ll be different each month depending on what you pledge.

Now let’s move on to some of the fun tracking pieces. Next up are Daily Graph and Monthly Graph, which are exactly what they sound like. I’ll show examples from my 2017 spreadsheet (note, the daily graph will look very up and down–that’s OKAY! Again, let yourself have zero days…).

Daily Graph

Monthly Graph

The last two tabs are my favorite! The Annual Comparison tab becomes useful after you’ve been using the spreadsheet more than a year, because by keeping track of totals year to year, you can start to see what your writing tendencies are. And there’s an average line (the curvy one) to bring those years together.

And the last tab helps you track your progress toward your pledges. This tab is why I created the spreadsheet in the first place. I did NaNo a few years ago and realized that being able to see my progress helped keep me writing. I like visuals! It lets me see at a glance how I’m progressing toward my goal each month. The pledge column is filled in automatically, based on what you pledged back in the Monthly Totals tab. It calculates the expected daily word count, and what you need to write. Then you can see the comparison.

Yes, that’s my actual horrible progress December 2018. While I’m not far from my annual goal, I’m having a really rough month of very few words (can I count these instructions???).

Anyway, that’s it! Hope you like it, and if you’ve used it before, thank you for coming back. Feel free to ask if you have any questions!