Library Register Format in Excel: Fee Sheet Layout

A paper register works until a student pays ₹400 now and ₹300 next week, renews for 3 months instead of 1, and moves from seat 14 to seat 31. After that, the pages fill with cuttings. A spreadsheet handles all three if the columns are set up right from the start.

This is a format you build yourself in about 20 minutes, not a file to download. Librify is a library management app for private self-study libraries and reading rooms in India; at the end we cover where a sheet stops being enough.

Why three sheets, not one

Most homemade registers use one row per student and overwrite the dates at every renewal. That loses the history: you cannot see that a student paid late in March, or what they paid before a fee increase. Splitting the register fixes this.

• Students: details that rarely change, such as name, mobile and admission date.
• Plans: one row each time a student starts or renews a plan. A student who renews monthly for a year has 12 rows.
• Payments: one row for every rupee that comes in, linked to a plan. Two part-payments are two rows.

This is the same structure library apps use behind the screen. Keeping it in a sheet means your data moves cleanly into an app later; see the register to app migration guide for that step.

Sheet 1: Students

Name the first sheet Students and put these headings in row 1.

Keep full Aadhaar numbers out of this sheet. If you must record ID, note the type and the last 4 digits. A shared spreadsheet is easy to forward by mistake.

Sheet 2: Plans, with formulas

Name the second sheet Plans. You type columns A to J; columns K onward calculate themselves. Put the formulas in row 2 and drag them down 500 rows.

What each formula does

• End date (H): EDATE adds whole months to the start date, and the -1 makes a plan from 5 November end on 4 December, not 5 December. See the Microsoft EDATE reference or the Google Sheets EDATE reference.
• Paid (L): SUMIF adds every payment on the Payments sheet that carries this Plan ID, so two part-payments add up automatically. The Google Sheets SUMIF page explains the arguments.
• Status (O): gives one of five words, so you can filter the sheet for "Overdue" every morning instead of scanning dates.
• Seat clash (P): counts other plans with the same shift and seat whose dates overlap this one. Any "CLASH" means a seat is double-booked.

If your library bills on the 1st of every month instead of the joining date, the end date is simply the last day of the month: =EOMONTH(G2,F2-1).

Sheet 3: Payments

Name the third sheet Payments. Every payment gets its own row the moment it is received, cash or UPI.

The "UPI ref" column is the one most owners skip and later regret. Copy it from your own bank or UPI app when the credit shows, which also confirms the money arrived.

Daily and monthly totals

Add a small fourth sheet named Summary for the numbers you check at closing time.

Match "Cash received today" against the cash drawer before you close. Our daily cash reconciliation guide covers the expense side and what a CA needs at year end.

Making the sheet hard to break

1. Add dropdowns (Data validation) for Shift and Mode, so "Morning", "morning" and "Mrng" do not count as three shifts.
2. Colour the Status column with conditional formatting: red for Overdue, amber for Ending soon.
3. Lock columns H and K to P so nobody types over a formula.
4. Freeze row 1 and column C, so the name stays visible on a phone screen.
5. Make a copy of the file on the 1st of every month and name it with the month.

When a sheet stops being enough

A well-built sheet is enough for one owner taking fees at one desk. It starts to strain when two people take fees, because both edit the same file and one of them forgets a row. It also cannot message anyone: you still type each reminder, using formats like our fee reminder message templates.

Librify keeps the same structure, a student with a series of plans and payments, but fills in the dates and balances as you record them. The Payment Due dashboard replaces the Overdue filter, partial payments carry a remaining balance, and the seat picker works from the seat map for the chosen shift and dates, so a taken seat shows up before you save. Details are on the subscription tracking page.

If your fee amounts are changing at the same time, read how to raise library fees without losing students before you set the new Plan fee column.

Frequently asked questions

What columns should a library fee register have?

At minimum: member ID, student name, mobile, shift, seat, plan months, start date, end date, fee, discount, amount payable, amount paid, balance and status. Keep payments on a separate sheet, one row per payment, so a student who pays in two parts does not need two rows in the main register.

How do I calculate the library plan end date in Excel?

Use =EDATE(start_date, months)-1. For a plan starting 5 November for 1 month, it returns 4 December, so the student gets exactly one month. The same formula works in Google Sheets. Format the cell as a date, or it will show a number like 46360.

Can I use Google Sheets instead of Excel for a library register?

Yes. Every formula in this layout works in both. Google Sheets is easier when two people update the register from their phones, because the file is shared online. Restrict edit access to the people who collect fees, and do not store full Aadhaar numbers in a shared sheet.

Library register Excel me kaise banaye?

Create three sheets named Students, Plans and Payments. Copy the column headings from the tables in this post into row 1 of each sheet, paste the formulas into row 2, and drag them down. Add a dropdown for shift and payment mode so entries stay consistent.

When should a library move from an Excel register to an app?

When two or more people take fees, when you send more than 20 reminders a month, or when you find a seat booked twice. A sheet records data well but does not message students, warn about seat clashes as you type, or send receipts.