Unusual Uses of Excel for Writers

Regular readers know I’m a little spreadsheet crazy. I’ve offered you a spreadsheet for time and motion studies and another for tracking your productivity. And I’m not the only one, because you’ll notice that Michael Hyatt’s ideal week is plotted on a spreadsheet.

When I wrote about tools, I mentioned some of the things Excel spreadsheets can do, and noted that there might be a whole other post in that.

To-do Lists

If you format your Excel spreadsheet using one of the table options under the Tables tab of the Ribbon, you’ll see arrows appear in each column heading. This lets you sort the list by any column. So perhaps instead of priority, I want to sort my list by date. It’s a two-click operation: click on the arrow, then on “Ascending.” Done.

Sort by date

Sort your to-do list by date or priority.

I like to have a “Done” column that I can put a “Y” in when a task is complete. This helps me track my small wins. I’ve also been known to use the square root symbol √ as a check mark, because it’s easy to type (on a Mac, OPT + v; on Windows, ALT + 8730). You could also insert a proper check mark from your symbol chooser, ✔︎ like so, and then copy and paste it as needed.

If you prefer, you can draw a line through your completed items by selecting the cells, opening the Format > Cells dialog, clicking on the Font tab, and checking the box for Strikethrough.

Use strikethrough formatting to cross things off your list.

Use strikethrough formatting to cross things off your list.

Calendaring

This is one of the main things I use Excel for, because I found that when I put all my work projects into my Calendar app, it got to be overwhelming. And also hard to read, because the size of the daily squares in Calendar is fixed. So now I use Calendar only to keep track of events that require me to be somewhere at a certain time. For work I do at my desk, I use a spreadsheet that looks like a calendar.

Calendar merge cells

Vertex 42 has a ton of Excel calendar templates. My favorite is the Perpetual Calendar Template, which has a yearly calendar on the first page that can be set to almost any year. Then each month of the year is a separate page in the workbook, as you see above.

You can see from this screen shot that I use merged cells to indicate multiday events like the Realm Makers conference or that hypothetical editing job I hope will come in. To merge cells, select them and then open the Format > Cells dialog box, click on Alignment, and tick the Merge Cells box. I assigned a keyboard shortcut (Tools > Customize Keyboard) to this command to make it even easier.

I schedule weekly tasks, like coordinating the courses for The Christian PEN: Proofreaders and Editors Network, as well as non-work projects that will take a big chunk of time, like planning a Sunday school lesson for the class I’m substitute teaching one week.

A calendar like this can also be used for tracking the timeline of your novel. Since it’s a perpetual calendar, all you have to do is enter the year in which your story takes place, and the template will show what day of the week dates fall on. It will also generate the holidays for that year. It can go into the future if you’re writing science fiction, and into the past for historical fiction.

Note: You can also get future and past calendar information from the Time and Date website.

Project Management

If you’re not familiar with them, Gantt charts can be a little intimidating. But they are ideal for showing multiple things happening at once. At the PEN, for example, we have five courses scheduled for the fourth quarter. They all have different start dates and different durations, and for two weeks, they will all be happening at the same time. This would be difficult to plot on a plain old calendar.

Gantt chart

A Gantt chart may be overkill for most writers, but if you are a self-publishing indie author, it could help you keep track of various stages that might be happening simultaneously, such as cover design and copyediting, or things that have to happen in succession, e.g., page design must be completed by the 21st so proofreading can begin on the 23rd.

At the time I downloaded my Gantt chart template, it was available from Microsoft. Unfortunately, since they’ve moved everything to Office Online, I haven’t been able to find a downloadable version of this template on the official site.

Fortunately Mr. Ruley, an engineering teacher with the Pearland school district in Texas, has put together a page of Gantt chart tutorials and templates. Way at the bottom of the page you’ll find file ts102887601.xltx, which is the Microsoft template in question. You can also get Gantt chart templates from Vertex 42.

Next time, we’ll look at how an Excel spreadsheet can help organize the developmental editing stage of your book.

About Kristen Stieffel

Kristen Stieffel is a writer and freelance editor specializing in speculative fiction. She's a member of the Editorial Freelancers Association, Christian Editor Connection, and American Christian Fiction Writers.

Leave a Reply

Your email address will not be published. Required fields are marked *