How to Catalog Books in Excel (2 Easy Methods)

Excel is now widely used to track records of inventories such as supply chains or libraries for book collection management. One of the most common usages is to make a catalog of the books that are in a library or stationery so that we can find them easily upon searching. In this article, we will learn how to catalog books in Excel in different methods.

How to Catalog Books in Excel: 2 Easy Methods

There are two answers to how to catalog books in Excel. One of them is the Microsoft-provided book catalog template for generalized usage. Another one is to make a catalog structure by ourselves because the structure varies according to purposes and needs. So, we will first learn the method of creating a custom book catalog. Then we will head on to the Microsoft-provided one so that we can edit that structure according to our needs.

1. Creating a Custom Catalog for Books

In this method, we emphasize the needs and applications of the book catalog like where we are using it, the size, the information that is necessary, etc. while making it. This method is applied when an organization needs to make a catalog in Excel and use it for long-term use. The steps of this method are below.

🔶 Steps:

First, we will select the information or headings that we need to be on the catalog. For example, we want to catalog library books. So, we will need the information in the image below.

Second, we will separate these columns or fields by the borderline.

Third, we will select data types for the respective fields. For example, for the Date of Publication, we will set the data type as Date or for the Shelf No. we can set data types as numbers. To do so, we will select a field like the below image.

Then we will press Ctrl+1 on the keyboard to open the Format Cells In the Format Cells tab, select any data type and press Enter. As a result, the field will accept only that data type, and anything else will be converted into that data type forcefully. For example, we selected Date as the data type for The Days of Publication field.

Next, our catalog may carry thousands of books, so while scrolling, we still want the top line or header line visible even if we scroll down. To do so, we will need to freeze the upper header row. So, we will select the row below the header row and go to the View tab>Window>Freeze Panes.

We can also add a filter to find books in the catalog easily by going to the Data tab in the Ribbon and ticking the Data Filter option like in the image below.

Finally, we will be able to insert our data in the table according to our needs. We will get the catalog after inserting data like the following.

2. Using the Microsoft-Provided Catalog Template

Microsoft has provided a book catalog template on its website. It is quite a generalized version of what we have done in the upper method. The steps are below.

🔶 Steps:

First, click on this link (Book collection list) to download the template from Microsoft’s website.

Then open the Excel application. Click on Open. Select the downloaded template. This will open the template.
Furthermore, the primary look of the template is like in the following image.

We can use the template right away as it is, or we can add or delete some fields to make it easier for us to use.

In the end, we will need to save the file as a .xlsx file. To do so, we will click on Files>Save As. Then choose the extension as .xlsx file.

Things to Remember

While custom cataloging books in Excel, it is recommended to give the filter button. This will let us choose certain criteria easily.
The Microsoft-provided template will be downloaded as .xlts file or an Excel template file. So, it’s important to save it as .xlsx file.
We will delete any column or field that is not needed in the catalog.

Download Practice Workbook

You can download the practice workbook here.

Book Catalog.xlsx

Conclusion

These are the two methods to catalog books in Excel. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions.

<< Go Back To Catalogue in Excel | Hyperlink in Excel | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!