Library Management System in Excel: Books, Members, Issue and Return, Due Dates, Late Fines and Availability

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: library-management-system-template.xlsx: a 8-sheet system with example data and real formulas (Read me, Setup, Books, Members, Issues, Report, Issue slip, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Ruhi Reading Room, Pune, as of 17 October 2026 (made-up data). Loan 14 days; fine ₹2 a day late; up to 3 books per member.

The librarian looks at one sheet to see who to remind today.

2. Build it sheet by sheet

Step 1. Setup: rules

1. B4 loan days (14), B5 fine per day (₹2), B6 max books per member (3). Categories in D4:D9.

Step 2. Issues: due date and fine

1. A5 ="IS-"&TEXT(ROW()-4,"000"). Type issue date, member ID and book ID from drop-downs.
2. F5 due =B5+Setup!$B$4.
3. J5 days late =MAX(0,IF(G5="",Setup!$B$3,G5)-F5) and H5 fine =J5*Setup!$B$5.
4. I5: =IF(G5<>"","Returned",IF(Setup!$B$3>F5,"Overdue","Issued")), red when Overdue (conditional formatting).

Step 3. Books and Members

1. Books E5 out =COUNTIFS(Issues!$D$5:$D$3000,A5,Issues!$I$5:$I$3000,"<>Returned") (COUNTIFS), F5 available =D5-E5.
2. Members D5 books out the same way, E5 can take more =MAX(0,Setup!$B$6-D5), F5 fines with SUMIFS.

Step 4. Report and Issue slip

1. Report per category: titles, copies, out and issues.
2. Issue slip: type an issue no in B4; rows 6 to 12 bring the book, due date and member. Set the print area.

Step 5. Dashboard

1. KPI cards: titles, copies, books out, overdue, fines due and active members.
2. Charts: issues by category and fines by member.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Fine counted only on return. Count days late to today too, so overdue books show what they will cost.
2. One row per title with several copies issued. Each issue is its own row; copies out are counted from Issues.
3. Deleting returned rows. Keep them: times issued and fines need the history.
4. Typing the due date by hand. Due = issue date + loan days. Change loan days on Setup, not in each row.
5. Issuing past the limit. Check Members column E before issuing a fourth book.

5. More real examples

Example 1: Office document room. Files instead of books, issued to staff with a return date.

Example 2: Toy library. Toys with deposit and condition on return.

Example 3: Equipment lending. Cameras, tools or projectors with a daily fee, like the photography studio system.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate a due date in Excel?

Issue date + loan days, as in Issues F5.

How do I calculate a library fine?

Days late × fine per day; days late = MAX(0, return date or today − due date).

How do I count copies available?

Copies − COUNTIFS of issues for that book whose status is not Returned.

How do I stop a member taking too many books?

Count their books out with COUNTIFS and compare with the limit on Setup.

How do I see overdue books?

Filter Issues column I for Overdue; it compares the due date with today.

7. Quick quiz

Ten questions on this page. Sign in to save your score.