All About Books

Spreadsheet: Books 2017

Spreadsheet 2017

The year is almost over which means it’s TIME FOR A NEW SPREADSHEET. “Books 2017” is a spreadsheet to keep track of your reading and accumulating of books and gives an intense amount of interesting statistics. (you could use it any year, but I make a new one every year because ALL THEM NEW IDEAS, so 2017 in the title it is.)

NOTE: I’m a lazy person myself so I get not wanting to read a long-ish post about how the spreadsheet works BUT every year I get the same kind of questions, very simple ones, that are explained in the post, so please read at least the following very important parts and look into the complete “manual” if anything isn’t working first before asking. (Do ask away if you have problems of course, I’m always here to help! I just don’t want to repeat myself over and over again if it’s already in the post/manual)

Important to know:

  • You might get asked for permission from a script to access the spreadsheet. Without that permission, some things won’t be working.
  • The term TBR in this spreadsheets stands for unread, owned books
  • The year set in Config does NOT have any influence on what books are counted (you could enter a book read in 2020 and it would still count towards its given month), it just influences what numbers are shown in Overview
  • You can use half-star ratings/decimals. They’ll be rounded for the statistics

The locale is currently set to US. Changing it to use different formats for numbers and dates can make problems with some formulas, so better don’t!

  • Date format: MM/DD/YYYY
    using the wrong format will pretty much mess up all statistics
  • Number format: decimals with a dot (rating/price/spending), duration as HH:MM:SS (audiobook length)
    again, using the wrong format will mess things up
  • Currency: currently set to $ (can be changed if you select all related cells and select: Format>Number>More Formats>More currencies (your preferred one might be suggested in the menu directly too)

(I’m providing a second spreadsheet with European formats, meaning DD/MM/YYYY for dates and commas for decimals)

For those who used 2016’s spreadsheet, there are changes you need to be aware of:

  • Standalones: just leave the “Series” column empty, do not use “-” like in 2016’s spreadsheet
  • Genres: not predefined in Config anymore, you can use any and even more than one (comma-separated list)
  • Challenges aren’t part of the spreadsheet anymore, but can still be tracked via tags

The Spreadsheet

Most of the spreadsheet should be clear on how to use and what to enter but I wrote a manual that has explanations for every single column where data can be entered and what different values mean. I’ll just point out some more “complicated” things here and advice you to read the manual while you look at the spreadsheet.

Where you shouldn’t edit anything:

  • Overview: there is only one row to be edited here, everything else is fully automatic. That row is “unhauled”. Here you can enter the number of books you unhauled without reading, or library books returned unread, in case they need to be subtracted from the TBR.
  • Read/Got: cell A will fill automatically with an index once you enter data
  • Stats: this one is filled with formulas and shouldn’t be changed in any way. The only cells to edit are the titles of genres you want to keep track of (note: there is only place for 12, but you can use more than that in the Read tab, just have to change them around here to see the statistics!)

Notes on the statistics

There are some statistics that might not be 100% clear without some further details.

Overview

If you look into the spreadsheet before 2017 you’ll see that everything is pretty much empty. That’s because numbers will only show up once a specific month has started. (just set it to an earlier year if you want to play around with it)

Stats

The percentages in “Released: Read/Got” concern their row, not books overall. For example having 10 rereads, 3 of them pre-2017, 7 of them a 2017 release would show percentages of 30% and 70%.

The Filter tab

Here you can filter all you read books by different categories.This is by far my favorite because you can go crazy with statistics here, even though it might not look that way at first.

You can filter by as many of the categories as you want, just one, multiple or all of them. You’ll then see a list of books that match all those. It’s especially need if you make use of tags and can easily find a list of books for those tags (for example: can be used for challenges, favorites, book recs, review copies,…)

What’s even more fun is the statistics you get. Here are two examples:

Spreadsheet 2017 - Filter Example

I chose to filter all my read books to only show YA books that are marked as “Read”.

The statistics I got mean the following:

  • There are 13 books that match my filter, that’s 9.2% of all books. Those 13 books have an average rating of 3.8
  • There are 44 books I categorized as YA, of those 29.5% match my filter (= are also marked “Read”)
  • There are 95 books I marked as “Read”, of those 13.7% are YA/match the filter

Spreadsheet 2017 - Filter Example 2

Choosing an author’s gender and a genre and this is what I got. The statistics mean the following:

  • Four books were found matching my filter, that’s 2.0% of all books. Those four have an average rating of 3.3
  • I have 80 books written by women, out of those 5% match my filter (= wrote High Fantasy)
  • I have 18 books categorized as High Fantasy, of those 22.2% are written by women

Suggestions for interesting statistics:

There are MANY possibilities of  what you can look up here, but here are some suggestions of things that I think are interesting to look up:

  • 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

Get the spreadsheet

The file is a read-only and you’ll have to make your own copy via File>Make a copy…

Leave a Comment

Your email address will not be published. Required fields are marked *

Name*
Email*
Website

  • I’m trying to be more organized with the books I’ve been reading (being waaay too reliant on Goodreads atm), so I’m definitely snagging this :D I’ll probably make it one of my new year resolutions to catalogue all the books I own too… Still thinking of how to get around doing that :P

  • This is amazing! I already keep Excel spreadsheets to keep track of everything, but I think I may add your spreadsheet and try to use it too :D

    Thanks for this, it looks so incredible!

  • This is really awesome!! I started using a spreadsheet to track similar stats last year but I like yours better, especially that it can calculate things. Thanks for sharing! :)

  • Your spreadsheets have been my lifesaver the past two years. THANK YOU I’m so excited!!!

  • This looks absolutely amazing! Thank you for sharing it, and I can’t wait to try it out!

  • Paul Cheney

    Found this after Rinn re-tweeted it.

    I use spreadsheets extensively to mange what I am reading, and what I have read and what I want to read, including various ones for challenges and book awards.

    A copy of my master of everything that I have read is here if you’re interested: https://drive.google.com/file/d/0B5-UR04iWeQWTnFSOElSTHF3M0E/view?usp=sharing

    Looking forwards to having a look at yours

  • Hi!
    Thank you so much for doing these! I love the one for 2016 and to be honest, the best part about finishing a book is getting to fill out the spreadsheet and look at the changed statistics afterwards.

    • I love this spreadsheet! I am trying to figure out how the TBR count gets filled up. Been searching all over for it, maybe there is something I haven’t done right. But thank you for this spreadsheet. It is just want I wanted this year.

      • Thanks!
        the TBR count starts with you setting the starting number for Jan 1st in the Config tab (number of owned unread books) and then it subtracts the books you read each month, and adds the ones you bought. (you can also define whether books borrowed should count into it). I hope that clears things up?

  • Wow that is super awesome!! I already write things in a little notebook + goodreads but I think I will try your spreadsheet! You did an amazing work, thanks! :D

  • I love your 2016 spreadsheet – in fact it’s what I’ve been using to keep track of my books this year – and this one looks even better! I definitely have to download this once I get the chance and I LOVE the looks of it. Thanks so much for sharing this.

  • This looks intense! I think I’m going to use this next year (or at least try too). Right now I’m using a simple one I made plus book reads. Thank you so much for sharing this with us!

  • This is awesome. I have never come across something like this for book reading, but always one for writing. I can’t wait to use this one this year. :)

  • WOWZA! Just came over from a link at Nose Graze and am I impressed. I used to have a similar but much less intense version but I couldn’t keep up and now just rely on Goodreads.

    I am definitely going to read the manual and try to use this for 2017 though.

    THANK YOU!

  • This spreadsheet is a LIFESAVER. I came via Nose Graze, and I got your ARC spreadsheet, too. One question: Is there any way to order the ARC spreadsheet on the To Read tab to order it with the fewest days left for a book? The order is the other way around for me, it’s longest days-to-release down to shortest days-to-release.

    Thank you!

    • You would have to change the formula in the cell where the first title appears for that!
      There it’s currently “order by A desc” at the end which you need to change to “order by A asc”

  • So, since you have each year in a different file, do you another file that gives you a compilation of the year end results? I would love to see it if you do.

    I have a couple versions of my tracking sheet and always like to see what others are doing.

    Here is the simple version of mine if anyone wants to see it.
    https://docs.google.com/spreadsheets/d/1qn_nfJyizU-v_hqWwN9HDazIT-LMH4-byo37uBlcOfg/edit?usp=sharing

  • Thanks für publishing the spreadsheet once again. I continually used your 2016 version and still love to fill it in. Now I am very interested in the changes you made for this year’s list, so I will be happy to load the Euro version.

    Have nice Saturday
    Bina

  • Hello!
    This is so amazing! I just found it on another blog, read the manual here and think, I got it all. We’ll see once I start filling the blanks. xD But I managed to set it up with my amount of boks tbr and so on. :)
    Thank you very much for this and the manual!

    Greetings from Germany,
    Sandra

  • Aah, thanks so much, this is amazing!!

  • Fortunately I found this post when I had only read five books in 2017. This is the best spreadsheet I have found. Thank you for putting this together. I am in love. I’m going to write a post about this and direct folks here.

    Thank you so much!!

  • I popped over to have a nosey at your spreadsheet after reading Maraia’s post. WOW, it’s amazing! Thank you so much for sharing this. I’m off to have a wee play with it. :D

  • Alyssa M.

    Happy April!

    I just have a quick question. On the statistics tab there is a section for # of authors read in the given year and what author was read the most. What code would I need to add to the formula to count and show the # of books read by that author? For example in 2017, so far I’ve read books by 33 different authors, but the most read has been Helena Hunting. I would like her result to show like this:

    Hunting, Helena (# books).

    Thanks in advance for the help!!!! Love the spreadsheets :)

    • Alyssa M, Change the formula in I15 to this:
      =arrayformula(index(unique(filter(Read!E2:E,countif(Read!E2:E,Read!E2:E) = max(if(Read!E2:E””,countif(Read!E2:E,Read!E2:E))))),1,1))&” (“&arrayformula(max(if(Read!E2:E””,countif(Read!E2:E,Read!E2:E))))&” books)”

Nov 28, 2016