How I Use Spreadsheets

Why use a spreadsheet at all?

I started making a note of the books that I was reading after I got about three chapters into a book and realised that I had read it before. That was back in 2002 and I began by keeping a list in a Word document. It dawned on me a while after that that I really should be keeping that information in Excel and I transferred the data over in 2009.

I see those who use all manner of different methods to keep a track of what they are reading and a list of books on their TBR list, but for me, a spreadsheet is the best method. It works similarly to my brain, and I can compartmentalise the blocks of data in a way that suits me. It is also a great way of getting lots of pertinent facts out about my reading habits and the way that they have changed over the years.

 

My different spreadsheets

I have two main spreadsheets that I use. One is called New Books Read Dashboard and the other is TBR Master. (I need to sort the names out…).

New Books Read Dashboard has five tabs:

Overview which is my dashboard for this sheet

Running Sheet, so I can keep a track of everything that I have read in the current year

Main Data, which is where I have a list of every book that I can remember having read, as well as my yearly reading lists which I have kept since 2002. (There are several books from childhood reading missing, some of which I must add, but there are some that I know I will never be able to recall).

Stats where I use pivot tables to drag information out from the Main Data tab and the final one i

Lookups which is now common across all of the spreadsheets.

TBR Master has evolved from two sheets. One had a list of all the library books that I had on loan and another sheet had a list of review books that I have been sent. I had since added to it quite a lot and it now has eight tabs! These are:

TBR Dashboard which is mostly pivot tables of the information in the tabs,

TBR Plan which is where I attempt to pull together the books that I want to read for the coming month.

Library Books is the list of books that I currently am borrowing from the library.

Review books is a list of books that I have been kindly sent by publishers (And is way too long!!).

Own book is something that I have started recently, where I have been logging the many many books that I buy.

Borrowed books tab is also self-explanatory and believe it or not, only has two listed on it.

Virtual TBR is where I log books that I want to read but as yet have not got a copy. I really need to tie this in with my Good Read’s want-to-read selection.

Lookups which is the same as in New Books Read Dashboard and other sheets.

 

Those are the two main spreadsheets that I use day in and day out. I also have three specifically for reading challenges; a general one, a natural history one and one for my World From My Armchair challenge. I then have a further 17 spreadsheets that are for awards, such as the Wainwright, Stanford Dolman, Portico and so on. There are then numerous others that contain lists of books from authors, publishers and other random lists that I haven’t quite got around to sorting out yet.

 

How I Use Spreadsheets

When I first moved over from a list in a document to a spreadsheet, I only had four columns, author and title, year read and so on, but as time has gone on I have added extra columns to be able to get meaningful data out.

I now have ten columns of information that make up what I call my core set. These all appear in the same order in each (ok most as I still haven’t updated them all yet) of the spreadsheets that I use. This means that I can copy information easily between each spreadsheet without having to change anything. I know that it is always going to be the same.

 

Core Columns

Title: Pretty self-explanatory really, it is the title of the book.

Author: Also, self-explanatory. I use Ed. for editor and Tr. and translator where necessary

Gender: I have a dropdown that is either male or female. I haven’t included the various other genders out there at the moment.

Publisher: This is the name of the publisher. I rarely read self-published books now, but I use Self in that column when I do so.

Pages: This is how many pages the book has, including indexes etc.

ISBN: A record of the ISBN of the edition I am reading.

Publication Date: This is the date that the edition I have recorded was published

Binding: I have three choices here, Hardback, Paperback and ebook.

Price: This is the price of the book on the cover, not necessarily what I paid for it!

Genre: I have three choices on this dropdown, Non-fiction, fiction, and Poetry

 

Dashboards

It is all very well having the data in my spreadsheet, but it isn’t much use if I cannot extract meaningful or useful information. I have started using simple dashboards now in some of my sheets as I have found this is a good way of summarising the data. Most of this I do through pivot tables, these are a clever way of taking big dollops of data and distilling it into easy-to-read information. I also use various formulas to sum and count very specific pieces of information, normally to do with reading challenges.

In my TBR Master sheet, I use most pivots so I can see how many books are on my reading plan for the month, review copies that I have got outstanding, books on my virtual TBR and how many books I have. It kind of works at the moment, but there are gaps in what I want from this sheet at the moment.

 

Lookups

One of the key parts of each of the spreadsheets is the tab titled Lookups. This sheet is now being included in all new spreadsheets that I am doing and this is what it looks like:

In this tab, all the data is in specific named areas and are formatted as a table. This makes the data validation fairly easy to do and the dropdowns in each spreadsheet match each time. It might not be for everyone, but I find it helps me.

 

Pros & cons

When I first set about thinking about the ways that I wanted to improve my spreadsheets I set about it using good old pencil and paper. It took a lot of thinking and sketches before coming up with the current solution. I have been using these modified sheets for just over a year now and mostly this has worked really well. However, I have seen ways to make improvements and that is going to be covered in the next post on this.

Spread the love

10 Comments

  1. Rebecca Foster

    This is amazingly detailed! Up until 2020 I just kept Word lists of what I read, but Marcie (Buried in Print) gave me her spreadsheet template for keeping track of her reading and I’ve been using that with adaptations since 2021. It has similar columns to yours. I have also started logging BIPOC and LGBTQ reads. I like how easy it is to see at a glance how many, e.g. translated books I’ve read so far in a year. No doubt my spreadsheets could be more sophisticated, but my Excel skills are basic.

    • Paul

      Thank you, Rebecca. I am more than happy to send copies of these for you adapt.

  2. Elle

    I can’t handle Excel in any sophisticated way, so this is wildly impressive to me! I use a very simple Word doc and colour-code BIPOC and LGBTQ+ authors/books.

    • Paul

      The important thing is finding a system that works for you. I started off with a list in word, but I found it too limiting.

  3. kaggsysbookishramblings

    Very impressive! I have a very simple spreadsheet, with a tab for each year to record what I read (name, title, date published, date finished, date reviewed and gender). Then there’s the big scary tab where I record incoming books, date received and source – I get a bit alarmed when I look at the numbers down the side!!!

    • Paul

      Thank you, Kaggsy. My TBR spreadsheet is terrifying…

  4. Marcene Jones

    Thanks. Your dashboard gave me ideas on how to complicate…I mean ..enhance my simple spreadsheet. It’ll give me something to do.

    • Paul

      Haha! Have fun setting it up!

  5. Liz Dexter

    This is so cool! I have two spreadsheets, an incomplete one which I have created out of my notebooks which lists author, title, when I got it (or library or loaned) and when I reviewed it, which I have completed for all the notebooks from 1997 to 2005 when I started my blog, and keep meaning to complete. Then I have my yearly one which lists book author and title, publisher, published date (original date for reprints), author gender, Fiction or Nonfiction, genre/subject (fiction, short stories, sport, race, etc), diverse topic (POC, LGBTQIA, Disability, Class, and combos), author POC or White and author nationality. That won’t show up books in translation, I realise. I have pivot tables giving me my stats for the year. I claim to add all my books to LibraryThing and to deaccession them into a “Read but unowned” collection so people don’t re-buy them for me. Oh, and I have a little notebook I note down when I plan to review things in for the month.

    • Paul

      Thank you, Liz. The important thing is finding a system that works for you. I have sent the template to a couple of people already and I am more than happy to send it on to you too

Leave a Reply

© 2024 Halfman, Halfbook

Theme by Anders NorénUp ↑