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.
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:
- 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.
- 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)
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.
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.
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”:
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
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!
Leave a Comment
Maraia
SO EXCITED. It’s going to be a lot easier to start this from the beginning next year. :D
Crini
I really hope the tag thing isn’t too complicated though XD
alexandra @ twirlingpages
i absolutely suck at spreadsheets and this post is kinda VERY inspirational and awesome. since there’re only two months left in the year, it’s pointless for me to create one now BUT i’ll definitely make one next year, using yours as a guide. this looks a bit intimidating so i’ll ask you questions (if you don’t mind) if i need help ? FANTASRIC POST!! xxx
Crini
Of course! Feel free to ask! I also made a tutorial series you can check out! You can find it here
Eri
This is absolutely amazing! I mentioned this on twitter already but I’m quite excited for 2016 to arrive, armed with such a useful tool :)
Becky @ A Fool's Ingenuity
You’re excited to post it and I’m so excited to get this spreadsheet. Your spreadsheet for this year became like my bible for all things bookish, I have no clue how I’d know what I’d read without it. I loved my 2015 spreadsheet so much so the fact you’ve improved upon it is amazing! Thank you for sharing your new one, I am so excited to start all of next years books!
Fahima @ Hitch Theory
THIS IS SUPER AWESOME, O MISTRESS OF ALL THINGS EXCEL! ;D Since I’m behind on entering books into my 2015 sheet anyway, I can actually start using this one right away. Yay! Thanks, Crini! xD
Myrthe
Ah! I love spreadsheets! This one is so much cooler than what I’ve been using for the past two years! I’ll have a look at it to see how I can use it (I like to keep track of the language I read the book in, because I tend to read a lot in English, but then miss out on awesome Dutch and German books).
Crini
You could use the tag column to keep track of different languages! :)
Jessica Samuelsen
This is really amazing. Thanks for doing this! I will be using it.
Lauren @ Bookmark Lit
I know I’ve already said this multiple times, but THANK YOU again for the help with the 2015 spreadsheet! Lifesaver.
I am SO EXCITED FOR THIS ONE. I really want to better use the spreadsheet for tracking spending and actual TBRs and things like that.
Thank you thank you!
Crini
You’re welcome! Always feel free to ask when something doesn’t work! Some problems/mistakes you only notice when you actually use the spreadsheet so I’m glad when I can get rid of them!
Kate
Hi! I have just been playing around with the spreadsheet you created and I find that using “-” for standalone books is not working. I don’t know if it’s something I’m doing wrong or an error in the spreadsheet. I am not very spreadsheet savvy. Am I doing something wrong? I love the spreadsheet and would like to use it in the coming year, thank you for creating and sharing!
Crini
Mmmh, I just tested again and it worked for me. You are using just the minus right? Without the quotation marks?
Otherwise I can help you adjust the formula so that you can just leave it blank for standalones
Kim
Ahh, thank you so much for this! I’m now more excited than ever for 2016!
Shelly
This is AMAZING! Already downloaded and configured it! I can’t wait to use it! You’re awesome, thanks!
Ashley
If I add more spreadsheets onto this will it mess up the configurations and numbers ?
Crini
You mean more tabs? No, that’s not a problem
Carlisa
I’ve never done a spreadsheet like this before so I’m excited to try it! Thanks so much for putting it together.
Sana // artsy musings of a bibliophile
Yes, love the update and SO close to finally start using it! =D
Brianna C
I love this spreadsheet so much! I love all the additions and changes!
I did have a quick question about the genres-is it possible to ADD genres so that there is more than 10 or do we need to narrow it down to 10 only?
Crini
You could add more, they just don’t show up in the Statistics tab. (and in the Read tab there is an evaluation which will mark the cell with little red flags but that won’t cause any problems)
Michele
This is incredible! Thank you for putting this together and sharing it. So excited to try it out for the new year! :)
Sylwia
This is so awesome! Thank you so much for sharing this with us and creating a guide! My only question is what is the difference between DNF and Lost Interest?
Crini
Lost interest is when you get rid of books without reading them. It’s just so that the TBR (as in unread books you own) shows the right amount of books in case you sort out books.
Mel@thedailyprophecy
You are amazing. This is simply brilliant and I can’t wait to use this spreadsheet next to my own one in Excel.
Rachael Turns Pages
How do you add a spot for reading challenges? I loved that feature on last year spreadsheet.
Crini
You could either use them in the tags section OR I could actually add that feature again. I completely forgot about it :D
Tory
Okay, so I am always confused by date formatting. But if I want my date to be formatted as dd/mm/yyyy, I just need to change my spreadsheet settings to German, right? It won’t affect any other input, aye?
Thanks Crini! :D
Crini
yes, that should do it :)
Deyse @ Bound With Words
Hey, I started using it to test and stuff and I’m having some trouble with the “Got” tab and the Expenses column. I first tried using the numbers like this “17.90” and etc. (without the asps of course) but them on the “Overview” tab the total kept getting wrong (I know is wrong, bc I only put 5 books on the “Got” tab and it’s getting a value bellow), I tried them to use like this “17,90” but them it just didn’t showed up at all on the “Overview”, so I guess it really is to use the dot but I’m doing something wrong. Please send help.
Crini
OMG, so good you tested it. I just found a mistake in the formulas (which I didn’t see before because I only tested with one book per month). It’s fixed now. Either copy the newest version again or just copy the formulas in cells C13 to N13 from the Overview tab.
Deyse @ Bound With Words
Since I was using that one just for test I’ll download the new one for 2016. Thanks!
LeseMaus
As I am not fond of Google spreadsheets I tried to re-build your 2015 spreadsheet in Excel for myself. But I did not succeed in rebuilding all the formula you used – really seems that Google is better than Excel in this case :-)
This year I think I will shift over totally to your spreadsheet – thank you so much for sharing this with us!
Alle the best for 2016!
Bina
Melanie
I love this!
But I’m curious, how do you account for books you read that you don’t own (for example, a book from the library)? Is there any way to not have a book read count toward the TBR number?
Crini
I count them as TBR as long as I have them, so I track them the same way as I would books that I buy.
Jennilyn V.
I usually struggle using someone else’s spreadsheet but this will be oh so useful. Thanks for sharing!
Jennilyn @ RurouniJenniReads
WortGestalt
Hey Crini, I hope it’s okay to comment in german?
Ich bin gerade schwer begeistert von deinen Spreadsheets, sehr genial! Danke, dass du die Seiten zur Verfügung stellst, ich werde sie für das neue Jahr auch nutzen, man liebt ja doch seine Statistiken und mit so coolen Listen ist das ja echt ein Fest! :D
Eine Frage wollte ich noch kurz loswerden. Kann ich irgendwie auch die Verlage unterbringen? Ich schau immer am Jahresende gern, wie viele Bücher ich aus welchem Verlag gelesen habe und wie sich das verteilt. Könnte ich die Zielgruppen-Spalte dafür umbenennen oder verursacht da Probleme bei der Auswertung? Schon mal danke für deine Antwort und viele liebe Grüße, Philly
Crini
ich selbst benutze zum Beispiel einfach die Tag Spalte dafür. Du könntest theoretisch auch die Zielgruppen Spalte nehmen, müsstest dann natürlich nur an einigen Stellen die Sachen dafür anpassen, damit es in der Statistik passt. Probleme direkt macht das nicht.
WortGestalt
Vielen Dank, dann probiere ich das mal aus! Stimmt, die Tags könnte ich dafür auch nehmen, mir war nur nicht klar, ob es dafür dann nachher auch eine Auswertungsmöglichkeit in Diagrammform gibt?
Crini
Die Tags sind teil der großen Tabelle, aber da kann man sich immer nur einen Tag auf einmal ansehen.
WortGestalt
Ok, Dankeschön!
ashley
Thank you SO much. This is perfect for me and my statistic heart.
Lucina
Normally I’m lazy to fill out a table like this over the course of a year but your spreadsheet is so amazing that I’m trying to stay on this task :D
I have a question regarding the GOT tab. Is there a way to add a new book (because of expenses) and not add it to the TBR except for adding +1 to the “lost interest” row? I buy a lot of books that I don’t count to the TBR because I already own or read the book. Or can I add a new column “Count Y/N” and change the TBR formulas so that only “Count Y” books are added to the TBR number?
And another smaller question: Why is the TBR in the overview table in the overview Tab only showing the “since beginning of 2016” TBR and not the real number? I thought it should show the same number as C28? :)
Crini
I would add another column to the Got tab (if you get Version 4.0 you can see that I did something similar to to Read tab, so not all books are subtracted from the TBR)
It’s not showing what your TBR currently is (that is at the bottom of the Overview tab!). It only shows what happened to your TBR that month (by how many books it grew/reduced)
Lucina
Thanks for the clarification ond the TBR count thingy.
I tried to make the column in the “Got” tab like your “Mine?” column (although at first I didn’t knew how to make a drop down menu) and then copied your formula the way I thought it might work (I don’t know a thing about spreadsheet or excel but it helped that the “mine” column works similarly ^^) and after some testing I think it does what I want. :D
CrystalBookSnob
Hi Crini!
I love this spreadsheet and I’m so excited to use it all year long!
I am having trouble in the Read tab in the Series box. I entered “-” for a standalone that I read but it’s not appearing on the Statistics tab that I’ve read a Standalone. Thanks for your help :)
Crystal
Crini
Did you use just the minus, without the quotes? (that’s how it should work)
Amy @ Pages of Starlight
First of all, I want to tell you that I love this spreadsheet. I just started using it for this year and it is amazing! I am having one problem with it though. I tried adding a novella that is book #5.5 of the series and it doesn’t add it to the series stats screen. There’s none in the ‘shortstories’ row and it doesn’t increase the number in any of the other series totals either. Any idea what might be happening here?
Crini
mmh, that’s weird. I just testes again with your exact number and it worked. Could you maybe give me access so I can specifically look at your spreadsheet?
Amy @ Pages of Starlight
I just ‘shared’ it with you, using your email for this account. Is there anything else that I need to do?
Crini
I found a small error in the formula and fixed it. It works now ;)
Bekka
This is such a useful tool. Thanks do for much for your hard work.
I am still adapting the spreadsheet for my purposes and have a question:
I deleted the MG and NA columns in the statistics overview. But now the automatic change of color in the headings doesn’t work anymore. How can I repair this? It would be great if you could help me with that or point me in the right direction.
Thanks
Crini
You would have to edit that via Tools>Script editor, as I assume, the wrong cells are changed for you now? You have t change the range in the script then
Bekka
Thank you very much. I was wondering where this feature was hidden :)
Stephanie @ My Bookish Itinerary
This spreadsheet is AMAZING!
Thank you so much for sharing it!!!!
Seriously, I am really excited to use it to keep track of books read this year! I feel like this is my year to be more organized.
Thank you! :D
AmandaEmma
Hi! I absolutely love your spreadsheets and I just started using the series one. I might grab your ARC spreadsheet too!
I don’t have any problems, I fixed the ones I found myself (because it was basically minus instead of plus and so on). However I would ask you how to add another feature. You’ve added the ‘Mine’ drop down with yes or no, but is there a way to add ‘for review’ or just ‘r’ for review? It would be fun to see how many review books I read contra my own.
Can you point me in the right direction or should I share it with you?
Thanks for all your great work, I’ll feature your spreadsheets on my blog in a while and I’ll of course give you a ton of credit!
AmandaEmma
We’re talking about changes in the books 2016 spreadsheet!
Crini
What exactly do you need help with? How to make the column with a drop down/how to get the stats or…? :D
AmandaEmma
In the ‘Read’ tab there’s the ‘Mine’ column with a dropdown menu with yes and no, can you add a third option to that drop down menu somehow?
Another thing; how does the ‘TBR’ row in the overview tab work? (the grey one). It just changed from 2 to 1 and I don’t even understand what the 2 stood for in the first place.
Crini
Ah! For that you have to mark all the cells in that column and then go to Data>Validation
That row shows the progress of your TBR. Two means it grew by two books this month (= you got two more books than you read).
AmandaEmma
Thank you so much! I was getting quite confused by the TBR row thanks for explaining! And the dropdown thing worked!
One last question, sorry to keep bothering you. My TBR on January 1st was 176 books and my goal is to reduce it by 60. Am I right in assuming that if I don’t get any new books I have to read 5, but this month I got 4 new books so I now have to read 9 to stay on track? Because it went from 176 to 180 and then down to 177 but still saying that I want to reach 171 by the end of the month. I don’t know if this makes any sense..
I’m quite thankful!
Crini
Yes! BooksRead minus BooksGot is the TBR reduction (which shows in the gray row) and you are on track if that is equal or better than your goal/12. So buying 4 and wanting to reduce each month by 5 means you have to read 9, that’s right! (basically you want a -5 or lower in the gray row)
Irene
Hmm, something’s not right in my sheet in overview and statistics.
For example, it says ‘7’ in DNF, and I’ve written ‘Read’ in all the 12 books I’ve read. It also says ’40’ in number of pages read, although it knows that I’ve read 4 books 0-150 pages, 5 151-300 and 3 301-500… I tried changing the dates from for example 01/31/2016 to 01/31/16, but it looks the same…
Crini
Could you give me access to your spreadsheet so I can have a look at it?
elisa
i’ve been keeping track of my books in a spreadsheet since beginning of 2015, but i had to change what i’m doing because this is incredibly cool. you have spreadsheet skills. thanks for putting this up :)
Dazrin
Thanks for sharing your spreadsheet, it has given me a couple ideas for my own.
Question, why do you create a new sheet each year? Just wanting a clean slate?
Personally, I like being able to compare the current year with previous years. Since that lets you see where you have been and how you are doing relative to other years. It is more complicated, and I have made mine even more complicated than needed, but I enjoy the results.
Link
April
I love this spreadsheet. It has been really cool to see all the statistics. I am trying to figure out something. How do I get the shortstories/novellas to count. In column D for series I figured out how to get I to count 1 through whatever of a series just not the shortstories/novellas.
Chris
Hey I’m looking at organising and tracking my reading and stumbled across your site. This spreadsheet looks amazing! Nice work. I’m terrible at spreadsheets but I’d like to adjust it to suit myself a bit better – ideally a long term ‘master’ list. Do you have a long term ‘master’ spreadsheet? Or do you think this can be easily converted with some fiddling?
Crini
I have such a spreadsheet in the works but it will take quite some more time until that is done. You could use this one I guess, it’s not really fixed to a certain year (only the overview tab is), but some statistics (like “new releases” vs “older releases”) won’t make sense in this scenario, but for genres etc, you can still use it.
Cathie Dalziel
Amazing spreadsheet! Thank you for all your work on this. Wish I had found it at the beginning of 2016 and not the end, too much work to fill in all the books I’ve read this year and their information. I am so looking forward to using this in 2017!
Crini
I actually have a new one for 2017. currently 3rd post from the top!