In this post I’m going to talk about a way to use a spreadsheet as a diagnostic tool for finding problems with your draft. I mentioned it online earlier this week, and there seemed to be a bit of interest there, so I thought I’d do a proper blog entry about it.
First, I went to emphasize that this is not my idea! I found it via a post on this blog; however, the post in which he talks about it seems to be gone (it was back in 2011). I am under the impression that JK Rowling does something similar, but I’ve never looked into her method and how it might differ from this one.
Materials: You will need a novel and some kind of spreadsheet software. I use Google Sheets these days. If you don’t feel like messing with spreadsheets, you can do it with note cards (and I have). In that case, you will need a mess of 3×5 or 4×6 cards (one per scene), several different colors of pen, tape, enough space to spread the cards out on a wall or a floor, and of course a novel.
I say “a” novel and not “your” novel because even though what I mostly talk about here is how to use this with your own writing, you can also use it with other people’s; see the note at the end of this post.
If it’s your own book you’re working on, I feel that this tool works best when you have a complete but unpolished story to work with; it’s okay if you have some holes, but if you’re still in a very early/exploratory stage, it might not help much.
I’m going to step through the spreadsheet layout I use first, and then dive into why.
Step One: Lay It All Out
Open up your novel draft and a brand new spreadsheet.
Your first two columns should identify chapter and scene by number. If you don’t have chapters, just number the scenes. You’re going to have one row (or note card) for every scene in your novel when you finish.
The third column is for the main action–what is the major thing that scene accomplishes in the story. If you can’t figure out what that is, fill it with ???? and move on to the next scene.
Depending on the story, I may have another column (or more) here for theme and/or subplot. For Tisiphone’s Quest, since the prompt was “Gothic science fiction sex fantasy mystery”, I had a separate column for each of those, with a mark for which elements were highlighted in each scene. For the book I’m going to be revising in 2019, two characters have romantic subplots, and the main character goes through a significant internal journey, so I’ll want to keep track of those.
Then add a column for the location of the scene. Finally, add a column for every. single. one. of your named characters. Add them in order of appearance. Put a marker in each scene where that character shows up thereafter, even if they don’t do anything in the scene.
(See the next section for an explanation of the colors.)
Do not edit your draft now. Remain calm! Fill in the spreadsheet until you have reached the end of the draft and created all of your columns and rows. If you spot what you think is a problem as you’re filling it in, make a note of it and keep going.
Take a break.
Step Two: Apply Color and Style
I have used color two ways in these thus far; you may think of others. When I was editing Tisiphone’s Quest, I used it to identify different POVs.
Blue indicates the scene is in Sam’s POV, yellow ones are Paul’s, and gray is for the antagonists. I used a bright green bar (see the screen shot in the previous section) to delineate the three major “acts” of the story, since I was using that structure.
You might notice in this screen shot that I’m also using two different fonts. Because I was already using color to code for POV, I used font to indicate scenes that are mostly expository (regular) vs mostly action (italics). If you don’t need to keep track of POV, you can use color to code for that instead. Fairy Hills is all from one POV, so when I wrote up my note cards I used red ink for action scenes and green for expository ones.
You can get very complicated here, obviously, but I recommend keeping it simple, with no more than four colors in play.
Three: Look, and Think
Nothing in writing is set in stone. You will not find anything that any writer has ever done that someone wouldn’t have said was the wrong thing to do, so don’t worry about that. What I’m putting in this section are things you might see using your diagnostic tool, some questions that you can ask, and possible issues that this tool can highlight for you.
The one absolute-ish thing that I do want to say here is that while there are no true rules, your reader’s attention is not an infinite resource. Make things easy for them where possible.
Chapter & Scene Number – If I look at this column and see that I have eight scenes in a chapter–that’s a lot of jumping around; I wonder if I can consolidate or cut some? Whereas a single long scene that eats up a whole chapter might make me wonder if I’m trying to make that one scene do too much. Do I have a chapter that’s way shorter or longer than most of the others?
There may be perfectly good reasons for these things. Maybe you have several short scenes in a row because you’re setting the stage for a long, intricate one, or establishing context before a climax. Maybe your extra-long scene is navigating a tricky bit of exposition or an emotional resolution. That’s why I say these questions might find issues. In any case, have you properly oriented your readers for each scene? Every change asks them to do a bit of mental work to figure out what’s happening; reward them for it by making each transition significant, and make it easy for them to know whose head they’re in, and where they are.
Main Action – First, obviously, you can use these to look for fundamental story flow; do the problems and solutions occur in the correct order, kind of thing. If you’re using a three- or five-act structure, do you have rising action and appropriate breaks?
Next, look for those question marks. If I can’t figure out what the point of a scene is, there’s a chance that my readers will also wonder why it’s there, and if there’s too much going on in it for me to pick out the most significant element, they might be confused.
Someone famous said that every scene ought to either advance the plot or establish character, if not both. If you have given your readers a lot of hard-working pages that do these things, you can get away with an occasional “glide” as my workshop teachers called them; indeed, if the story has been very intense for a while, your readers may appreciate a breather. Keep in mind, though, that the point of a breather is that you can pick the pace back up again after.
Subplots & Themes – Good for spotting those times I introduced a sparkly new theme in chapter 22 out of 25, or started a subplot and then ignored it for a hundred pages. Be kind to your readers; if a plot thread is important, give it a tweak once in a while so they know that. If an element is critical to the resolution, set it up ahead of time.
Location – This one has room for a lot of variation. Fairy Hills is a road trip; there are virtually no repeated locations in it, because that’s the point, but do I have too many generic hotel rooms when I could be taking advantage of local color? Prometheus Tapestry is set in Boston, and one of the fun things about urban fantasy is the chance to use actual locations in the story (I once had a beta reader say that they knew exactly which Dunkin’ Donuts I was referring to). If you’re writing a country house mystery, on the other hand, the entire story could take place in three rooms.
Here I would advise only that you have a good reason for location changes–or lack thereof. Use your choices thoughtfully to add texture.
Characters – This one can be tricky, especially if you have a lot of characters, but look for patterns. Do important characters go on long, inexplicable absences? Is a minor character crowding center stage? Do important people not show up until act three? Is your cast small and cozy except for this one chapter where four minor characters show up and are then never seen again? If you have a sprawling cast, do they cluster in groups, or do they move around and mix with one another, and have you included a handy reference for your readers?
This is one point where I might (might!) recommend being brutal with your editing decisions. Named characters who only appear once had better be doing something critically important to the story, like being murdered. Otherwise, they’re just taking up real estate in your readers’ brains for no good reason. That said, you don’t necessarily need to cut them, metaphorically or otherwise. If you have several minor characters filling a similar function, maybe they can be combined into someone more fleshed out.
Color Codes – Again (again!) what’s appropriate is going to depend on what kind of story you’re writing. If you’re changing POVs a lot, how many of those do you have? Do you have excellent reasons for writing from each of those POVs? (I know you do.) Are you giving roughly equal time to characters of equivalent importance? Or does one character get all of the really good bits, and if so, do you actually need that other one? My first draft of Tisiphone used five different points of view, which I later determined was too many.
If you’re color-coding for scene type, what’s the rhythm of your story? Tisiphone is a genre adventure, so I tried to include an action scene at least every couple of chapters. You may be writing something more serene, but even so, are your readers being pulled forward by new questions and new revelations at some reasonable interval? Are plot elements resolved, or have they stalled? Do you have a long string of action scenes without pauses for explanation? If that’s what the characters are experiencing, okay, but be thoughtful as you exhaust them, and your readers.
Four: Write… Notes
At this point you may have identified a lot of things you want to fix, or want a drink.
Write.
Them.
Down.
All of them. You can do this right in your spreadsheet using new columns or an annotation tool, or write it on your note cards. Capture the things that you are thinking before you start mucking around with revisions. You will run into weird synergies during this process, find places where two birds can be killed, etc., but not if you jump right into it.
Some of these fixes might be specific and easy: “Replace Character M with a telegram to reduce clutter,” or “Cut this scene except for that one bit, and move that to Chapter Z.” Some will be hard. Examples of notes that I have written for myself include, “Slow chapter–move some of this dialog out,” “Too many scenes in a row with just A and B talking to each other,” and my favorite, “Introduce this theme earlier!”
Take your time. Bounce ideas off your favorite people. For each of these problems you’ve found, figure out a means to address it. And I’m going to say this again, do not start editing until you’re done making the plan. If only because when you start changing things in chapter 1, they’re going to ripple down to the end of the book, and you don’t want to have to start over again when you get there.
Five: Revise
That’s easy, right?
Right. You have a plan; work through the changes you lined up.
Note: Practicing on Other People
Like I said way back at the start of this post, you can use this tool to analyze other people’s writing as well as your own. Just take a favorite book of yours, break it down, and look for the patterns. How does Brilliant Author handle POV changes? How many characters can they handle? What does their pacing look like? How do they advance their subplots?
One of the best tools you have as a writer is your own sense of what you like, so figure out why you like it, and make that work for you. Or hell, pick up a story you think is objectively bad (not just something you hate). What does that author do that you can strenuously avoid doing? Even if you dislike the story, is there anything in it that they do really well?
I hope this is a little bit useful for some people! Let me know what you find out if you end up using this, and if there are any other subjects you’d like me to tackle.
R Jean Stevenson 