Spreadsheet: Books 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:

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

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…

• Standard version (Date: MM/DD/YYYY, decimals with dots)
• Different formatted version (Date: DD/MM/YYYY, decimals with comma, €)
• The manual