Freak in the Google Sheets: Building a Writing Tracker

As you may or may not have known, there is a fresh book coming from yours truly in 2025 titled: The Art of Bullshit Hunting. As you can imagine, this will be a deep-dive into many of the topics we are covering here on this very Substack.

This is now my third book, with two previous ones using Python to write hacking and reverse engineering tools, both from No Starch Press. Although much less technical in nature, we are happy that the Art of Bullshit Hunting finds its home with the same great publisher, renown for their kick-ass nerd material. Through writing these books, guiding other authors (poorly), and having suffered a lot, I have learned a few things along the way about the writing process, how to manage it as a neurodivergent and how to stay motivated.

When my proposal was accepted this year, the first thing I did was setup a spreadsheet to help track my progress and to make it easy for me to “budget” my time and know whether I am on pace or not. I immediately saw value in it as soon as I started using it and I genuinely feel like it’s a tool I can use to keep some control over the marathon that is writing a book.

I figured I would show you how I set this tracking sheet up, how I use it and hopefully give you ideas on how you can customize it for your own uses. You can make a copy of it for your own use as a writer or as a learning tool to understand Google Sheets a bit better.

I encourage you to of course handcraft it by following along.

What You Will Learn & How You Will Learn It

1. How to structure a custom spreadsheet to track data the way you want.

2. How to write formulas of a variety of difficulty.

3. How to write conditional formatting rules to colour cells based on their values.

4. How to break down complex formulas to understand how each step works.

There is also a 10:09 walkthrough video above, complete with a smooth, ambient beat, my Canadian accent and kung fu sound effects. You read that last part right.

You also have a handy button to make a copy of my spreadsheet so you can tinker and follow along yourself.

Let’s get started!

Setting Up Your Tracker

When you write a book proposal for a publisher, one of the things they request is an estimated page count for the book and the number of chapters you intend to write. In my case I had set down roughly 16 chapters and 400 pages for the Art of Bullshit Hunting. Depending on your publisher, the layout method, fonts and styles, for example, you can have a variance for number of words per page but we use 350 words per page for all of my calculations below.

Keeping this in mind, let’s take a look at my tracker in it’s entirety and then break down each section and how I created it.

Feel free to watch the video above now, later or never. I find all of our readers have different learning styles. You do you, boo.

Let’s now dive in deep to the belly of the beast.

Figure 1 - The finished writing tracker.

As shown in Figure 1 above, in the first section (1) we have all of our chapters laid out. I have blanked some of them to keep an air of mystery, but all 16 have been entered and planned out in a Google Doc that I continually update as I have new techniques or ideas for chapters that I haven’t written yet.

In the second section (2) we have calculated values that show us whether we are on schedule or not, how many writing days remaining in our deadline and some completion statistics.

The final section (3) is where we are punching in our configuration information such as the start and end dates and our proposed number of chapters and pages. These values are what will be used to calculate whether we are on schedule or not.

Let’s break down each section separately, and the key formula cells I have highlighted in purple as shown in Figure 2 below.

Figure 2 - Starting formulas for tracking word count per chapter.

Formula Code:

= ROW() - 1

What this does is take the current row number, subtracts 1 from it and then prints it. This gives us a nicely incrementing chapter count as we are adding more rows and we don’t have to worry about going back and adjusting numbers if we change anything. We subtract 1 because our header line sits on row 1, so Chapter 1 is actually on row 2.

In the Words (C) column we are entering in the number of words we have written. I do this at the end of every writing session, no matter how short or long. Whether I wrote 10 words or 1,000 words, I punch it in. Progress is progress!

Now let’s calculate the current chapter completion percentage so we can keep track of our current chapter progress.

In the Completion (D) column it is taking the number of words that we have written so far and dividing it by the number of words per chapter, a value that we will be calculating shortly when we cover the Pacing Configuration section of the spreadsheet. Feel free to mark this cell red for now, as I show in the video and we can return to it later.

The Pages (E) column is calculated by taking the number of words written and dividing it by 350, a value that is an estimate from my publisher and previous books.

Formula Code:

= C9 / 350

Note that the formula above is contained on row 9, if this formula was entered into D10 (row 10) then it would be: = C10 / 350

Now let’s move on to the Pacing Configuration section where we’ll have some data entry cells, followed by some formulas, as highlighted in purple in Figure 3 below.

Figure 3 - Formulas used in the Pacing Configuration section of the tracker.

In the first four rows of this section we are setting up where we can enter our various settings so let’s explore each of them briefly:

Start Date (H12) - the date you started writing or plan on starting writing.

End Date (H13) - the estimate due date of the full manuscript.

Chapters (H14) - the number of chapters you estimated.

Target Pages (H15) - the target page count for the entire project.

---

Now we have three formulas that will calculate some totals for us based on the values we have entered into the Pacing Configuration which will give us Total Target Page Count, Target Word Count Per Chapter and Words Per Day values.

Formula Code:

= H15 * 350

The formula takes the total number of pages and multiplies it by 350 to give us an estimated total word count for the entire book. This is what will allow our spreadsheet to track our progress down to the word level. Next let’s enter the formula to calculate the number of pages per chapter.

Formula Code:

= H16 / H14

We are taking the total number of estimated words and dividing it by the number of chapters we intend to write. This will give us per-chapter estimates so that we can keep an eye on our chapter completion % as we are writing. It is definitely critical that you aren’t writing a bunch of unnecessary fluff as much as it is that you are fulfilling your obligation to produce the words you said you would. Now let’s look at the final formula to calculate the number of words per day that we need to be writing to stay on pace.

Formula Code:

= (H16 - H6) / H4

Here I am subtracting the total number of words I have written H6 from the total number of words for the book in H16. Then we divide that by the total number of days remaining in our writing period H4. We have not yet coded the formulas in the remaining section to derive the value of days remaining so let’s move up to the final section of our tracker.

---

Formula Code:

= (H13 - NOW()) * (5/7)

Here we use the NOW() function to get today’s date and then we subtract it from the end date. This will give us a number of days between the two dates. Neat, right?

We then use a modifier that will only count “workdays” as writing days, we do this by dividing 5 (workdays) by 7 (all days). Then we multiply this against the number of days to scale it appropriately. Unless of course you want to include weekends as writing days, then you can remove this last part of the formula or you can choose to only write 3 days a week, you can change that value to 3.

---

The final two formulas to calculate the Total Pages Written and Total Words Written which are simple SUM formulas that add together each value in the range of cells that you specify:

Formula Code (H5):

= SUM(E2:E17)

Formula Code (H6):

=SUM(C2:C17)

Now we can calculate a completion percentage that allows us to see our progress on the entire book over time. I find this really useful to keep me motivated to keep writing as that number climbs over time.

Formula Code:

= H6 / H16

The final formula we need to write is the formula that determines whether we are currently on pace or not. This is our most involved formula so we’ll break it down:

Formula Code:

=IF((H6-((NOW()-H12)*H18) > 0),"Ahead of Schedule","Behind Schedule")

It’s a doozy! I warned you we would get a bit freaky around here. Let’s walk through this step by step in the order of execution so that it is much more understandable:

1. Using the NOW() function we get today’s date and subtract it from our writing start date. This will tell us the number of days that we’ve been writing so far.

2. We multiply the result of our date subtraction by the Words Per Day (H18) value, to give us the total number of words that we should have written by now.

3. We then subtract the Total Words Written (H6) from the result of our multiplication in step #2. This will give us a positive value if we are ahead of schedule (number of words written is higher than the pace amount) or behind schedule will give us a negative value.

4. The previous 3 steps are wrapped inside of an IF() statement. This IF statement is testing the result of our first three steps is great than zero or not (> 0). Again to reiterate, greater than zero means that we have written more words so far than the pace requires, thus are ahead of schedule. Less than zero means that we have not written enough words to match pace for our completion date.

5. If it is greater than zero then the value “Ahead of Schedule” is printed. If the value is less than zero the value “Behind Schedule” is printed.

Perfect. That pretty much wraps it up the heavy lifting. You should now be able to enter your own start date and time, estimate page counts and whether you are on track or not. Try playing around with the numbers to show how the “Ahead of Schedule” or “Behind Schedule” messages work. Try setting your start date to 2022 for example to see how your spreadsheet responds. Now let’s add one more small tweak before wrapping up.

Conditional Formatting Rules

As you see in my sheet, I have one more small tweak that will highlight the top cell green if the message is “Ahead of Schedule” and will highlight it red if the message is “Behind Schedule”. How we do this is with Conditional Formatting rules, which are ways for us to say: depending on what is contained inside this cell, I want to change how this cell is visually presented. You will use this feature time and again in your Google Sheet romps, so it’s useful to try it out.

Let’s walk through this step by step:

1. Select the G2 cell where our ahead of schedule/behind schedule formula lives and click the Fill icon.

2. In the following menu click Conditional formatting.

3. This opens the conditional formatting panel, from the format dropdown select: Text is exactly which will match exact text that is in the cell.

4. The text you want to match is: Ahead of Schedule as shown.

5. You pick the colour and style that you want for this rule, in this case if we are ahead of schedule we want the background colour to be green.

6. Click Done to save the rule.

Excellent, this shows our rule being successfully added. Let’s click the Add another rule (1) link to add one more rule that will color the cell red if we are behind schedule:

Pretty straightforward at this point, we set the matching condition (1), the value we want it to match (2), set the colour to red (3) and then click Done (4) to implement the rule. Your final result should look like this:

Beautiful! If you made it this far you are truly a spreadsheet kinkster and I am here for it. This spreadsheet is now a weapon against procrastination, a lack of motivation (dopamine) and it will help answer the ass-clenching question of : “How much work left on your book do you have?”.

Clench no more, my writing queens, you will now be able to answer with confidence that you are behind schedule, ready to quit and have the math to prove it!

I am only partially kidding.

Homework

Here are a few tweaks or improvements you could try making to the spreadsheet:

1. We have to keep entering the 350 numeric value in the spreadsheet formulas for converting the number of words into a page count. If we wanted to change this value to 300 it would be a pain in the ass because we would have to find and replace each one. What is an improvement you could make so that you could change the number in one place and have all of the values in the spreadsheet update to use it?

2. How could you apply conditional formatting to the Completion Percentage value such that the background colour of the cell starts out “cold” (blue) and gets “hot” (red) depending on how close to 100% it gets? Could you do the same with the pages per day pace?

3. Often us neurodivergent people need a bit more pressure to push us to do something. How could you calculate the current “pace” value for the number of words that should be written? If you are ahead of schedule, every day you should see this number creep up closer and closer to the total number of words you’ve written which should start to create a bit of a sense of urgency to “stay ahead”.

I find doing small little challenges like this helps me to stretch my legs, remind me how to use some of the formatting tools and also helps me to build more solid and resilient sheets in the future.

Questions? Comments? Need a hand with anything? Hit me up!

— Justin