How to Create a Word Progress Chart in Excel

Photo credit: All screenshots are mine.
Many of you may remember last month’s post on why I keep track of my word count progress, in which I mentioned an awesome NaNoWriMo chart and referred back to this picture.

While I couldn’t find a widget to replicate it, thanks to some awesome Twitter people (@YeseniaVargas32 and @MadelineDyerUK), I was able to figure out how to create one with a spreadsheet on Excel.

I love it so much that I want to share the process with you guys, so that you can create your own motivational word count progress chart in ten easy steps. Enjoy!
  1. Determine your goals. This chart will really only be useful to you if you have some kind of goal to work with. It doesn’t have to be one that’s set-in-stone, but in order to make this chart as beautiful as possible, you’ll want to determine a final word count goal and a deadline. In my case, I’m aiming for 70,000 words in forty days. 

  2. Set up the spreadsheet. In the first row you’ll want to set up your headers. If all you’re recording is the data for the spreadsheet, then you’ll only need two columns: Date and Total Word Count. Under the “Date” column, plug in the first day you will begin writing, and the day after that in the next cell. It should look like this:


  3. Select data. Click cell 2A, hold down shift and click cell 3B (or the first empty cell under the “Date” heading, and the second empty cell under the “Total Word Count” heading).

  4. Create chart. Select “Charts”> “Column” > “Clustered Column.” You will now have a very ugly and empty chart, with two dates running along the horizontal axis and strange numbers on the vertical axis. Don’t panic; we’re going to make it beautiful. 

  5. Set the horizontal axis. Right now there should be two dates on the horizontal axis. Double click them to open up the Format Axis menu. Select the “Scale” tab, and set the Maximum to your deadline, and the Major Unit to 1 and click Ok.

  6. Set the vertical axis. Double click the numbers on the vertical axis to open up the Format Axis menu again, but this time for the vertical axis. Once again, go to the “Scale” tab. Set the Maximum to your word count goal and the “Major unit” to 5000 or so, and click Ok. All of the numbers on the vertical axis have now disappeared, but they’ll reappear once you enter some numbers. 

  7. Select data (again). Click the whitespace in your chart to select the chart and the data. A highlighted box should appear around your selected data that looks like this:


  8. Adjust data input. Select the bottom left or right corner of the bounding box and drag it low on the spreadsheet. The idea is to select one cell for every day that you will write (so if you’re giving yourself forty days, select forty cells). It doesn’t have to be perfect and you can always adjust it later, but the easiest thing to do is select way more cells than you’ll need. 

  9. Start recording your data. Your chart is now ready to use. All you need to record is the total word count of your manuscript day to day, and the date. If you miss a day, record it anyway, even though your word count won’t have changed from the day before. As long as you record everyday, you’ll have a lovely chart that looks similar to to the one at the top of the post. 

  10. Bonus steps: 

    1. To remove the legend: Click the “Series1” label on your chart and hit the delete key. 

    2. To change the colors: You may select different colors with “Chart Styles” (in the chart menu above the spreadsheet) or by adjusting the settings in the Format Chart Area menu, which you can access by right-clicking the chart. Using Chart Styles, however, is much easier.  

And there you have it! Now go have fun with your awesome new word progress charts.

Twitter-sized bites: 
Want a NaNoWriMo-style chart to keep track of your writing progress? Here's how to make your very own: (Click to tweet
Writer @Ava_Jae uses Excel to create a word progress chart while first drafting. Have you tried this trick? (Click to tweet)

15 comments:

Melissa Maygrove said...

Great post!


*shakes head* I need to take a class on these different Word programs... :P



I still keep my progress in a plain document, using a table. Part of the reason is that I don't just keep track of total word count. I also keep track of total words written per day. I have a column in there for noting 'plot notes added or deleted' (I have the plot notes as part of my document, so changing it affects word count). That way I make sure I get credit for what I actually wrote. ;)

Ava Jae said...

Thanks, Melissa! I actually also keep track of words written per day (as well as words written per week), they just don't go in the chart. :) I find it really gratifying to be able to see progress in three different levels (daily, weekly, total), but when it comes to the chart, in order to mimic the NaNoWriMo graph I fell in love with, I just take the data from the total word count section. But there are loads of ways of keeping track of progress and they all work as wonderful motivators.

SJ Mitchell said...

What a great idea! Thanks for sharing!

Ava Jae said...

Absolutely! Hope you find it useful. :)

Andi-Roo said...

This is so kewl! I use Excel a lot, but have never messed around with the charts-n-graphs functions. Now I want to go make lots of trackers for everything in my life, hahaha!!! You've created a monster! Thanks so much for sharing this! :)

Ava Jae said...

I hadn't really experimented with the charts and graphs until working out how to do this one, and now I love it. Have fun with your new graphs! :D

Alberto L. Beretta said...

That's quite useful, thanks a lot for sharing...
I mostly write in Italian, my native tongue, but it's useful for all languages, of course...

Ava Jae said...

You're very welcome! And you're absolutely right--the beauty of Excel is that numbers cross most language barriers. :)


Thanks for stopping by!

Alberto L. Beretta said...

You're very nice to reply.
'Writ...ability', good site title, = ability to write, Italian 'scrivibilità', 'abilità di scrivere' ;o).

Ava Jae said...

Grazie! I try to reply to every comment. :)


Also, I have to say that I love the Italian translation. "Scrivibilità" sounds sophisticated, not that I'm surprised, as Italian is a beautiful language.

Alberto L. Beretta said...

Thank you also for the nice words about my native language, stemming from Latin, with some ancient Greek too...

Ava Jae said...

Absolutely! I meant what I said. I've always wanted to learn Italian.

Alberto L. Beretta said...

My dear, a project of mine is 'Turbo-ItAlien for...', as one or more e-book(s), CD audio, DVD, web community and live groups, but I'm still alone as for this.

Ava Jae said...

Ah, well I wish you all the best with it!

kanima said...

chart yang menarik http://cara-excel.blogspot.com ini mudah dipahami
terima kasih infonya

Post a Comment

Related Posts Plugin for WordPress, Blogger...