If you own Excel and are looking for a way to store a timeline of events for your novel, this is the guide for you! I’m going to outline two different methods and show how examples tailored specifically to writers. Note that I’m using Excel 365, the latest subscription version.
The First Option
The first method is the easiest I found, which makes use of pre-made graphics. What you want to do is go to the Insert tab, click Illustrations, and then select SmartArt.
From here, you can choose the graphic that you want. I recommend either the Circle Accent Timeline or the Basic Timeline, shown next to it. Both of these are found under Process.
With the Circle Accent timeline, you can use different levels of a bulleted list to show events in different ways. I used three levels, the first one representing the date, the second one representing major events that happened that day, and the third level giving additional details for those events as necessary. Some details here are more vague than they truly would be, in the interest of not giving away more than the basic opening of my book, but I’m sure you can see how you might make use of it for your own novel.
The Basic Timeline works the same way, but shows things in a different format, with an arrow and more bullet points. It might be better suited to more simple timelines than the one I have in this example. If you just want all of the events in order, without keeping track of how many days have passed since certain things happened, you can probably do away with the dates and list each event as a separate, top-level bullet instead.
The Second Option
The second method is more complicated, but, in my opinion, much more useful for long and detailed timelines. It gives so much more functionality than the basic option, and once I figured out how to use it, I loved it.
It starts with putting all of your events into a standard Excel table. Make sure you include headers. This will matter later on down the line. I recommend having at least a column for date and a column for the events. I also added a column for characters and a column for sequence of events within the day, which you’ll also see the usefulness of later.
If you want to be able to insert a timeline later, you should format the date as a real date as opposed to what I did here, however. Go for something like August 17, 2022.
What you want to do next is highlight the table, go to Insert, click PivotTable, and select From Table/Range.
This will open a popup with the range of data (if you highlighted everything, this will be prefilled for you) and a few other options. I recommend just going with the default and hitting OK.
This will open a new sheet, with a little window on the side called PivotTable Fields. This is where your table headers should be showing. Clicking the checkmark next to all of them will add them to a table that gets automatically created for you. You can then click and drag them to create the order that things get shown in. You can now see that my sequence column is ensuring that each event shows in the proper order of the things that happened to that character on that day. I had to click and drag it from the Values to the Rows, so if you’re doing the same thing, you should expect that step as well.
A Grand Total shows up by default, which doesn’t make sense for this type of data. You can get rid of it by going to the Design tab on the top of the screen, clicking Grand Totals, and selecting the option to turn them off.
Now I’m going to show you why I made a column for character. There’s actually an option with this type of table to filter based on whatever column you want. Because I created one for character, I can now focus in on what happened just to that character over the course of the book or over the course of their life if I want to include backstory (and you know I do!).
This is done by adding what Excel calls a Slicer. Go to PivotTable Analyze at the top (make sure you’ve clicked on the table if you can’t see it), and select Insert Slicer.
This brings up a popup that allows you to select the column(s) you want to be able to filter by. I selected character, and just like that I gained the ability to select the one I want to see and have the table update for me. You can also select multiple characters at once using the Multi-Select option, which looks like a little checklist. Or you can hold down Shift while clicking on an additional character.
Now then, I promised a timeline, didn’t I? From this point, you can insert one, although it will work much differently from the first option I showed. Instead of giving you a graphic with all the events listed along it, it actually works a lot like the slicer. It allows you to select a certain date or period of time and focus in on only the events that happened then.
To add one, just go back to PivotTable Analyze and click on Insert Timeline.
The popup will prompt you to select the date column. After hitting OK, you’ll have a little box where you can view by years, quarters, months, or days. You can select the day/month/quarter/year you want or click and drag to select a range. In the example here, I’ve selected only August 17th.
And there’s the basics! There are plenty of other options for you to explore if you would like. For example, you could check out the filtering capabilities next to the Row Labels heading by clicking on the funnel image. If you decide you don’t need these, you can have Row Labels not display at all by turning off Field Headers under the PivotTable Analyze tab. Similarly, if you don’t need to be able to collapse and expand things using the +/- boxes, you can turn that off in the same place.
Conclusion
Feel free to play around and create what works best for you and for your novel. As for me, I think I’m going to use this PivotTable with days of the week and made-up years (because my novel doesn’t take place in the world we know) and seed out all the character backstory information I could ever want. Because my novel doesn’t focus on just one character, this is going to be incredibly useful for me as I try to ensure consistency. Hopefully you’re able to find a method that works for you and the book you’re trying to write as well!
Shannon Fallon 