All About Books

A book blog, mostly touring foreign worlds of fantasy novels with occasional detours into space and explorations of magical realism, featuring book reviews, random bookish thoughts and hand lettered quotes.

Spreadsheet: Books 2016

Yes, we still have two whole months to go until 2016 starts, but I love my spreadsheets and I’m way too excited about this one to wait any longer. I just had to share it now.
Every year I create myself a new spreadsheet to keep track of all the books I read and bought to see what genre I read most, how much money I spent and lots more. This year I went a bit over the top and I created a huge matrix of statistics. I almost want to fill the spreadsheet with my books from 2015, I’m THAT excited about all these new statistics.

This post is not just to show you my spreadsheet and share a copy for you to use, I also explain in detail HOW to use it. There are a few things to be made clear before you can start using it so please read the whole thing if you are going to use this spreadsheet.

What you fill in

The spreadsheet consists of 5 different tabs, but you only fill in 3 of them, the other 2 are automatically filled with statistics for you.

Config:

This is the tab you should look into first. You can always make some changes to the configuration later but it is better to do this right from the start.

Spreadsheet2016_config

As not everyone might like the green color I chose for the spreadsheet (what is wrong with you? :P), I added something to the config so that you can change the color of everything in just one place: First you set the background and font color of the field C3 (which is currently green) to the colors you want. To actually trigger the change you have to change the content of the cell. Delete the “!” from the text for example and when you want to change it again, add it again. It really doesn’t matter what you do, just that you change the content.

You also have to set the year. This is important for the Overview tab. The monthly statistics will only show up for months that are already in the past or the current month of that year. (It’s only about when statistics show up, not what books are counted. It actually counts books from all years) If you already want to test the spreadsheet now and see some numbers, set it to 2015.

The next one is the (Goodreads) reading challenge goal. You can also configure if you want to count rereads and books you marked as DNF (did not finish).

For those of you who want to reduce their TBR, you can set a goal (for the whole year) and what the number of books on your TBR at the beginning of the year was.

You can change these configurations at any time, even after you started filling in the spreadsheet!

Lastly you can define the genres you want to use. There are two important things to remember about this: 

  1. Once you used a genre in the Read tab, don’t change its title again! Changing a title of a genre doesn’t automatically change it in the Read tab. You would have to do that yourself.
  2. If you don’t want to use all 10 genres, don’t leave any of the titles blank! Keep the titles of “Genre X” for those you don’t use or you might get weird/wrong statistics.

Read:

This is the tab about the books you read. Here you fill in the data for every book, one row for each book. (Note: if you don’t want to use all columns, right-click on the column and choose “Hide column” instead of deleting it. This prevents possible errors in the statistics) 

Spreadsheet2016_read

The data for each book is:

  • Date*: the day you finished reading the book (format: Month/Day/Year)
  • Title*
  • Series: this is the title and number of the series
    Format: SeriesName #Number, decimals for short stories are okay too
    Use “-” when a book is a standalone
  • Author
  • Gender (of the author, F=female, M=male, O=other)
  • Release: the year of the release (not the exact date! ONLY the year)
  • Genre (choose from the list defined in the Config tab)
  • Audience (MG=middle grade, YA=young adult, NA=new adult, Adult)
  • Pages
  • Format (HC,PB,eBook,audio)
  • Rating (You can use any number between 1 and 5. Ratings with decimals will be rounded in the statistics)
  • Status* (Read, ReRead, DNF=did not finish)

*columns not to be left empty or you’ll get wrong statistics

The last column (not in the picture) is Tags. I wanted to add that column to mark books with LGBT, POC and similar tags to get statistics about these as well. You can enter any tags you like and separate them any way you want BUT the statistics are a bit tricky due to what is possible in these spreadsheet formulas. Use unique tags! “art” for example could cause problems, because there are a lot of other words that contain it (e.g. “heartbreak”) and those would be counted for it as well.

Example tags I am going to use:

  • pub-tor, pub-scholastic, … (to see what publisher I read most)
  • poc-mc, poc-author
  • favorite (because not only favorites get 5 stars and this way I can distinguish them)

Got:

This tab is for all the books you bought/swapped/were gifted. Just every single book you got one way or the other. It has a lot less data than the Read tab:

  • Date: the day you got the book (format: Month/Day/Year)
  • Title
  • Released: the year of the release (not the exact date! ONLY the year)
  • Expenses

 

Note: the decimal mark is a dot!
(if you want the German format for dates and numbers for example, go to: File>Spreadsheet setting… and change the Locale to Germany)

What you get

Overview:

The overview tab is the monthly break down of all the books you read and got. It also shows the status of the reading challenge and TBR reduction and if you are behind or ahead of your goals.

Spreadsheet2016_overview

This tab is 99% automatic. You don’t have to fill in anything but one row: “Lost interest”. If you get rid of books and they should be subtracted from the number of books on your TBR, then this is the place to fill that number in. Do not fill out anything else but that row or you will overwrite formulas. It looks empty now but change the year in Config to 2015 or even 2014 and you will see it fills automatically.

The overview shows the number of books you read, reread, DNFed, got and lost interested in and shows your TBR progress. It has also statistics on the number of pages, average rating and expenses of each month.
Additionally you can see the total number for the whole year and the average per month on the right.

Reading Challenge and TBR work the same way. They both have a row “want” that shows the number you should be at according to your goal (that you set in Config), a row “is” that shows your current status and “+/-” that shows the difference = if you are behind, on track or ahead and by how many books. 

Statistics:

This is where the real fun begins. In this tab you see a ton of statistics about the books you have read.

Spreadsheet2016_stats

I think most of it should be pretty clear so I’m not going to explain much about this (feel free to ask questions though if something isn’t clear!). This one is fully automatic! Do not edit anything here but one single cell:

C27: currently saying “ENTER TAG HERE”
In this cell you can insert any tag you used and get statistics for it as well. In row 27 you get all the statistics about how often the tag appeared in books for certain audiences etc and in the last column (U) you get the tag in combination with the different genres.

 

With spreadsheets as big as this one there are bound to be mistakes somewhere. I tried to test most of it but sometimes forget this or that anyway. If you already try out the spreadsheet before the end of the year and find something that isn’t doing what it is supposed to, please let me know! If you notice that you would need more genres for example, I can make those changes too.
I’ll let you know about updated versions in this post too, so maybe come back at the end of the year to see if anything changed and you should get the newer version.

Updates

Version 2.0: has an additional new tab called “Filter”:

Spreadsheet_books_2016_filter

Here you can set different values for many available columns and it will filter all the books you read accordingly and shows the percentage of all books read. You can enter values for only one of all these fields, for multiple or all. 
I thought it would be nice to add this because in the statistics tab you only get the number of books for the combination of two values and this way you can get them for three, four and more.

Version 2.1: you can now filter by the year the book was released in too and I fixed a small error

Version 3.0: you can now keep track of challenges

spreadsheet2016-challenges

In the tab called “Challenges” you can enter the name and the goal of the challenges, everything else will appear automatically. In the “Read” tab is a new column (N) where you can enter multiple challenges (comma seperated) for which you want the book to count. As you can see in the example, you will see your progress in 1) number of books read, 2) percentage and 3) if you are ahead, on track or behind. The filter also has an additional field to filter by challenge.

Version 3.1: Fixed a problem with the calculation of monthly expenses 

Version 3.2: Fixed some smaller mistakes.

Version 4.0: Added another column in the Read tab to mark books as Mine: Yes|No. Only books that are marked with “Y” will be subtracted from the TBR in the Overview tab! (this way you don’t have to add library books in the “Got” tab and can just mark them as not yours)

Get the spreadsheet here: Books 3.2 (if you don’t need the newest feature) or  Books 2016 4.0
This is a read-only file. Go to: File>Make a copy… to get one you can use.
This spreadsheet can only be used within Google Docs. It makes use of formulas that aren’t available in programs like Excel.

 

Also check out my other spreadsheets!