NOTE: Several adjustments have been made to the template (as of Dec. 18, 2018). Novellas were inadvertently left out of the Results tab and the drop down for POCs in the Tracking tab listed “author/artist & MC” and has been changed to “author/artist & main character” to ensure correct tabulation. If you have already saved a copy of this template, you will need to make these adjustments or resave the updated version.
When I initially set out to create a reading log spreadsheet thing two years ago, I never intended for updates to become a regular, annual event. But each year of tracking my reading and all the juicy data that comes with it gives me a few more improvements to make.
In an attempt to give myself more stats about the diversity of the books and authors I was reading than what Goodreads could provide, I created a Google Sheets tracker two years ago. Building upon last year’s spreadsheet and feedback from the many people who’ve used it, I’ve got a few more updates for 2019’s edition.
Click here to view the tracker. To save your own version of this reading log, click on File > Make a Copy and save it to your own Google Drive in order to personalize it. With some notable changes this year (including a new color scheme), we’ll work through each of the tabs in turn and go through the features one by one. (For reference, here is last year’s post about the tracker.)
This is where you’ll put all the details for each of the books you’re reading. I’ve made a few small but notable changes to this tab. You’ll log title, author/artist (for comics), and publisher. For publication date, date started, and date finished, you can either enter the date in mm/dd/yyyy format or double click in the cell and you can select the date from the calendar. There is now a DNF column so you can track all the books you started, even if you don’t finish them. They won’t be counted in your final tally, but now you can also enter the number of pages you read (or amount of time you listened for audiobooks) before you gave up in the Page Count or Length column and those pages/minutes will count toward your total tally. (However, if you enter all the other information for the books you DNF’d – like Genre, Form, etc., that information will also be counted in the final tallies. It’s up to you if you want to include DNF’d books in those calculations.) Otherwise, just put the book’s full page count or length (in hh:mm:ss format) in their respective columns if you’ve finished the book.
Format still tracks print, digital, or audio, but the Source column has had a few additional option added to the drop down, including Borrowed and Other.
Form has also expanded to include novels, novellas, plays, and nonfiction prose (for book-length nonfic) and short stories and essays have moved over here from the genre drop down.
After some serious deliberation (and acknowledgement that any list of genres will inevitably fall short for most people), I’ve expanded and updated the list of genres available in the drop down menu. Now you can choose from the following:
Crime (which encompasses mystery, thriller, suspense, and true crime)
Current Affairs/Politics/Social Science
I’ll go over how to change these drop downs farther down in this post if you want to change anything on your own tracker to best suit your needs.
Next, the Gender column. In response to some great input, I decided to add a drop down for this edition of the tracker and why I used the options I did. So you have the M and F options, but there is also a M/F option for those books that have two or more authors and/or artists of different binary genders. Then I added a nonbinary option. (If you run into a book with multiple authors in which one is nonbinary, definitely customize to include the M/nonbinary or F/nonbinary options if you need to.) I avoided using the trans label here for two reasons: 1) the trans identifier is covered in the LGBTQ+ column, and 2) by and large, unless the author identifies as nonbinary, a cis man and a trans man are both men. I’m grateful for the trans and enby individuals that provided feedback on the best way to handle this category and, as a general template, this appears the most friendly out of the box. Again, if you want to expand or change the options (or remove the drop down altogether), you are absolutely free to customize your tracker as needed.
From there, the rest of the drop down columns haven’t changed. Age, POC, LGBTQ+, In translation, #OwnVoices, and Reread are all the same as last year.
For challenge tracking, I’ve included Read Harder of course and added Reading Women’s annual challenge (see the dedicated tab for their task list). We love the Reading Women team and podcast, and if you’re looking at adding another reading challenge to your reading goals for 2019, I think this is a pretty great one. Find more details about the Reading Women Challenge here. For both these challenges, you’ll be entering the task number (the number in column A on the challenge tab, not the row number) that the book fulfills in the respective challenge column, which will automatically populate the title and author into the task row on the challenge tab. (Both challenge tabs also now include a completed task tally and a count of how many tasks you have left to go.)
Next is a brand new but oft-requested feature: Ratings! (Yes, it includes half stars). You can select, on a scale of a half star to five stars, your rating of the book.
Lastly, the final three columns on the Tracking tab automatically calculate the total number of days it took you to read a book based on your date started and date finished entries and the number of pages or the time per day (for audio) for each book.
Here’s where all that data gets put to work! You’ll be able to see how many books you read and DNF’d, the number of pages you read and hours listened, and the average number of days per book, pages per day for print and digital, books per month, and hours per day for audiobooks. The number of male, female, and nonbinary authors/artists; number of adult, YA, and children’s books; how many authors/artists and main characters who are POC and LGBTQ+; how many books are in translation and how many are #ownvoices; how many from each source and in which format and form; how many fiction vs nonfiction; and how many published in 2019 and how many published previously, as well as how many published in 2020 (if you have access to early released books; if not you can delete this row). Additionally, you can see how many books you read each month. New for this year’s tracker, you can now track your star ratings and the length of your books (page count for books you DNF’d won’t be included here, so you only see the length of books you finished).
In the C and F columns, you’ll also see the percentage breakdown for each of these categories. And percentages are all well and good, but wouldn’t those numbers be so much more appealing in a visual format? Yes, my friends, there are CHARTS!
Most of the charts on this tab are of the pie variety. You’ll be able to see visual representation for the following: percentage breakdowns for Books finished vs DNFs; Fiction vs Nonfiction; Gender, Format, Age, Source, Publication Date, Genre, and Form; percentage of books with POC and Queer authors/artists and main characters against your total books read; and percentage of books in translation and that are #ownvoices against your total books read.
Additionally, you can see bar charts for ratings, books per month, and book lengths.
Now, important customization tips. If you want change any of the options in the drop downs, you have to make the changes in two places, the Tracking tab and the Results tab, to make sure the new options are calculated correctly. For this example to change genres in the drop down, follow these steps:
- In the Tracking tab, select the first cell under the Genre header (in this case, cell O3), click Data from the menu bar, then Data Validation.
- In Cell Range, add a colon and a capital O so it reads Tracking!O3:O. This will ensure that all the drop downs from the first cell all the way down include these same options. Then in the list of items, add, change or delete any genres, separating each option with a comma (no spaces!). Then hit Save. So if I wanted to split up Sci-Fi/Fantasy into two distinct categories and add Speculative Fiction as an additional genre, I would go from this: To this (note the cell range, the comma between Sci-Fi and Fantasy, and Speculative Fiction added to the end of the list):
- Next, in the results tab, I will change the Sci-Fi/Fantasy label in column A to Sci-Fi and insert a row each for Fantasy and Speculative Fiction. This: Becomes this:
- Now to adjust the formulas in columns B and C. Your original formula for Sci-Fi/Fantasy tracking in column B should say this: In the cell in column B next to your new Sci-Fi label, your formula should now read:
- New rows (like the ones you added for Fantasy and Speculative Fiction) will have a similar formula, but the term between the asterisks should match exactly the term you added in the drop down on the Tracking tab.
- Adding the percentage formula for new rows in column C is very easy. You can copy the formula from one of the other cells in that column and change the row number to match the row you’re working on (for example, changing B45 to B46). For the new Fantasy row, your formula would look like this (noting that row 46 matches in the formula):
Adjusting these formulas in the results tab will automatically update the charts these numbers tie to as well. (If you need to update or adjust data/formulas that feeds the charts for POC authors/artists, POC main characters, Queer authors/artists, Queer main characters, In translation, or #OwnVoices, these are hidden in columns H and I in the Results tab.) You can adapt this process for customizing all your options and make this tracker your own.
If you have questions about any of the formulas or anything else, leave a comment and I’ll try to answer as quickly as I can.
Go check it out and happy data nerding!