In 2019 I “completed” 66 books. I say “completed” rather than read because many of them were audiobooks that I listened to on my work commute. However, when looking at all the books I completed, I was surprised that the largest portion were actually real hard copy books.
Below I show you how I analyzed and visualized my 2019 book data using pivot tables and a variety of chart types.
Gathering the Data
First, I compiled all of my data. Nearly all of my books came from the library, but there were a few I was gifted or purchased myself so I had to make sure those were included. The categories of information I tracked were Book Type (Audiobook, Book, eBook, or eAudiobook), whether I completed it (Completed, In Progress, Not Completed), whether it was Fiction or Nonfiction, Genre, Author Name, and Author Gender.
For Genre, I faced two challenges: 1) I couldn’t find a consistent list of all possible genres that made sense for my reading, 2) books were often not self-identified in a certain genre. Therefore, I made personal judgment calls when categorizing books’ genres.
Here is how my data sheet was set up:
To analyze the data, I clicked in the table of data and inserted a Pivot Chart. This enabled me to do quick cross-tabulations on the different types of data I had.
Here is the pivot table I used to start exploring my data. First, I looked at the intersection of book type and whether I completed it. On the right you can see the list of variables in my data table and how I used them to structure the pivot table.
Here is a close-up of the book type by completion data. In order to use the data in a chart with maximum customization, I like to copy the data from the pivot table and "Paste as Values" below it. Then the data are no longer linked to the pivot table and won’t change if I alter the variables in the pivot table.
Using this method, I created the first chart below, which I put in my Annual Report. Then, by swapping out the variables in the rows and columns of my pivot table, I explored other interactions of my data categories, also visualized below.
Book Type by Completion
Most books that I read were hard copies, but there were a substantial number of Audiobooks and eAudiobooks as well. Combined, the two categories of audiobooks contained almost as many books as I read in hard copy. I often listen to audiobooks in my car as I drive to and from work so I was actually expecting there to be more audiobooks than hard copies.
Book Genre and Completion