Analyze Novel Structure With Excel

Authors can analyze novel structure using Excel.  Microsoft’s spreadsheet program can be an invaluable tool for scene and structure analysis.  Creating and populating a table with specific information from their novel can help them find flaws and weak points.

I started Western Watch – my first Lady Merreth novel – as something of  a “pantster”, with a vague idea of what would happen about three chapters ahead, but without an overall plan.  Novel structure was, needless to say, a mystery to me.

Then I read Randy Ingerman’s Writing Fiction for Dummies, closely followed by Scene and Structure by Jack Bickham.

Novel Structure

These two books (and I am sure, many others) put forth the following ideas:

  1. The novel’s building block is the ‘scene’, not the chapter; chapters are merely scene groupings
  2. Each ‘scene’ is categorized as either a ‘scene’ or a ‘sequel’
  3. ‘Scene’s have a scene goal – something the protagonist wishes to accomplish
  4. The outcome of the scene is a ‘disaster’ in that the protagonist usually does not obtain his or her goal
  5. There are three types of disaster:
    1. The protagonist does not achieve his or her goal
    2. The protagonist does not obtain his or her goal AND their situation is made worse by their efforts to achieve their goal within the scene
    3. The protagonist does achieve the goal BUT their situation is made worse or more difficult by that fact in some unexpected fashion
  6. The scene is followed by the ‘sequel’ where the protagonist processes his or her reaction to the disaster on an emotional level, thinks things over, and formulates a new goal, which sets up the next scene
  7. The novel structure is scene-sequel-scene-sequel etc until the novel is finished
  8. There is no set word length for scenes and sequels; they can range from a paragraph up to multiple pages.  A scene or sequel may be long enough to comprise a chapter on its own.

The two books cover things in much more detail than I have of course, and discuss myriad variations of the structure.  I strongly recommend reading both.

Both books told me I had not followed anything like their recommended structure in Western Watch.  I resolved to analyze the structure of my still unfinished novel.

I love tables for analysis, so I wondered if I could use Microsoft Excel to help out.  Turns out I could.  🙂

Excel Setup

This is how I set up my ‘novel structure’ analysis table.  From left to right here are columns:

  • S# — scene number
  • Tm – Time – tracks the day the scene took place and the time.  Days a numbered from the start of the story.  Thus a scene with a time entry of mD7 takes place in the morning of the seventh day from the start of the story
  • Description – a short description of the scene
  • Setting – a short description of where the scene takes place
  • POV – the point of view character for the scene
  • Type – is the scene and ‘scene’ or a ‘sequel’?
  • Goal – the goal of the POV character
  • Disaster – short description of the disaster in the scene, N/A for sequels
  • Conflict – note about which characters are in conflict in the scene
Western Watch Novel Structure, Brant Forseng, @brantforseng

Western Watch Structural Analysis — Click to enlarge

To the right of the Conflict column I have placed a column for every named character in the novel.  I colour-coded the entries so that I could see at a glance who has participated in each scene.  Characters who are related in some way have differing shades of the same colour.  For example, Lady Merreth and her sister, High Mistress Ammantha, are both of Sable House, so Merreth is black and Ammantha is dark grey.  Likewise, characters from the Red Hand are assigned various shades of maroon.

I also entered a two-letter abbreviation of each character’s name into scene row where they appear in the scene.  This allowed me to use Excel’s @count function to quickly see how much “page time” each character was getting.

Novel Structure Scene Count, Brant Forseng, @brantforseng

Western Watch Structure — Scene count. Click to enlarge

Once I had he table set up I spent several hours going through my manuscript filling in the columns.


It became apparent that my novel is not structured according to the principles laid out in the two books I listed above.  I knew this going in to the exercise, but it was a bit sobering to see all those question marks in the Type and Goal columns.

In addition, I was quite surprised at just how much page time some of my “secondary” characters had managed to acquire for themselves.  A couple of them are now better characterized as “supporting” rather than secondary.

Finally a glance at the Tm column let me that a lot of the story takes place on the 13th day.  It may be a little unrealistic to squeeze so much action into a single 24 hour period.

The spreadsheet will be of great aid to me when I do the second draft of Western Watch.  I’m going to use it as a road map to fix plot and structure issues.

Final Thoughts

I found Excel quite useful in a structural analysis of my draft novel.  The method outlined above may be of less use to those who outline/plot in more detail before drafting their story, particularly those well-versed in the scene-sequel structure briefly described above. Still, even they may find Excel of some value in tweaking their writing.

In short, it worked for me, your mileage may vary.  🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.