Novel timeline tool in Microsoft Excel

In November 2013 I drafted a mystery novel whose plot spanned centuries and generations. I needed to track what was happening with different characters and historical developments over the decades. It was National Novel Writing Month, so I didn’t have time to learn a new tool. I turned to an old standby, Microsoft Excel. Download the example here. (This spreadsheet works MUCH better in MS Excel for Windows, I know because I’ve tried it in Excel for Mac. Sorry about that Mac folks.)

Excel_timeline_bigHow it works: In the Events tab, the basic timeline of the novel (X-axis on the chart) is in column A (by 5 year intervals since it spanned so much time). Each element of the novel I want to track (for example, plot point, character lifetime, historical event) has its own set of columns. The first column contains the element’s position on the timeline chart (height or Y-axis), with entries in each year cell. The second column either describes what happened or contains the character’s age at that point in time.

I used negative numbers to put “reference” timelines such as history and ancestors at the bottom of the chart. I used positive numbers to put active plot and character timelines at the top.

In the chart, I formatted the data series (lines, markers) as I wished and used the = function to have the data labels fill in the text from the Events tab. (This is a tedious manual process, so I only did this for some data points.) The resulting chart looks rather like the “swim lanes” in project planning.

There are tools out there that do this more efficiently and gracefully than I did. In Excel, it is possible to use dual-axis charts, pivot tables and error bars in creative ways to generate a nice-looking timeline. This quick-and-dirty method was enough for me to track what was happening along different dimensions of the story during the same time frames. If it helps anyone I’d love to hear about it.