Way back in January 2017, I was trying to figure out how to do more with keeping track of my reading beyond the basics that Goodreads could provide. Enter: my handy, dandy reading log on Google Sheets.
I started tracking everything from title and author to the gender, sexuality (if known), race, and nationality of authors and characters. I tracked how much I read (or listened to) and in what formats (helpful since my reading went nearly 100% digital after June). The reading log worked and I kicked my own reading goals in the tuchus: I finished Read Harder AND the Litsy AtoZ challenges, and not only finished 50 books for the year, but beat it by more than 150%.
Some of the categories I tracked worked great—I kept a close eye on my gender breakdown and managed to hit more than 75% books by women for 2017—and some not so much—figuring out an author’s nationality can be really hard. So with that in mind, I took another look at my reading log and tried to figure out how to make it even better for 2018. Thanks to many of you, I got tons of feedback on features you also wanted added or removed.
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 and keep your own log. View the tracker below:
The first part gives you space to log the normal stuff and hasn’t changed much from last year: title, author/artist (for comics), publisher, and publication date. In addition to tracking the date you finished a book, this year I’ve added a “date started” column, which will become important in a minute. Page count and length (for audiobooks) is the same, but you’ll notice that rather than asking you to input the format, genre, form, fiction/nonfiction, POC, LGBTQ+, etc. categories by hand, I’ve given you drop downs that are pre-filled, making the chances of making an inputting mistake harder. (I did not do this for gender because everyone may have a different method of tracking and categorizing gender. The results formula on the next tab is set for a M/F binary, but it is easily changeable.)
The genres category has a broad list in the drop down:
I’ll explain in a minute how you can add or subtract from any of these drop downs to personalize your own reading log.
In addition to the Date Started column, I’ve given you a few new categories: Source (where you got the book); Age (tracks whether the book is adult, YA, or children’s/middle grade); In Translation, #ownvoices, and Reread (all of which are yes/no trackers). The POC and LGBTQ+ drop downs are yes/no trackers as well, but they give you the option to select whether it is the author/artist, main character, or both that fits that distinction.
Here’s how to change any of these drop downs to fit your own needs. Let’s say for example that you want to remove “ARC” from the list of sources because you don’t receive them from publishers. Click on the I at the top of the column so the whole column is highlighted. Then in the menu bar click on Data > Data Validation.
In Cell range, make sure the range says I4, not I1 (or whatever column letter you’re working within), otherwise your top couple of header cells will be included. Then under Criteria, select List of Items, and type the entries you want to include in the list separated by commas (with no spaces). Then hit Save. This method works for all of those columns with a drop down.
Now for some more fun stuff! Let’s skip over to the last three columns: Total Days, Pages Per Day, and Time Per Day. Total Days is where that Date Started column will come in handy. Total Days will automatically calculate how many days it took you to read a book based on your start and finish date. So if you start a book on Jan. 3 and finish it on Jan. 6, it will show 4 days. Based on that total, the average number of pages you read (or the average amount of time you listened to that audiobook) in a day will be calculated in the Pages Per Day or Time Per Day columns. Nifty, huh?
Jumping back to the Read Harder column, rather than just mark whether that book is a challenge book, you’ve got a little more robust tracking this year. Over in the Read Harder tab, you’ll find the list of tasks for the 2018 Read Harder challenge, each numbered in column A. Note which task number your book fulfills and then back in the Read Harder column on the Tracking tab, type the task number (make sure you’re using the number in column A, NOT the row number). If you go back to the Read Harder tab, voila! The title and author of that book has populated for you.
Just like last year’s log, the power of the spreadsheet isn’t necessarily in the Tracking tab; it’s in the Results tab.
All of that data you tracked on the main tab is put to work here. You’ll be able to see how many books and pages you read, number of 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 and female 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 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 2018 and how many published previously. It is ROBUST.
Because the Tracking tab has mostly pre-filled drop downs, you no longer need to worry about making sure you’re entering the right thing to make the formulas work. (Just make sure that if you change any of the drop downs, you’re adjusting the formulas here to keep the numbers accurate.) There are a few notes to be aware of in terms of formatting:
To track publication date, date started, and date finished: Enter date in either “mm/dd/yyyy” or “Month Day, Year” format.
To track audiobook length: Enter the length of the book (i.e. 9 hours, 8 mins) in the hh:mm:ss format (in this case, 09:08:00).
To track author gender: Enter M or F. If you have a male/female author/artist team or male/female co-writers, enter “M, F” (separated by commas) and one each will be marked.*
For any field where the category doesn’t apply (for example, page count on an audiobook or in translation), leave it blank. If there are columns you don’t want to include, you can just delete them, and the formulas will readjust appropriately.
For even more of a breakdown, you’ve also got the ability to see how many books you read each month (based on the date you finished the book) and in each genre.
Here’s where it gets fun: as you start to read some books and get some totals populating into those fields, column D in the Results tab becomes a hidden gem. See, column D is your percentage column. Not only will you be able to see how many female authors you read, you’ll be able to see what percentage of all your books’ authors are women. You’ll be able to see what percent of your reading is historical fiction or romance or sci-fi/fantasy. You’ll also be able to see which percentage of books you bought or got from the library. And on and on and on. If you have a question about the percentages, leave a comment and I’ll make sure I clarify.
So go check it out and play around. Make it yours and log away! If you have any trouble with personalizing it and you need some help, leave a comment and I’ll try to troubleshoot.
*Note: As I stated earlier, I used a binary gender categorization because that’s what works for me and because I don’t tend to read many authors that identify outside of the male/female gender binary. However, if you want to track non-binary authors, the formula is fairly easy to tweak. (If you need help tweaking it, leave a comment and I’ll do my best to help you alter it.)
*Author’s Note: The template formulas were tweaked slightly a few hours after the post originally published to better calculate POC and LGBTQ authors, so you may need to redownload the spreadsheet to get the most up-to-date version.