Writing Stats Spreadsheet v1

Now available is the spreadsheet that I’ve been using to record my statistics for The Rebel Queen. (It’s free, which means use at your own risk, no liability accepted. Always back up your files :-)). Writing Stats Spreadsheet v1

While it’s highly probable that I’ll make improvements to it later, I don’t envision doing any work on it for the foreseeable future. The spreadsheet is designed to keep track of word count in both draft and revision versions of a story. The Excel spreadsheet contains the following worksheets:

Settings

There’s really not much to see here. You can enter the name of your writing project, and it checks that all of your scenes have a point-of-view character assigned.

Stats

As the name would suggest, it shows some basic stats about the writing project. One of the cool things that I’ve recently added is the concept of an “estimated reading time”.

While the estimated reading time is of less value here (whole book), the value comes out in the per-chapter analysis. My new-found opinion is that I want chapters ideally to be 15-20 minutes long, ideal length for a commute. (Though it strikes me as I write this; commutes vary).

Basic Stats

While the table on this worksheet could be deemed ‘information overload’, it is important to be able to monitor word count reduction at a scene-by-scene level. Also on the stats page is a way-too-small chart which shows chapter and scene comparisons between revision and draft.

Chapter Summaries

A more useful by-chapter view of the world: scene count, word count, estimated reading time and percentage change between revision and draft.

by-chapter.PNG

Draft Scene Info and Revision1 Scene Info

These two worksheets are near identical. They both possess a table where you specify chapter number, scene number, a scene name, a point of view and the word count.

revision

(Ignore the bad scene names; I don’t want this to be a spoiler for The Rebel Queen). The scene names for the same scene must be the same in both draft and revision worksheets, but they can be in a different order (different chapter or scene numbers). Also, the revision worksheet can contain new scenes or absent scenes. (That’s what revision is all about). Filling out the Point of View is easy, with a drop-down list.

Characters

This worksheet contains a list, and summary of the characters who get a point of view in your story. (Non point-of-view characters aren’t included).

pov.PNG

Characters POV

This worksheet is also a recent addition, which also has the benefit of being aesthetically pleasing. I discussed this in a recent post on character balance.

Draft General Pkar POV

If you have any questions or problems using the spreadsheet, please add a comment below.

 

 

Advertisements

Tracking Changes between Drafts

(Just to be clear: In this post I’m talking about tracking word count and structure changes between drafts, not changes at the text-level).

I use spreadsheets that I use to track my writing (and editing) activities. I’ve previously shared how I use spreadsheets for laying out the plot, comparing chapters and scene lengths and monitoring progress.

Using the raw numbers and visual cues helps me to shape the structure of the story. It does take time to maintain but it provides a wealth of information I otherwise wouldn’t have.

You might say that it’s not important to track changes between drafts. For the most part you’d be right: stats matter most for the current version. Mostly I track them because my personality says I must. I can find some benefits in tracking between drafts:

  1. You can monitor how aggressively your editing is cutting words. I’ve read elsewhere that an amateur should aim to cut 15%.
  2. By knowing how you change a novel structure during editing it might help next time you’re writing. Ditto with identifying when a scene needs to be added/deleted.

‘Experience is the best teacher’ so the maxim says. While editing Vengeance Will Come I’ve learned a thing or two that I’ll do different (and better) on my next novel.

My initial tracking involved 3 tables like this: a before-edit, edit goal and result table.vwc stats before edit

This works fine when its a simple mapping between draft and revision. But that is not the case – certainly for me working on my first novel.

easy-comparison

The above simple version of 3 tables doesn’t work so well when you start having to move scenes around, adding or deleting scenes or moving entire chapters. Comparison then gets messy and hard to do.

My new approach is a little bit more complex to set up, but handles the chapter and scene movements with ease.

I’ll walk you through making a simple version of it. I’ll be assuming reasonable skill in Excel; if that’s not you, Google is your friend. If you’d rather just see a demo download this (xlsx): Demo wordcount spreadsheet

Step 1: Create a new workbook in Microsoft Excel with 2 worksheets. Name 1 of the Sheets “Structure” and the other “Stats”.

Step 2: Create a new table Scene List in the Structure worksheet. This list will contain a Scene Name and a word count column for each revision you’re tracking (e.g. draft, revised = 2 columns). The Scene Name isn’t going to appear in your finished story – it just needs to be something which describes the scene so that you’ll know which scene it is. The Scene Name needs to be unique – you cannot have the same Scene Name more than once.

scene-list
This table is where you will enter your word counts. You can see that in the screenshot the scenes DISCUSSING_THE_MEETING and MENAS_NEW_PLAN have a word count in version 1, but not in version 2. This means the scene doesn’t exist in version 2 of the story.

Step 3: Create a new table Structure List in the Structure worksheet. This table should have a Position column (which denotes the scenes position in the novel) and a column for each revision you’re tracking.

structure list.PNG

I’ve used a code for the Position of “CHxSCy“. In the second and third column of the table the Scene Names must be exactly the same as they are in the Scene List.

new structure.PNG

To show the same thing pictorially, this is what has happened between the two drafts.

4 scenes have been deleted from version 1 to 2 (or merged into existing scenes); 2 new scenes have been added and what was chapter 3 (version 1) has become chapter 2 (version 2).

Step 4: The Stats worksheet.

Column A: will display the same codes as used in the Position column.

Column B and C then display labels for the chapter positions (e.g. “Ch 1” and “Sc 1” respectively).

Then we have a column for each of the revisions that you want to map (D and E). In the revision column D use this formula:

=IFERROR(VLOOKUP(VLOOKUP(Stats!$A4, Position[#All], D$2,FALSE), SceneList[#All],Stats!D$2,FALSE),0)

In the revision column E use this formula:

=IFERROR(VLOOKUP(VLOOKUP(Stats!$A4, Position[#All], E$2,FALSE), SceneList[#All],Stats!E$2,FALSE),0)

How these formulas work is that they first use the Position information in column A to look up the Scene Name, and then look up the word count for the Scene Name (selecting the appropriate version).

Get Going!

I am not going to dwell on the lack of my own achievements or progress toward goals. Not today, anyway.

I am going to say that I am inspired by fellow blogger and aspiring-to-be-full-time author Ana Spoke. Ana is taking 5 months off from regular work to try and complete novels two and three of her Shizzle Inc. series. Her intention is not just to produce the novels but also for it to be a dry-run for the successful author lifestyle.

She plans to write 20,000 words a week; a number which is impressively large and dwarfs my current output.

One thing I like about Ana’s blog is that she clearly loves data, numbers, facts and figures. She doesn’t do assumptions; she does calculations. For a while I was tracking my output for my first novel Vengeance Will Come but I haven’t done the same tracking recently.

Vengeance Will Come - Word Count

She has inspired me to track my own word count per more closely, and try to elevate my output.