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)