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.


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.

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).


Got thoughts?

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s