The Reading List

Introduction

My sister reads, like, a lot. And I read a good amount, too. So in 2021 we decided to start a blog together called The Reading Siblings where we could post reviews of books we had recently read. As we neared the end of the year, we wanted to do a little Year in Review for each other -- a look back at what the other person had read, a reminder of what we did/did not enjoy, and any goals we had for the new year. We had one big problem though. My sister was keeping track of her reading in a shoddy Excel spreadsheet, and I hadn't logged any of my reading. It put a major pause on our plans, and we decided we needed a better system to record what we read during the year.

The Reading Siblings' Reading List was born.

Goal

Easily and efficiently keep track of all the books my sister and I read for each calendar year.

Data Preparation

The first step in this process was asking ourselves what data we even wanted to collect and track. There were some obvious ones, like the title and author, but we also decided to monitor the author's gender or if there were different forms of representation within the books we read such as race, gender, and sexuality in an effort to read more broadly and inclusively.

It took some time to hammer down these points of interest, but we ultimately settled on the following:

• Title
• Author
• Genre
• # of Pages
• Start Date: start date of reading book
• End Date: end date of reading book
• ⭐ rating: rating out of 5
• Notes: any comments we might have about the book
• Book source: purchased, library, etc.
• Publisher
• Publication Year
• Fiction/Nonfiction
• Form: prose, poetry, comics etc.
• Author Gender: male, female, non-binary, trans, etc.
• Author Representation: person of color, LGBTQ+, disability, etc.
• Character Representation: person of color, LGBTQ+, disability, etc.
• Re-Read: whether we would re-read the book
• Original Language

Now knowing what we wanted to track, it was time to build the Reading List.

Building the Reading List

The easy part was getting the general set up of the Excel spreadsheet including the column names.

While building the Reading List, I was constanty referring back to what my sister had been utilizing before. While it had worked for her purposes, when I was going through it for our End of Year Review, I had noticed hundreds of null cells that were difficult to work with. When I spoke to her about why there were so many bank cells, it seemed to stem from her not knowing what to include or where to include it.

This ended up being a big focus for me on this new Reading List. I wanted to employ a technique that would elimate as many chances for null values as possible to make any future data analysis more seamless. If starting from scratch, might as well make it as good as possible!

Data Validation

I settled on data validation for this problem. In Excel, you can use data validation to restrict the type of data or the values that someone enters into a cell. In this scenario, I used drop-down lists so that my sister and I would both know what type of data to put in specific cells.

My sister likes to be uber-specific about how she records a books genre, much moreso than I, and I initially worried this would be an issue with data validation. Luckily, including options within the data validation drop-down menu is incredibly easy to do on a rolling basis. My sister and I got to have it both ways -- she can be as specific as she wants with her genre labeling, and I get no missing data!

I employed data validation through a majority of the spreadsheet, and it's worked really well. After reviewing her and mine Reading Lists' for the year 2022 almost all of the empty cells were gone! Huge win!

Date and Pages Calculations

Another thing I wanted to add to the Reading Log portion of the Reading List were two columns that would calculate how long it had taken us to read the book and how many pages were read per day on average.

Because I wanted these columns to automatically update once the columns Pages, Start Date, and Finish Date were populated, I came up with the following formulas:

• Days Read: =IF(G4<>"",DATEDIF(F4,G4,"d")+1,"")
• Pages Per Day: =IF(G4<>"",E4/N4,"")

Both use the If Cell Contains formula, but let's walk through them one at a time.

The Days Read formula allows me to return a value based on whether or not a cell contains any value at all. In this scenario, we're checking if G4 (Finish Date) is blank or not, and then returning a value if it isn't. In this case, once a finish date has been inputed, the formula will then find the difference between G4 (Finish Date) and F4 (Start Date) and then add 1. And that's how many days it took to read the book!

Meanwhile, the Pages Per Day column uses similiar logic. It checks if G4 (Finish Date) is blank, and if not, divides E4 (Pages) by N4 (Days Read) which returns how many pages we read per day.

Processing the Collected Data

Having this new spreadsheet was great as it immediately helped my sister and I better oraganize all the books we read in an intuitive, user-friendly system. I, however, still wanted to be able to analyze everything that was being inputed. With this in mind, my next goal was to set up a system that would automatically process and track all of the collected data.

In total, on this sheet, I am keeping track of 18 different metrics. Below are some of the formulas I implemented in order to have this sheet fully automated:

Scroll to view the full table.

The rest, which track how many books read in each month, how many books per each star rating, how many books read per each genre, etc. use a COUNTIF formula. For example, in order to find out how many books received a five star review, I used the formula =COUNTIF('Reading Log'!$H$4:$H$1000,"5") . Essentially, this will count all the cells from the Reading Log sheet in column H (⭐ rating column) with the number 5 and return how many it counted. This logic is used throughout this sheet in order to return how many science fiction books I read, how many books I read that were originally published in English, or how many were checked-out from a library.

Author and Character Representation

The most challenging part of this automating process was coming up with formulas to track forms of representation (race, gender, and sexuality) of the authors and the characters in their books as well. My hope was that by tracking this, I could consciously work to read more broadly and inclusively.

The first challenge when trying to automatically track this, however, began with the Reading Log. Both columns, Author Representation and Character Representation are outfitted with data validation meaning the user can chose bectween POC (person of color), LGBTQ+, Disability, or N/A. Excel's data validation unfortunately only allows the user to chose one of these which presents obvious problems. What happens when I read a book by a queer black author?

Because Excel would not allow for this possbility, I had to look for a solution outside the program. Luckily, because this is no doubt a common problem, I found my answer quickly -- a chunk of VBA code. With a few alterations to change the columns affected, it worked. I could now chose two or more options from the drop-down menu!

With this first step done, I could now work on tracking this data. This, however, presented another issue. The typical COUNTIFS formula counts cells across multiple ranges based on one or several conditions; however, this counts only those cells where all the specified conditions are TRUE.

As an example:

Scroll to view the full table.

If we wanted to count the rows where 'POC' is listed, we could write the following formula: =COUNTIFS('Reading Log'!S4:1000,"POC", 'Reading Log'!S4:1000,"POC, LGBTQ+") How many would this formula count in the table above? 3?

It would count none! This formula looks for cells where the condition 'POC' and the condition 'POC, LGBTQ+' are both true, which obviously doesn't happen in the table above. We're essentially failed because we're using AND logic when we need OR. In other words, we want to count cells for which at least one of the specified conditions is TRUE. This is possible by either adding up several COUNTIF formulas or by using a SUM COUNTIFS formula with an array constraint. I chose the latter as it results in a much more compact formula.

But first, we need to figure out all of the constraints which means we need to know all the possible ways in which 'POC' could appear in the Reading Log. The above table contains a few alternate ways, but not all of them.

Scroll to view the full table.

All of them? Still no, unfortunately. The order they appear in the cell matters as well. 'POC, LGBTQ+' would be counted, but not 'LGBTQ+, POC'. The permutations continue...

Scroll to view the full table.

There! We have all of our constraints. Now, with this mouthful of a formula:

=SUM(COUNTIFS('Reading Log'!$R$4:$R$1000,{"POC","POC, LGBTQ+","LGBTQ+, POC","POC, Disability","Disability, POC","POC, LGBTQ+, Disability","POC, Disability, LGBTQ+","LGBTQ+, POC, Disability","LGBTQ+, Disability, POC","Disability, POC, LGBTQ+","Disability, LGBTQ+,POC"}))

we can count the true number of times 'POC' is listed! I did this for the LGBTQ+ and Disability categories as well and now our Data Tracker sheet is fully automated!

Creating the Dashboard

With the Data Tracker sheet taken care of, I needed to make a dashboard. The raw, numerical data is great, but not quickly accessible to get an understanding of my reading. I wanted to turn these into a series of graphs so I could get a feel for the year's reading in seconds.

And there it is! Because it uses all the data from the Data Tracker sheet, these graphs update anytime a new book is entered into the Reading Log. And by formatting this dashboard as a series of questions (How Much Have I Read?, What Did I Read?, How Did I Like It All?, and Representation Categories?) I can quickly and easily see any trends in my reading habits.

The Last Element

With the Reading Log, Data Tracker, and Dashboard all working well, I was incredibly happy with my work. I satisfied the original goal of building a spreadsheet that would efficiently keep track of all the books my sister and I read during the year. But it still seemed lacking. The Reading Log felt too impersonal, not enough flair.

I wanted to add some conditional formatting to the Title column so that it would reflect the ⭐ Rating.

I began by highlighting the Title column and making a new conditional formatting rule. I changed the style to Classic, and used the following formula to determine which cells to format: =$H4=1. I then chose the color (a dark red to symbolize a bad rating). I continued this process for each star rating possible (1, 1.5, 2, 2.5...5). For each new conditional formatting rule, I implemented a gradient so that a 1 would be red (bad) while a 5 would be green (good).

Now the Title column automatically changes color depending on the star rating I give it! I also froze the column headers as well as the Title column so they stay in place as I scroll throughout the sheet that way as I'm exploring I can still have context for how I liked the book without needing the actual star rating number.

Conclusion

What started as a simple tool to track the books my sister and I read turned into so much more. From the fully automated Data Tracker to the Dashboard, this file is completely capable of giving my sister and I a broad outline of our reading habits/tendencies all while being incredibly user-friendly and enojoyable to look at (I seriously love the conditional formatting for the title column!). I am incredibly proud of the finished product, especially because I know it serves its purpose well and will be used diligently by my sister and I for years and years to come.

Learning Take-Aways

Ask questions and outline before starting. My sister and I did a great job deciding what we wanted this Excel spreadsheet to do and be useful for before I began building it. Without sitting down and thoroughly going through everything we wanted to capture, it would have taken much more time to actually create. In all likelihood, it would have ended up being filled with useless attributes that never got used. Asking questions and preparing initially were essential to this spreadsheet's ultimate effectiveness.

Know the tool you're working with, including its limitations. Excel can do a lot, but there are still limits to its powers. In this project's case, Excel doesn't allow for selecting multiple options from a drop-down menu via data validation. Knowing this and deciding to seek outside help immediately allowed me to find a solution quickly. It's great knowing how to do something yourself, but sometimes the smartest option is knowing when you need a little assistance.

A finished product can only be finished if it's easy for anyone to use. I initially did not include the Dashboard sheet because I assumed any user would be able to quickly and easily make graphs themselves based off the Data Tracker sheet. This was obviously a poor assumption as plenty of people like to read but don't know how to use Excel. Even my sister initially struggled with this, and so what I thought was a helpful, finished product was not actually useful to her at all. Once I implemented the Dashboard, my sister and I could both use the product as equals and harness its full utility.