Dax Murray

I have spent several months trying to find the perfect method of keeping track of where I am in editing my novel. I mostly now use TickTick's Kanban feature to move stuff back and forth in the swim lanes, and TickTick's Gannt timeline to plan what days I will do what. But that still leaves me with a “are we there yet?” for “when will I be done? How much more must I do?”

So I started looking for integrations for TickTick with various data viz solutions. And apparently, the TickTick API is... lacking. And the integrations in Zapier and IFTTT were not that stunning, and I couldn't find a way to make it a data source for excel.

So, instead of, you know, editing, I set about to make the perfect novel editing tracker complete with :jazz hands: progress bars :jazz hands:.

Home Page

The first thing I wanted was an at-a-glance of how far I was both overall and in each step of the process. The only numbers a user is supposed to enter on this page are the start date and end dates. The rest of the numbers are all pulled as references from the other sheets. Set the due date overall, and for each step/section. The date fields on this page are the Source of Truth for all of Time.

The second thing I wanted was a forecast. If I was only doing two scenes a day on average, how long would it take me to finish? Would I hit or miss my deadline? What would the deadline be if I kept going at my pace? This is what the “moving deadline” captures. If you complete your tasks at the current daily average, what day will you finish? Have you been super productive and now you might finish ahead of schedule? Or are you now super behind? It also shows what your daily average should be at this point if you still want to hit your target.

The Progress bars all pull from their respective tabs. All “Work Tracking” pages have A1 Cell displaying the overall progress for that section, and that is where the Home page fetches them. If you add new sections, make sure to either leave A1 blank or make sure to put the completion rate in A1. The Overall Progress Bar checks every page between the “Home” page and the “README” page (the last page) to see if a value exists in A1, if it does, it adds it to it's calculations. It averages together all sections and gives you the results. It isn't weighted, I'm sorry. Maybe V2 I will find a way to add a custom weight attribute for certain sections. But V2 isn't until I'm done with my current novel!

The section at-a-glances are also all pulled directly from the section pages themselves EXCEPT for the Due Dates. You add these in. The “Total Tasks” is calculated by searching all of the “K” column after K17. So if you add a section, make sure that the “total” column is in K, and the Total Tasks field will still calculate properly. The same for the Tasks Completed, it searches the I column.

The purple section pulls from the Daily Tab. These values have no bearing on the progress of the project. I wanted them to track how much writing/editing I was actually doing, and how many scenes per hour I was able to edit and what days I spent the most amount of time writing/editing.

Daily Page

I created the Daily tab to track how many scenes I could edit per day when editing, and how many words I could write per day when writing. This page has no impact on your completion rate, it is a rather standalone page, with only a few of the values being surfaced on the Home page. It doesn't affect any progress bars, just nice info to help you figure out what days you were more or less productive, and maybe help you identify what might be impacting a slower or more productive day or week.

If you want, you can make the “Count” column into “Word Count” or “Task Count” or “Pages” Count, or anything else you want to track in comparison to Hours.

For the calculations, it pulls the Hours total from Row 39. So if you add more months, as long as the Hours total is in Row 39, the formula will still work. Same for the Count totals and Row 40.

The formulas for finding days with highest hours/counts all span 31 days. So if you decide to make the table that is currently September into October, it will still work.

The Edit Pages

These pages are where you may or may not notice how much I love progress bars.

In A1, you will see the overall progress bar for whichever round of editing it is. It comes with 25 chapters each with 6 scenes. However, you can add as many additional chapters as you want; the only limitation is your computer's CPU.

For these pages, a cell with 0 and a blank cell are completely different things. In the top bar, the “Total Scenes” number is calculated by looking at Columns C – 3 from Row 3 downwards. If a cell has a number in it, it counts it as a Scene. For Scenes completed, it looks at C – 3 from row 3 down and looks for “100”. So, if a chapter only has 5 scenes, just don't put a number in the corresponding cell for scene 6. If you have more than 6 scenes, simply insert another column somewhere between C and 3, the formula will update.

The Days Remaining her are based on the Due Date you set on the Home Page for that edit specifically, not the overall due date.

These progress bars are made using conditional formatting. I like color, too. So, the “Complete” column also uses conditional formatting. These conditional formats are applied when the criteria are met, so if you add Chapter 26, the corresponding cell will go from grey to red when you add the first 0 into Chapter 26 Scene 1.

Other Eyes

This is set up to track how far I am in reading the feedback and applying the feedback from various haters readers. This page behaves very similarly in terms of functionality to the Edit Pages.

It pulls the Due Date from the Home Page, it calculates how many “tasks” based on the number of cells between C and E, and calculates completes based on how many of those cells are set to “100”. It has the same conditional formatting to make the progress bars and color coding.

Pre Pub

This is basically copy-pasta of the Other Eyes page. I use it when compiling my manuscripts in Atticus and doing my final proofreads.

Final Words

I cannot stress this enough: do not delete the README. It's a bookend so to speak to mark which sheet is last and its existence as the last page in the workbook is what keeps the overall progress bar working. Also, don't move Home from the first tab, either.

This is “for sale” as pay-what-you-want (yes, even free, but I am poor and disabled and legally required to live in poverty, so a dollar or two for it would help me out a ton!). You can purchase this novel editing tracker spreadsheet at my new storefront. And never fear, the workbook has a very nice readme that is just as detailed.

If you are interested in the joys of Excel, I gratuitously used XLOOKUP, COUNTA, IFERROR, QUOTIENT, COUNTIF, DAYS, and TODAY while putting this together, as well as careful planning and standardizing cells across pages.

Don't forget to subscribe if you want my next post delivered straight to your inbox!

If you want discounts on all of my books as well as my products and services, consider joining my newsletter. Not only will you get discounts, but you'll also announcements about new releases as well as exclusive bonuses and sneak peeks.