How to Make a Book Tracker on Google Sheets

Creating your own book tracker in Google Sheets is one of the best ways to get a real-time pulse on your reading life. It lets you see what you’ve accomplished, what’s up next, and uncover cool patterns in your habits. This guide will walk you through building a powerful reading tracker from scratch, complete with automated stats and custom charts to visualize your progress.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Choose Your Essential Reading Categories

First, open a new Google Sheet and give it a name like "2024 Reading Log." The foundation of any good tracker is the set of data points you want to collect. Think about what you'd find most interesting to know about your reading habits. A great starting point includes the following columns in row 1:

• Title: The name of the book.
• Author: Who wrote it.
• Genre: Fiction, sci-fi, biography, etc.
• Format: Audiobook, physical book, ebook.
• Pages: The total number of pages.
• Start Date: When you started reading.
• End Date: When you finished.
• Rating: Your score out of 5.
• Status: Whether you're currently reading it, finished it, or plan to read it.

Your spreadsheet should look something like this. Bolding the headers and freezing the top row (View > Freeze > 1 row) will make it much easier to use as you add more books.

Supercharge Your Tracker with Formulas and Formatting

With the basic framework in place, we can bring your tracker to life. These next steps will automate calculations, keep your data clean, and make the sheet visually dynamic.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Create Dropdown Menus for Easy Entry

Repetitive typing is a waste of time and can lead to typos that mess up your data (e.g., "Fiction" vs. "fiction"). Data dropdowns solve this by giving you a pre-set list of options to choose from.

Let's create one for the Status column.

1. Select the entire Status column by clicking the column letter (e.g., 'I').
2. Go to the menu and click Data > Data validation.
3. Next to "Criteria," choose "List of items."
4. In the text box, enter your statuses, separated by a comma: To Read, Reading, Finished, Did Not Finish
5. Make sure "Show dropdown list in cell" is checked and click "Save."

Now, when you click on any cell in the "Status" column, a neat dropdown will appear. You can repeat this process for your "Format" and "Rating" columns (using 1, 2, 3, 4, 5 for ratings).

Use Conditional Formatting to Show Reading Progress

Conditional formatting changes a cell's appearance based on what's inside it. We can use it to automatically color-code each book based on its reading status. For example, completed books will turn green.

1. Select your entire data range, starting from A2 down to the last row (e.g., A2:I1000). You want to select all the rows where you'll have data, not the header.
2. Go to Format > Conditional formatting.
3. Under "Format rules," choose "Custom formula is."
4. In the value box, type the formula =$I2="Finished" (make sure to replace 'I' with the letter of your "Status" column). Note the dollar sign – it's important! It tells Google Sheets to always look at the status column when formatting the row.
5. Choose a green background color in the "Formatting style" section.
6. Click "Done."

Now, just click "Add another rule" and repeat the process for "Reading" (maybe a yellow background) and "Did Not Finish" (red background). This small touch makes it incredibly easy to see your progress at a glance.

Automate Reading Time Calculation

Let's add a "Days to Read" column (Column J). Instead of manually counting the days, you can make Google Sheets do the math for you. In cell J2, enter this formula:

=IF(I2="Finished", H2-G2, "")

Let's break that down:

• The IF function checks for a condition.
• I2="Finished" is the condition. It checks if the "Status" cell in that row is marked as "Finished."
• If it is, the formula calculates H2-G2 (End Date minus Start Date) to find the number of days it took to read.
• If the status isn't "Finished," it just leaves the cell blank ("").

Click on the small blue square in the bottom-right corner of cell J2 and drag it down. The formula will auto-apply to all your rows. Now, every time you finish a book, your reading time appears automatically.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Create a Dynamic Reading Dashboard

This is where your nerdy reader heart will truly sing. A dashboard gives you a high-level summary of your reading stats. Start by creating a new tab and naming it "Dashboard." This is where we'll put some key metrics and charts.

Calculate Your Key Reading Stats

In your new dashboard tab, set up some labels in column A like "Total Books Read," "Total Pages Read," and "Average Rating." In column B, we'll use formulas to pull the data from your main log sheet (let's say its name is 'Log').

For Total Books Read (in cell B1):

=COUNTIF(Log!I:I, "Finished")

This formula counts every row in Sheet1's "Status" column where the text is exactly "Finished."

For Total Pages Read (in cell B2):

=SUMIF(Log!I:I, "Finished", Log!E:E)

This is similar, but instead of counting, it sums the values in the "Pages" column (we assigned column E earlier) for every book marked "Finished."

For Average Reading Time (in cell B3):

=AVERAGE(Log!J:J)

This one's simple! It just finds the average of all the values in your "Days to Read" column.

Your simple dashboard should now look something like this and will update in real-time as you add to your log!

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Visualize Your Data with Charts

Numbers are great, but charts tell a story. Let's create two essential reading charts: books read per month and books read by genre.

Building a "Books by Genre" Pie Chart

A pie chart is perfect for showing your genre distribution. The easiest way to do this is with a Pivot Table.

1. Go to your 'Log' sheet and select all your data (including headers).
2. Click Insert > Pivot table.
3. Choose to place it on your "Existing sheet" and select a blank space on your "Dashboard" tab (e.g., 'D1'). Click "Create."
4. In the Pivot Table Editor on the right, click "Add" next to Rows and choose "Genre."
5. Now, click "Add" next to Values and also choose "Genre," making sure it is set to "COUNTA."

You'll now have a clean summary table showing a count of books for each genre. To turn this into a chart:

1. Select the data in your newly created pivot table.
2. Click Insert > Chart.
3. Google Sheets will likely recommend a pie chart, but if not, you can select it from the Chart Editor "Setup" tab.

And just like that, you have a beautiful visualization of your reading tastes!

Building a "Books Finished Per Month" Bar Chart

To create this chart, you first need to extract the month from your "End Date."

1. Go back to your 'Log' sheet and create a new column called "Month Finished" (Column K).
2. In cell K2, enter the formula: =IF(H2<>"",TEXT(H2,"YYYY-MM"),"")
3. This formula checks if the "End Date" (H2) cell isn't empty. If it has a date, the TEXT function formats it into a "Year-Month" format (like 2024-03). If not, it stays blank.
4. Drag this formula down the column, just like you did before.
5. Now, create another Pivot Table on your Dashboard tab. Set the Rows to "Month Finished" and the Values to use a COUNTA of "Month Finished."
6. Select your new pivot table, click Insert > Chart, and select a column or bar chart.

You now have a dynamic bar chart that tracks your reading pace throughout the year.

Final Thoughts

By following these steps, you’ve built a powerful, automated, and personalized book tracker right within Google Sheets. You can now effortlessly log your reading and get instant insights into your habits, all while getting more comfortable with highly useful spreadsheet skills.

Putting together custom trackers is a fantastic way to learn, but it reveals how manual reporting can get when you're dealing with lots of data from different places, like your company's marketing and sales tools. We built Graphed to solve this exact problem. Instead of wrestling with formulas and pivot tables, you can connect platforms like Google Analytics, Shopify, and Salesforce, and just ask questions in plain English - like "create a chart comparing Facebook Ads spend vs. revenue last month." Graphed instantly builds the live, auto-updating dashboard for you, turning hours of work into a 30-second conversation.