Planning a book with a spreadsheet

The first thing to be said is that any factual book involves huge amounts of research and planning, and you will invariably wish you'd made a note of something you saw, six months ago. And if you did, where did I put that notebook, and if I have found it, where, oh where is that note in the many, many pages of crabbed illegibility?  That is why I transcribe my notes into machine-readable form, as soon as possible after I leave the library or whatever, but it still needs to be organised.

The second thing is that, having dabbled around with fiction, managing the facts doesn't seem to be any easier there, either.

Here's a way to keep track.  I have used it many times, though this account is based on some of the work for a book I haven't written yet. Let's just say that a small part of the context is Australian and centred on the goldfields, it's mainly historical, and this sample is wildly misleading, because many of the lines were added from other researches. Don't waste your time guessing!

The spreadsheet format shown here is one that I have developed over a long period of time. The key thing to note is that you can sort by three columns at once, so I can sort by ch (chapter), then within that by pt (part) and then by no (number).

So how did it come about? The answer is that I got my grounding as a writer as a student journalist, working on honi soit, the University of Sydney newspaper. Back then, each paragraph was typed on an octavo (roughly A5 size) sheet of newsprint paper, double-spaced. As the sub-editor went through these, pages might be removed, switched in order or marked up in various ways.

Some years back, I heard of a sporting journalist at The Sun in Sydney (I think) who had been using the first-ever spreadsheet, Visicalc, to write news stories. As I pondered why, it hit me that he was using this as a way of juggling paragraphs, just by numbering in another column and using the SORT function. If you want to insert a paragraph between number 4 and number 5, your new para is given the number 4.5—and now you should be able to see what got me started.

There was more to it, though, once I had the general idea. At other times in the planning, I might wish to bring together everything that happened on a given date or in a given year, or at a given place. At another time, I might need to review all of the items relating to (say) theft. Right now, most of those are still linked together in this example, because part 40 of what is chapter 7 for now is mainly about crime. Later on, some of the pieces will be switched to other chapters or parts, just by changing a couple of values and sorting again.

Then there was the advantage that I could store the exact reference for later back-tracking, but there are also all of the other tabs that you can see down at the bottom of the screen-shot. I store all of my references, along with where I heard about them, where they are located, and comments about their usefulness. I also store all of the bits, which is why there is a tab labelled "discards".

One of my basic rules is that nothing is ever ditched, because I have regretted such decisions far too many times. Even if something is no use in the current book, it may come in handy later, like most of the lines that currently sit in this planning sheet.

As a rule, the spreadsheet starts at least six months to two years before I start writing the book. I have a rough chapter structure laid down, and I work within that, but most of the time, that will change entirely during the writing.

I have been trying my hand at historical fiction, and the results are less than satisfactory at this stage, mainly because I need more historical detail than I can track down, so the project is on hold. The plots for the eight planned books are all in spreadsheets, but I also have a record of the characters recorded under 12 headings: surname, Fname, Details, born, eyes, hair, height, weight, education, character, characteristics, accent. Each entry also records which books each character appears in, which helps to remind me of who has been where and done what, because the series required me to write across all eight titles at once.

So if you want to write, maybe you can do worse than learn how to subvert the spreadsheet and use it in a way that it was never intended to be used. You don't have to do it that way, but if your mind works like mine, you may find it remarkably convenient.

In the next three blogs, I will go into some rather hair-raising detail.

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

This is the first of a series of four blogs about getting lazy-smart, using computers, and they all reflect my background as a hacker from a time when hacker was an honourable occupation. The precursor to this was a three-part series which began with Effective Science Writing, Part 1.

These are the others, and they will, in due course, be made into links:

Practical use of macros and spreadsheets;

Macro tricks in Word, part 1; and

Macro tricks in Word, part 2.