Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create a Reading Tracker in Google Sheets

Cody Schneider••9 min read

Building your own reading tracker in Google Sheets is a fantastic way to visualize your habits, achieve your reading goals, and remember every book you’ve enjoyed. Instead of relying on a pre-made template that doesn't quite fit your style, you can build a personalized system from the ground up. This tutorial will guide you through creating a powerful and beautiful reading tracker, complete with a summary dashboard and charts, even if you’re not a spreadsheet wizard.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

Setting Up Your Digital Bookshelf

First things first, let’s lay the foundation. This is where you'll log every book you pick up. A clean and well-organized structure here will make all the fun analysis and reporting much easier later on.

Open a new Google Sheet and name it something like "My 2024 Reading Tracker." Rename the first tab at the bottom from "Sheet1" to "📚 Reading Log." Now, create the following headers in the first row:

  • A: Book Title - The name of the book.
  • B: Author - Who wrote it.
  • C: Genre - Helps you spot trends in your reading tastes.
  • D: Format - Paperback, Hardcover, Ebook, Audiobook, etc.
  • E: Start Date - When you started reading.
  • F: End Date - When you finished (or stopped).
  • G: Pages - The total number of pages in the book. If you're tracking audiobooks, you can use hours here instead.
  • H: My Rating - Your personal rating, on a scale of 1-5.
  • I: Status - Are you currently reading, finished, or did you drop it?
  • J: Days to Read - An automated column to see how long it took you.
  • K: Cover - A fun column to display the book's cover image.

Your spreadsheet should now look clean and ready to go. Go ahead and freeze the top row so your headers are always visible as you scroll. You can do this by clicking View > Freeze > 1 row.

Adding Smart Features for Easy Tracking

Manually typing everything can lead to typos and inconsistencies. Let's add some features that make logging your books faster and keep your data clean.

Create Dropdown Menus for Clicks, Not Typos

For columns like "Status," "Format," and "Genre," using a dropdown menu prevents you from accidentally typing "Finshed" instead of "Finished" and messing up your counts later. Let's set one up for the "Status" column.

  1. Select the entire "Status" column by clicking the letter I at the top.
  2. Go to the menu and click Data > Data validation.
  3. In the sidebar that appears, under Criteria, select List of items.
  4. In the text box, enter your statuses separated by commas: Finished, Reading, DNF, To Read (DNF stands for "Did Not Finish").
  5. Make sure Show dropdown list in cell is checked and click Done.

Now, when you click on any cell in the "Status" column, a neat dropdown will appear. Repeat this process for the "Format" column (e.g., Paperback, Hardcover, Ebook, Audiobook) and the "Genre" column with a few of your favorites (e.g., Fiction, Non-Fiction, Sci-Fi, Fantasy, Mystery, Biography).

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Automate "Days to Read" with a Simple Formula

Let's make Google Sheets do the math for us. You can calculate how many days it took to read a book by simply subtracting the 'Start Date' from the 'End Date'.

Click on cell J2 (the first cell under your "Days to Read" header) and enter this formula:

=IF(F2<>"", F2-E2, "")

Let's break that down: IF(F2<>"", ...) checks if the 'End Date' cell is not empty. If it has a date, it calculates F2-E2. If it’s empty, it just leaves the cell blank "". This keeps your sheet looking clean for books you're still reading. To apply this formula to the whole column, click on cell J2, then grab the small blue dot in the bottom-right corner and drag it all the way down the column.

Add Visual Star Ratings

Numbers are great, but visual ratings are more fun. We can automatically convert your 1-5 rating in the "My Rating" column into star emojis.

Let's add one more column in column L called "Stars". Then, in cell L2, enter this formula:

=REPT("⭐", H2)

The REPT function repeats a piece of text a specified number of times. In this case, it repeats the star emoji (⭐) based on the number you enter in cell H2. If you rate a book a 4, you'll see four stars appear instantly. Drag this formula down the column to apply it to all your books.

Building Your Personal Reading Dashboard

Now for the most exciting part: summarizing and visualizing your data. We'll create a dedicated dashboard on a new tab to see your progress at a glance.

Create a new tab by clicking the "+" icon at the bottom left and rename it Dashboard. This is where we will calculate key stats and create charts.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

Track Your Key Reading Metrics

Let's set up a small area at the top of your Dashboard sheet to see your most important numbers for the current year.

Type the following labels in column A:

  • A2: Books Finished This Year
  • A3: Total Pages Read This Year
  • A4: Average Rating (All-Time)
  • A5: Book You're Currently Reading

Now, let's add the formulas in column B to pull the data automatically from your 'Reading Log' sheet.

Books Finished This Year

In cell B2, use the COUNTIFS function, which counts rows that meet multiple criteria. We want to count books that are marked "Finished" and were completed within the current calendar year.

=COUNTIFS('📚 Reading Log'!I:I, "Finished", '📚 Reading Log'!F:F, ">="&DATE(YEAR(TODAY()), 1, 1), '📚 Reading Log'!F:F, "<="&DATE(YEAR(TODAY()), 12, 31))

This formula may look intimidating, but it's just telling Sheets: Count a book if its status (Column I) is "Finished" AND its end date (Column F) is on or after January 1st of this year AND on or before December 31st of this year.

Total Pages Read This Year

We'll use a nearly identical formula, but swap COUNTIFS for SUMIFS to add up the pages instead of just counting the books.

=SUMIFS('📚 Reading Log'!G:G, '📚 Reading Log'!I:I, "Finished", '📚 Reading Log'!F:F, ">="&DATE(YEAR(TODAY()), 1, 1), '📚 Reading Log'!F:F, "<="&DATE(YEAR(TODAY()), 12, 31))

Average Rating

This one's much simpler. We don't need to filter by year - let's just get your average timeless taste!

=AVERAGE('📚 Reading Log'!H:H)

Book You're Currently Reading

To pull the title of the book you're actively reading, we can use VLOOKUP.

=VLOOKUP("Reading", {'📚 Reading Log'!I:I, '📚 Reading Log'!A:A}, 2, FALSE)

This formula looks for the word "Reading" in your status column and returns the corresponding book title from the first column. Note: this will only show the first book it finds listed with the "Reading" status.

Create a Reading Goal Progress Bar

Want to track your progress toward an annual reading goal? Let's add that. In cell A7, type a label like "Annual Goal" and in B7, type your goal number (e.g., 50).

Next, in cell B8, pop in this cool SPARKLINE formula to create a mini progress bar:

=SPARKLINE(B2, {"charttype", "bar", "max", B7})

This formula takes the value from B2 (Books Finished This Year) and visualizes it as a bar chart inside the cell, with the maximum value of the bar set to your goal in B7. As you finish more books, the bar will fill up!

Visualizing Your Habits with Charts

Charts are the best way to spot patterns you might otherwise miss. Let’s create a couple of visuals for our dashboard.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Books by Genre

To chart your most-read genres, we first need a small summary table. A little ways down on your Dashboard sheet (say, starting in cell D2), list your top genres in one column and we'll count them in another.

Your Mini-Table:

  • D3: Fiction
  • D4: Non-Fiction
  • D5: Sci-Fi
  • ....and so on for your most common genres.

In cell E3 (next to "Fiction"), enter this formula:

=COUNTIF('📚 Reading Log'!C:C, D3)

This counts how many times the genre in cell D3 ("Fiction") appears in your log. Drag this formula down next to your other genres to count them all.

Now, to create the chart:

  1. Select your summary data (from D3 to the last genre and count).
  2. Go to Insert > Chart.
  3. Google Sheets will likely suggest a Pie Chart, which is perfect for this. If not, you can select it from the "Chart type" dropdown in the Chart editor sidebar.
  4. Customize the title and colors as you see fit!

Books Read Per Month

A column chart showing your reading pace throughout the year is an excellent motivator. This also requires a quick summary table.

Your Mini-Table:

In column G, list the months of the year (e.g., "January", "February", etc.). In column H, we will use a COUNTIFS formula for each month.

For January (in cell H2, next to "January"), you would use:

=COUNTIFS('📚 Reading Log'!I:I, "Finished", '📚 Reading Log'!F:F, ">="&DATE(YEAR(TODAY()), 1, 1), '📚 Reading Log'!F:F, "<"&DATE(YEAR(TODAY()), 2, 1))

This counts all finished books with an end date in January of the current year. For February, you would change the numbers to DATE(YEAR(TODAY()), 2, 1) and DATE(YEAR(TODAY()), 3, 1), and so on for each month.

Once your table is filled out, select the data, go to Insert > Chart and choose a Column Chart to visualize your monthly reading trends.

Final Polish and Customizations

Your tracker is fully functional, but here are a few more tips to make it even better.

  • Conditional Formatting: Make your favorite books stand out! Select your entire data range in the 'Reading Log' (from A2 down to your last entry), then go to Format > Conditional Formatting. Set the rule to "Custom formula is" and enter =$H2=5. Choose a background color (like light green). Now, any book you rate a 5 will be highlighted automatically.
  • Book Covers: In the "Cover" column (K), you can use the IMAGE function to display the book's cover. Find an image of the book cover online, copy its image address, and then use the formula like this: =IMAGE("URL_PASTED_HERE"). It adds a wonderful visual touch to your log.

Final Thoughts

By following these steps, you've transformed a blank Google Sheet into a dynamic, automated reading tracker that gives you real insights into your habits and progress. It’s a rewarding project you can use and tweak for years to come.

Creating trackers like this is empowering, but building reports for your business from scratch can quickly eat up your time. Manually gathering data from Google Analytics, Shopify, and Salesforce to put in a spreadsheet is often a weekly chore. We created Graphed to automate this entirely. Instead of wrestling with complex formulas, you connect your business apps and then ask for what you need in plain English - like "create a dashboard comparing Facebook Ads spend vs revenue by campaign" - to get an automated, live dashboard in seconds.

Related Articles