We can’t have a new year without a new spreadsheet, right? I even managed to make some minor changes to the one from 2017 even though I thought I was out of ideas (and time).
Before you get the spreadsheet, please read:
- This spreadsheet is read-only. You have to make yourself a copy via File > Make a copy. You will not get access to my own one. (The number of requests I get each year is frustrating AF to be honest and I’ll blatantly ignore those without explaining how it’s done instead.)
- Not everything in the spreadsheet is self-explanatory, so please read this manual! (same as with (1), I get way too many questions every year about things that are all explained already)
For those of you who have already been using my spreadsheets from the last few years, especially the one from 2017: the 2018 version is almost the same but handles authors differently, so please read in the manual how those stats work now, if you want to use the new spreadsheet!
I’m not going to explain much about the spreadsheet because that’s all in the manual linked above. Just some general hints and tips:
- Date format: MM/DD/YYYY
- Number format: decimals with a dot
- Duration as HH:MM:SS (audiobook length), typing HH:MM will be enough! The spreadsheet adds the seconds on its own
- Challenges aren’t part of the spreadsheet, but you can track them via tags!
Have fun with the filter tab! You can get a lot of stats with that one. Some suggestions:
- get the average rating for a genre by only filtering by genre
- get the average rating of books released this year by only filtering by release year
- combine format + status to find out which format is used most for reads/rereads (e.g. I mostly use audiobooks for rereads)
- tag books with “diverse read” and combine with a genre and/or audience
If you have any problems, feel free to ask! (just make sure it’s nothing basic that’s covered in the manual please).
Open the spreadsheet and make yourself a copy via File > Make a copy.
Rachael Turns Pages
Thank you for creating it. I didn’t use it in 2017, but I’m excited to return in 2018.
thank you! your last year version was so helpful to me, but honestly i didn’t get to finished D: 2018 will be different, I KNOW IT
when i download it on my computer there are some things that need to be fixed and when i changed the year to 2017 it shows in some overview cells “#VALOR”
on google drive works beautifully but on desktop no so much D:
this spreadsheet is not supposed to be used with anything but google drive. there are many functions that only work in google drive.
This looks amazing.
Would anyone who used last years spreadsheet be willing to post a read-only link to it so that I can see what it looks like in action?
I love love love these spreadsheets!! I used the 2017 one religiously. I do have one question; I like to track both when I start and end a book, rather than just the date completed. In the 2017 one, I added a column before date, but I believe that messed up quite a few of the formulas, specifically on the filter and tag sections. My guess is that a column is hardcoded into the formulas and I just need to update that. Do you have any suggestions of what I should look for to make sure it’s correct? I appreciate it!
Yeah, there are a lot of functions that use “query” and google drive doesn’t automatically update the columns inside those queries. You would have to update pretty much every formular of the filter tab. I’ll see if I have the time to make you a different copy today. (would be faster than going into the details I think)
Would I be able to get this version too as I also would like to be able to track both start and finish dates?
Many, many thanks for not only creating this, but for sharing it too as it must have taken you hours to do.
I would also REALLY love this modified spreadsheet with the start and end date. Fingers crossed, but totally OK if you can’t ♥
Thank you so much for potentially putting this together!!
Thank you for doing this and making it available for us! I used it in 2017 and it was awesome.
Amber @ Books of Amber
THANK YOUUUUUUUU <3
Thank you for making this again this year too! It’s great! Just one thing, you cannot add the gender of the author or maybe I just can’t find it?
Ok, never mind I missed it! Sorry delete the comment please!
a silly question but… the date column is for finished or started date?
when you finished it!
Thank you so much for the Spreadsheet!
I’ll be hopefully be more diligent in filling it out this year compared to last.
Thank you so much for creating these – used it in 2017 and was so happy to see that you did one for 2018 as well. :)
Thank you for sharing this. I will have to plot out my year in the spreadsheet and see if I am surprised or not at the end of 2018. =]
JJ @ This Dark Material
I just found this through Elgee Writes’ Sunday Post and oh my god, it’s a spreadsheet after my own heart!! Thank you so much for sharing this and your instructions too. Already I can’t wait until tonight when I can hunker down with my meager January reading stats to get this all set up ♥
Evelina @ AvalinahsBooks
This is so cool :) I track the majority of these through goodreads, but I’ve got my own sheet system – basically, mostly for hauls and ARCs because I can’t track them very easily otherwise. I don’t know where I’d be without them :D also, currently working on a color coding system for ARCs and their “when to read” dates :) so they’d turn a certain color when the dates approach. Although the current problem I’m having is that conditional formatting (the color) has to be coded for every single cell, if it’s based on the same cell. Or I just haven’t found the right scripts for that yet :D
But yes. Love of spreadsheets. SOOOO glad to see another person who understands :)
What exactly are you trying to do with the color formatting? I might be able to help out!
Evelina @ AvalinahsBooks
I’ve already done it (the hard way) in a certain spreadsheet… it’s kind of hard to explain, so maybe I won’t bother, but thanks anyway :)
I’m always up for a challenge regarding spreadsheets, so let me know when/if you change your mind ;D
Evelina @ AvalinahsBooks
Haha, well… Basically, I was trying to get it to mark a certain box (with a date) yellow, green or red, depending on whether that box is before today, after today or like a month away from today. The problem is that the list is very long, and apparently google sheets wants you to do every single conditional formatting box by hand – you can’t just drag, because it will do it based on the same box. And I need each box to color itself according to the date in it, which means I’d have to change the formula in every single box’s conditional formatting window. I’ve done this with a shorter spreadsheet for work, but like… no way am I coding in three separate formulas for a list of 50+ items. There’s probably an easy way to do this with a script, but I don’t do those. And I would have done it like… Every ARC has a line, and one column is for the publishing date. So if that date is in the past, it’s red, if it’s a month from now or closer, it’s yellow, and if it’s farther away, it’s green. But since I couldn’t find a way to do that without murdering myself, I just dropped it :D
Sorry for only replying now, but that’s actually pretty easy to do!
Let’s say all your dates are in column B, then open the conditional format rules window and set the values like this:
Apply to range -> “B1:B1000”
Format cells if… -> “Custom formula is”
Now the three formulas you need to use are:
for dates before today -> “=AND($B$1:$B<>“”; $B$1:$B
for dates more than a month (30 days) in the future: “=$B$1:$B-TODAY() > 30”
damn, those formulas get messed up in the comments, I’ll drop you an email :D
I’m wondering how to get DNF books to count toward the amount of pages read but not come up as the shortest book of 2018? For example, I DNF’d a book at page 50, so I marked 50 pages read on the read tab, but the book is also coming up on the stats page as the shortest book read, when that’s not really accurate – the shortest book I completed is really a novella with 103 pages. Any advice would be appreciated! Thanks for creating this and making it available for everyone!
it’s currently just using the MIN function on the pages column. If you want to exclude DNF books, you would have to filter the rows first to only use those of books that are not DNF, so some kind of MIN(FILTER(…)) function instead
Thanks! May be beyond my skillset but I’ll try!!