Research like a pro: spreadsheets

Where do I find the plot again?

A few years ago, I developed a workshop for writers titled, “Research Like a Pro”. It was meant to highlight some things that professional researchers do that writers could adopt to become more efficient.¹ To put it together, I drew on decades of experience as an analyst in the intelligence community, at RAND, and as the director of a DOD research lab.

I developed this workshop after going on tour for The Hunger. At every stop, I was asked how I did the research for such an obviously research-intensive story. I realized many people hadn’t done a research project since high school or college and needed a refresher. And maybe a little perspective.

One of the things I talk about in the workshop is how I don’t use paper when developing my books (or writing them, for that matter.) That’s changed a tiny bit; I’ve succumbed to the siren call of notebooks, occasionally, to work through a plot point or brainstorm in general. But for the nuts and bolts of plotting, I rely on… spreadsheets.

That’s because spreadsheets are what I used for the last 20 years or so as an analyst. Before that, I was entirely dependent on yellow highlighters. It was the era of paper. We’d print out the intelligence we were going to draw from to write our reports, and we’d highlight the parts that were essential. The office I was in at the time was usually working on a “war” footing—conflict on the ground, U.S. policymakers watching closely—and meant working really, really quickly. No time for endlessly rewriting drafts.

Now, I’m just as dependent on spreadsheets. The point is, spreadsheets are just another tool to help you get your thoughts together and to keep track of them more efficiently. I know plenty of people are leery of spreadsheets, but if you don’t use the mathematical functions, you’re going to be fine. It’s just like a piece of paper, only with the ability to add and delete lines (rows, columns) without needing to recompose the entire thing. And I’m happy to report that since I’ve been teaching this workshop, I’ve heard from other writers who use spreadsheets, too.

The last benefit I’ll mention, before getting into how I use spreadsheets, is that you’re not left with a pile of paper after the project is over. No decisions to be made about whether to keep all those scraps, how to store them. No project boxes following you forever.

Here are a couple examples of how I use spreadsheets in my writing process:

Bible: One of the first things I do for any project is create a spreadsheet to act as a bible, capturing all the important bits of pertinent information. You’ve probably heard the term “bible” used in conjunction with television writers. The bible is to mean the keep track of details about a show and/or a series. Bibles are often put together for novel series, too. Any writer who comes in to work on the TV show or the series can use the bible to get right to work.

In these Excel files (I use Excel but of course, you are free to use whichever program you prefer), there’s always a sheet labeled “characters” where I keep track of the characters’ names and some background information (relationship to the main character, physical features if pertinent, etc.) I’m flexible here; it’s not like I have a list of must-have details for all characters. I try to populate this as I’m writing the story but invariably I slack off, and have to catch up when I’m on the second or third draft.

Some people will create character bibles, far more comprehensive collections of facts about the characters. If this is important to you, you might create an entire Excel spreadsheet just for this, capturing all the pertinent details for each character. I can see where, if you’re writing a series or TV show, you might want to go this extra step, capturing eye color, major turns in that character’s history—whatever is pertinent to your work. Depending on the story, it might include physical characteristics; where they went to school or where they grew up; where they work; and, almost certainly, their relationship to other characters.

My bibles also include a sheet with the all the plot points, usually by chapter. It might look something like this:

Line 2 should read “…keels over dead…”

This is a pretend story so I can show you an example of how my spreadsheets work. These might be very short, brisk chapters, or I might combines all these beats into one chapter. The column on the right, “Notes”, is a catch-all column for things not IN the chapter/beat right now, but maybe something I want to think about incorporating.

From one version of The Fervor’s outline. Here, color-coding tells me which things still need to be added, or something that was added since the last rewrite. The second column is where I keep track of which POV the chapter is being told from.

Here’s the labor-intensive part: I will keep a similar page of every major revision of the book (again, either chapter by chapter or every plot point). These tend to get more detailed with each revision. This may sound like a major pain in the ass and it is, but in the long run, it has saved me more time than anything else I do. Let’s say I decide to move a specific action up earlier in the novel; I can use the outline to find all the subsequent events related to that specific action, events that might need to be moved around, altered, or maybe even eliminated. Now, some people will use edit→find to look up a keyword associated with that specific event to find every occurrence in the manuscript, but I find that’s a bit of a crap shoot. The other benefit of doing this is that it keeps you really close to how your story unfolds, which in turn may lead to you come to more revelations about your plot. Anything that makes you think more concretely about your plot is a good thing.

Tracking multiple POVs: I tend to write in third person, with multiple narrators. I will use spreadsheets to keep track of the chapters/scenes from each POV.

Let me break this down a little. A story requires that the writer comes up with the scenes needed to tell the story: scenes are the pieces that must be there for the plot to make sense. In a multiple POV story, the author decides which POV is the best one from which to tell this particular piece. For instance, if a secret is instrumental to the plot—a devastating bombshell that only one character knows—then it makes sense to refer to this secret from that one character’s POV. But it’s just as important that the rest of the characters remain ignorant.

The point of using a spreadsheet to track POV is twofold: one, it helps me make sure that I’m putting the reveals in the right order. Of course, you can do this with the outline of the plot which I referred to in the previous section but by including POV, you can make sure you’re using the right narrator for this particular plot point.

But secondly, I use it to measure how much “screen time” each POV is getting. The protagonist should be getting the most pages, of course, but what about the rest? Maybe you find your antagonist has nearly as many pages as the protagonist. Is that okay? There’s no right answer to that question, or rather the right answer is whatever best serves the story, but these kind of metrics are useful for further investigation. You might ask if there a reason for this. Maybe your subconscious is trying to tell you something about the antagonist. Another example is that maybe you find out you have only a couple scenes from another character’s POV. You might consider whether you need that extra POV at all. Having too many POVs can be confusing for readers, after all. You want to make deliberate choices and again, having the information laid out in front of you helps you understand what’s going on more objectively.

Timelines: Using a spreadsheet to keep track of your story’s timeline is a no-brainer. It allows you to pin a lot of information to a date and/or time. For instance, The Deep is about the Titanic, and the ship’s sinking was famously complicated and drawn out: when ice was first sighted, when messages were sent out from the radio room, when messages from other ships came in, what was happening on the ship itself (loading and releasing lifeboats, what the captain, crew, and key passengers were doing at any given minute. A spreadsheet is the right tool for juggling that much information. I understood what was happening much better after plotting it out like this; it really helps you get your arms around something that big.

If you write a book with a timeline that’s even slightly complicated, I guarantee that your copyeditor will end up having questions about timing. You might consider breaking down the timeline in advance, so you can both refer to it. For Red Widow or Red London (I forget which), the copy editor had a lot of questions about the timing of all of the main character’s overseas postings, which impacted when other key things happened in the story. In this case, I added a timeline of the main character’s career to the timeline that I developed for the novel, so we could see that there were no continuity errors.

At this point, this bookkeeping might sound a bit daunting. You don’t have to do any of it, of course. You can keep in on index cards. Or you can start small or simple. I worked with relational databases for years, so doing things this way makes sense to me, but the only thing that matters is that you do what feels right and which you find the most helpful.

That was a firehose. If I’ve left you befuddled, please drop questions in the comments section and I’ll try to do a better job explaining.

1. I say efficiency, but what is efficiency except a shortcut to productivity? I think what I’m proposing are ways to develop a habit that will get you to the important part: writing. You develop muscle memory, little rituals and routines, that help you to fall into the work phase.