How to Create a Library Database in Excel

Tired of wondering who borrowed that C.S. Lewis book or trying to remember if you already own a specific title while browsing a bookstore? You can build a simple but powerful personal library database using a tool you probably already have: Microsoft Excel. This guide will walk you through setting up a detailed and surprisingly useful book catalog from scratch, step by step.

Graphed

Your AI Data Analyst to Create Live Dashboards

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

Build Your First Dashboard

Step 1: Plan Your Database Fields

Before you open Excel, take a moment to think about what information you want to track. A little planning now saves a lot of reorganization later. Grab a pen and paper or open a new note and jot down your ideal "fields" or columns. These are the specific pieces of information you want to record for each book.

Here’s a great starting list you can customize:

• Title: The full title of the book. (Essential)
• Author: The author's name. (Essential)
• Genre/Category: Helps you find books based on type (Fantasy, Business, Memoir, etc.).
• Format: Is it a Hardcover, Paperback, Ebook, or Audiobook?
• Publication Year: The year the book was originally published.
• ISBN: The 10 or 13-digit International Standard Book Number. It's the unique barcode on every book and great for precise identification.
• Date Acquired: The date you added the book to your library.
• Status: A crucial field for tracking. Is the book on your shelf (Owned), on loan to someone, on your Wishlist, or did you DNF (Did Not Finish)?
• Lent To: If the status is "On Loan," who has it?
• My Rating: Your personal rating, maybe on a 1-5 star scale.
• Date Read: The date you finished reading the book.
• Notes: A miscellaneous column for personal thoughts, quotes you liked, or where you got the book.

You don't need to use all of these! Pick the ones that matter most to you. The goal is to create a tool that is useful for you, whether you're a casual reader or a meticulous collector.

Step 2: Create and Format Your Table

With your plan in hand, it's time to fire up Excel. Open a new, blank workbook. The top row of your spreadsheet will become the headers for your database.

In cell A1, type your first field name (e.g., "Title"). In B1, type the next one ("Author"), and so on, until all your chosen fields from Step 1 have their own column. Don't worry about column widths yet, you can adjust them later by double-clicking the line between column letters (like between A and B) to automatically fit the content.

To make the headers stand out and stay visible as you scroll through your library:

1. Highlight the entire first row by clicking on the number "1" on the far left.
2. Go to the "Home" tab and click the "B" icon to make the text bold. You can also add a background color (the paint bucket icon) for more visual separation.
3. To keep the headers visible, go to the "View" tab on the ribbon, click "Freeze Panes," and select "Freeze Top Row." Now, when you scroll down your growing list of books, your headers will remain locked in place at the top.

Your blank database is now officially set up and ready for data!

AI for Data Analysis

Crash Course

Graphed

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.

Download Free Guide

Step 3: Enforce Consistent Data with Data Validation

This single step will elevate your spreadsheet from a simple list to a true database. For columns where you'll be using the same entries over and over again (like "Status," "Genre," or "Format"), you want to avoid typos and inconsistencies. For example, you don't want "Sci-fi," "Sci Fi," and "Science Fiction" all showing up as separate categories.

The solution is an Excel feature called Data Validation, which lets you create a dropdown list of pre-approved options. Let's set one up for the "Status" column.

1. Click the letter at the top of the "Status" column to select the entire column. (For this example, let's say it's column F).
2. Go to the "Data" tab on the Excel ribbon.
3. Find and click the "Data Validation" button. A dialog box will appear.
4. Under the "Settings" tab in the "Allow:" dropdown, choose "List".
5. A text box labeled "Source:" will appear. This is where you'll type your options, separated by commas. For our status example, you might type: Owned,On Loan,Wishlist,DNF
6. Click "OK."

Now, whenever you click a cell in your "Status" column, a small dropdown arrow will appear next to it. Clicking this arrow will show your list of options, allowing you to select one instead of typing it out. This guarantees your data is clean, consistent, and easy to filter later on.

Pro Tip: You can do the same for your Genre/Category column. Since this list might be long, you can type all your genres in a separate, unused column on the far right of your sheet (or on a different worksheet). Then, for the "Source:" box in the Data Validation settings, you can simply highlight that range of cells instead of typing them all out with commas.

Graphed

Your AI Data Analyst to Create Live Dashboards

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

Build Your First Dashboard

Step 4: Turn Your Data into a Smart, Sortable Table

Your spreadsheet functions okay as is, but with one more click, you can unlock modern sorting and filtering capabilities. The key is to format your data range as an official Excel Table.

This works best after you've entered at least one book into row 2 so the Table feature can see your data.

1. Click on any single cell within your data range (including your header).
2. Go to the "Insert" tab on the ribbon and click "Table."
3. A small box will appear confirming the range of your data. It should automatically detect your headers. Make sure the checkbox for "My table has headers" is ticked.
4. Click "OK."

Immediately, your data will be nicely formatted with alternating colors for each row (called "banded rows"). More importantly, an arrow will appear in each header cell. Clicking these arrows lets you instantly organize your entire database:

• Sorting: You can sort any column from A to Z or Z to A (or smallest to largest and vice versa for numbers). Want to see all your books alphabetized by author? Just click the arrow in the Author header and select "Sort A to Z."
• Filtering: This is where the magic happens. Click an arrow and you'll see a list of every item in that column. You can check off only the items you want to see. For example, in your Genre column, you can uncheck "(Select All)" and then tick the box next to "Fantasy" to see a list of only your fantasy books. The other books are temporarily hidden, not deleted. To bring them back, just go back to the filter and click "Clear Filter."

Step 5: Add Visual Cues with Conditional Formatting

Conditional Formatting automatically changes the appearance of a cell based on what's inside it. This is perfect for making important information pop. For a library database, an excellent use case is automatically highlighting books you've lent out.

Let's make it so any book with the status "On Loan" turns a bright, noticeable color:

1. Select the entire "Status" column by clicking the column letter at the top. Don't worry about the header - Excel is smart enough to ignore it.
2. On the "Home" tab, click "Conditional Formatting."
3. Hover over "Highlight Cells Rules" and then select "Text that Contains..."
4. In the box that appears, type the text you want to trigger the rule: On Loan
5. On the right, use the dropdown to choose your desired formatting. You can select a preset like "Yellow Fill with Dark Yellow Text" or choose "Custom Format..." for complete control over the font, border, and fill color.
6. Click "OK."

Now, anytime a cell in that column contains the exact text "On Loan," it will instantly change to the format you chose. This makes it impossible to miss who has your books with a quick glance.

Step 6: Use Formulas to Calculate Key Stats

Want a quick summary of your library without having to filter and count manually? A few simple formulas can give you a live dashboard of your collection's vital signs.

You can place these formulas in a clean area to the side of your table or directly above it.

Count Your Total Books

To count how many books are in your library, you can use the COUNTA formula, which counts any cell that isn't empty.

=COUNTA(Table1[Title])

Here, 'Table1' is the default name Excel gives your table, and '[Title]' refers to the Title column inside that table. This formula counts every entry in your Title column, giving you an exact book count that updates automatically as you add or remove books.

AI for Data Analysis

Crash Course

Graphed

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.

Download Free Guide

Count Books on Loan

To count books that meet a specific condition, you'll use the COUNTIF formula. It looks for a certain value within a range of cells.

=COUNTIF(Table1[Status], "On Loan")

This formula searches your "Status" column for the text "On Loan" and gives you a count of how many times it finds a match. Now you'll always know how many books you need to track down!

You can create similar formulas to track how many books are on your wishlist, your DNF count, or how many books are in a specific genre.

Final Thoughts

Combining Excel's Table formatting, Data Validation, Conditional Formatting, and a few basic formulas allows you to create a robust and personalized library database. You get a fully sortable, searchable catalog to manage your collection, track down borrowed books, and avoid buying duplicates. What starts as a simple list can quickly become an indispensable tool for any book lover.

While Excel is great for organizing personal data like a book collection, this kind of manual setup and spreadsheet management can quickly become a bottleneck for business data. For marketers and sales teams who need to analyze data from sources like Google Analytics, Shopify, and Salesforce all at once, the process of exporting and wrangling data is a constant time-sink. That's precisely why we built Graphed. We connect to your data sources and use AI to build real-time dashboards and reports for you, so you can just ask questions in plain English and get instant answers without any of the manual work.