K S Lane enjoys reading, writing, and oxford commas. She penned her first novel at the age of twelve and has been writing ever since.
Writing is one of the most rewarding tasks out there. Watching the little idea that spawned in the back of your mind develop into a fully fledged poem, short story or novel is truly miraculous. Think about it; you're literally creating a world out of nothing. However, writing can also be incredibly difficult. It can be hard to keep up motivation and difficult to track your day-to-day progress. In this article I've outlined the steps to creating your very own progress spreadsheet, which will help you keep track of your daily word-count across all your projects, your average word-count and the total number of words you've written across a certain timeframe, or perhaps even across the span of your entire writing career.
Step One: Create the Framework of Your Spreadsheet
To start off, you'll need to decide how you want to build your spreadsheet. To do this you need to consider the following:
- How many projects do you have? If you're like me you'll have four or five on the go at once, but perhaps there's only one that you want to track. Once you've decided the number you can label them across the top row (bear in mind that 'PROJECT ONE,' ect. can be substituted for the name of a novel or short story). In my personal spreadsheet I even have a column titled 'other' for my miscellaneous ramblings.
- How many days do you want to track your progress for? Are you embarking on a monthly long challenge to improve your writing output or do you want a steady, long-lasting tool? Your intent will determine how many rows your sheet will have. In the below example I've only used thirty three (including the header and 'total words' row) and have labeled them with dates for a single month. Bear in mind that you can always go back and insert more rows later should you decide to keep using your spreadsheet.
Pro tip: Use the autofill function to complete the dates for you. After typing in the first one, click on the cell and hover until you see a little yellow circle on the bottom edge. Click this circle and drag down to have the program fill in all of the dates instantly. Too easy!
Step Two: Calculate Your Total Words
The next step in the process is to add your daily word-counts to your spreadsheet. Doing so is fairly simple:
- Click on the first cell in your 'total words' column (not including the title). In my example spreadsheet the cell is F2, but it could be different in yours.
- Add a formula to the selected cell by clicking the 'insert' button in the toolbar and then selecting 'sum.'
- The program should automatically select the cells to the left of the formula as the range, which are the ones that you want selected. You can see if this has happened by looking at the cells highlighted blue. Are they the ones that you want your daily word-count to be calculated from? If you're unsure please refer to the picture below, and if the right range wasn't automatically selected then refer to the next step, in which I explain how to change the range.
- Once you have the correct formula for the first cell adding the rest is easy. As mentioned previously, all you have to do is click on the cell and drag downwards to all of the cells you want to duplicate your formula in.
- Plug a few numbers into the spreadsheet to test that everything is working out. If it is then congratulations, you're almost there!
- Take a lemonade break. You deserve it.
- To add word-counts for each of your projects to the bottom row of your spreadsheet follow the same procedure as you did before, adding a 'sum' formula to the bottom of the 'PROJECT ONE' column. Again, the range should automatically be selected as the entire column. Make use of the autofill tool again and drag across to fill in the rest of the cells.
Step Three: Add Your Averages
Keeping track of your average weekly or monthly word-count is a great idea for those writers who want to set and stick to goals but just don't have enough consistency in their daily schedule to be able to write everyday. By aiming for an average word-count, eg. seven thousand a week, you can miss a writing day without the guilt of abandoning your word-count goal. To add cells that will display your average into your table you need to:
- Insert a formula into your chosen cell (if you want to keep track of a weekly average then add one every seven days, if you want and monthly average try one every thirty days and so on). This can be done by clicking the cell, selecting 'insert' (in the top menu bar) and then 'average.'
- Once you've inserted your formula you'll need to specify what values you want it to average. To do this double-click on the cell you chose. You should see a bar pop up with the word 'average' in it and a blue oval shape that contains the range the formula is currently working for.
- Click on this oval shape again and a blue rectangle highlighting the range will pop up. To adjust your range simply adjust the rectangle by dragging on the circles in each corner. In this case you'll want to move the whole rectangle over so that it's covering the 'TOTAL WORDS' column, and make it longer or shorter depending on how many days you want the average of.
Step Four: Include a Pop of Colour!
Now for the fun bit; adding colour to the word-count column! The first step in this is to have a goal in mind; how many words do you want to write each day? I'd recommend not setting it too high because you'll only disappoint yourself, but also not too low as you'll never be motivated to push yourself. A happy medium between these two extremes is perfect, and only you know what number that is.
Once you've established your goal the rest is easy:
- Highlight your 'daily word-count' column and, on the menu to the right of the main screen, click 'cell.' At the bottom of the tab is a button that says 'conditional highlighting.' Select it.
- Press the blue 'add rule' button and in the menu that appears on screen select 'equal to.' This should be the first option.
- In the space designated for specifying the rule, type the number zero. Click the drop down menu under the space, scroll down and press 'custom style' and then select a black fill. Now every time that you write zero words for the day the cell will turn black (if you don't want to be as harsh on yourself maybe pick a less forbidding colour. Pink is always nice).
- Continue to add rules via the same process, using the 'greater than' and 'less than' and 'between' options as well as 'equal to.' Make any number exceeding your daily goal turn your favourite colour, and any number under it a less pleasant one. I use green, yellow and red, but you can pick any colour of the rainbow. Jazz it up a little!
Step Five: Write!
Your spreadsheet is finished! Now get off the internet and start working on your word-count so you can add some numbers and see it in action!
© 2018 K S Lane
K S Lane (author) from Melbourne, Australia on February 06, 2018:
Thanks for the comment, Louise. I hope you found it helpful :)
Louise Powles from Norfolk, England on February 06, 2018:
This is a really good idea. It's certainly something to bare in mind. Thankyou. =)
K S Lane (author) from Melbourne, Australia on February 01, 2018:
Thanks very much, Lauren. My organisation is pretty atrocious too... it seems to be a common trait amongst writers!
Lauren Flauding from Sahuarita, AZ on February 01, 2018:
This is a fantastic idea! Especially for someone like me who isn’t very organized (and I suspect a lot of other writers aren’t either).