Excel 2016 – how to fill a series… backwards

I’ve used Excel for a very long time, but I literally just discovered this neat trick so I’m going to share. 🙂

Ok, to start at the beginning, I started an Excel spreadsheet to create a super accurate timeline of the Vokhtah story. To track the number of days of the timeline, I created a column and ‘filled’ it with a sequence of numbers. Most people know how to do this but I’ll cover it nonetheless:

Step 1 Type in two consecutive numbers and then select both together:

Selecting these two consecutive numbers tells Excel the step order – i.e. 1 + 1 = 2, 2 + 1 = 3, 3 + 1 = 4 etc. If you typed in 10 followed by 20, Excel would know the step order was 10 + 10 = 20, 20 + 10 = 30, etc.

Once Excel knows the step order, clicking and holding the small square [as shown below] allows you to drag that step order to as many cells as you wish:

In the screenshot above, I dragged the handle down to the 7th cell, filling all the cells with the correct sequence of numbers.

So far so good? Stay with me. This is where it gets exciting. Being able to fill a series of cells with consecutive numbers was perfect for tracking how many days there were in the timeline, but that didn’t help me work out on which calendar day the journey/story began.

To put this as simply as possible, imagine a task takes you 10 days to complete, and you finish it on the last day of March [which has 31 days]. Now imagine if someone asked you which day of the month you started the task. If it’s only a few days you can easily count backwards, but if it’s more than a few days, you might have to drag out a calendar to work it out.

On Vokhtah, there are no months per se. Instead, there are 4 seasons which have an irregular number of days. Book 1 of Vokhtah takes place during the season of Tohoh, which has 100 days. To find out which calendar day the story began, I needed to do a backwards fill. This is how I did it.

1. Click in a vacant cell.
2. Look at the top right corner of the Excel toolbar and click the small arrow next to the ‘Fill’ icon:

This will display a small, drop down menu.

1. Select the ‘UP’ option from the drop down menu.
2. Now type the last number of your desired fill sequence in the cell.
3. Next, type the second last number of your desired fill sequence in the next cell up.
4. Select both cells.
5. Click-hold-drag the small square box UP to fill the cells from last to first [or any point in between]:

In the example shown above, I only dragged the small square as far as the number 4. In my real spreadsheet I dragged it from 100:

to Tohoh 42 – i.e. the day of the season on which the journey/story began:

I know a lot of writers out there will be shaking their heads right about now. “Use a spreadsheet? No way!”

To be honest, as a pantster, I would never have thought of using a spreadsheet to work out how the story should progress. But once I started writing books in a series, I had to make sure that info. in the first book married up to info in the second and third books. And that’s where Excel comes in because it allows me to outline in reverse.

So there you have it. Outlining in reverse aided by a backwards fill from Excel. It’s been a good day. 🙂

cheers,
Meeks