A missing batch column turns every expiry into a surprise loss
Say a pharmacy owner in Ahmedabad maintains a medicine stock register in Excel with columns for Item Name, Quantity, and MRP. That covers sales. It does not cover batches. When two batches of the same SKU arrive on different dates — one expiring in February, one in August — they both sit under the same row. The February batch should sell first (this is the FEFO rule: first expiry, first out), even if it arrived later. Without a dedicated Batch Number column and a separate Expiry Date column, there is no way to enforce that order.
Without batch-level columns, even a careful pharmacist billing manually cannot reliably pick the earlier-expiring batch every time.
The columns that eliminate this category of loss are:
- Batch Number — one row per batch, not one row per SKU
- Expiry Date — in a sortable format (MM/YYYY or DD/MM/YYYY, consistently applied)
- Date of Purchase — so FEFO can be cross-checked against arrival date when batches from the same supplier arrive close together
- Quantity In / Quantity Out / Balance — updated at every transaction, not weekly
Without these four columns working together, the expiry date column alone tells you when stock will expire. It does not tell you whether the person billing today will pick the right batch.
---
The Schedule H1 register is not optional — and a single column gap is a compliance failure
Rule 65(3)(h) of the Drugs and Cosmetics Rules is specific: the supply of a Schedule H1 drug "shall be recorded in a separate register at the time of the supply giving the name and address of the prescriber, the name of the patient, the name of the drug and the quantity supplied and such records shall be maintained for three years and be open for inspection."
That sentence names what each Schedule H1 entry must carry: prescriber name, prescriber address, patient name, drug name and quantity supplied, recorded at the time of the supply. An Excel register that keeps H1 items inside the general stock sheet, or leaves out the prescriber address, does not meet that wording.
Selling a Schedule H1 drug without a valid prescription is an offence under Section 27(d) of the Drugs and Cosmetics Act, 1940, carrying imprisonment of one to two years and a fine of not less than ₹20,000.
The three-year retention requirement means your Excel file for H1 transactions from October 2023 must still be accessible, intact, and searchable today. If that file lives on one staff member's laptop, the risk is structural, not accidental.
---
GST input credit slips away when your purchase register misses two columns
The GST Council reduced the rate on most medicines under HSN 3004 to 5% (the 12% slab was withdrawn from 22 September 2025 following the 56th GST Council decision). Input tax credit on those purchases is claimable only if the purchase register ties back to a valid tax invoice with the correct HSN code and the supplier's GSTIN.
A medicine stock register format in Excel that records only Item Name, Quantity, and Rate is not a purchase register. It becomes one only when it includes:
- Supplier GSTIN
- Invoice Number and Invoice Date — exactly as printed on the distributor's bill
- HSN Code (3004 for most medicines)
- GST Rate and GST Amount — separately, not folded into MRP
When these columns are absent or filled inconsistently, your CA cannot reconcile GSTR-2B at month-end. Mismatches between your purchase register and the supplier's filed invoices result in credit reversals.
---
What the register looks like when every column is working
Here is a side-by-side of what a pharmacy stock register in Excel typically captures versus what a complete format needs:
| Column | Typical Excel register | Complete format |
|---|
| Item identification | Item name only | Item name + HSN code + schedule (H, H1, X, or none) |
| Batch tracking | Often absent or single row per SKU | Batch number + expiry date, one row per batch |
| Purchase record | Quantity + rate | Quantity + rate + supplier GSTIN + invoice number + GST amount |
| Sales record | Quantity sold | Quantity sold + date + batch selected (for H1: patient name, prescriber name, prescriber address) |
| Balance | Updated manually, often weekly | Running balance updated at every transaction |
| Expiry action | None | Flagged 60/30 days before expiry for return or discount decision |
The difference between the left column and the right column is not complexity. It is discipline applied consistently across every transaction.
The after-state looks like this: every batch on the shelf has a corresponding row in the register with its expiry date visible. Items expiring within 30 days appear on a daily review list with their rupee value. H1 transactions have a complete record created at the moment of billing. Purchase invoices tie back to GST data without manual reconciliation. The pharmacist's time goes to the counter, not to correcting last week's entries.
---
What this looks like inside Nesayo
Picture the morning a batch of Pantoprazole 40 mg (HSN 3004) crosses into the 30-day expiry window in a pharmacy running on Nesayo. On the Full plan (₹549/month as of October 2026), the Expiry Guard agent flags it and can draft an expiry return for the pharmacist to check. Nothing changes until the pharmacist approves the action. There is no automation that acts without confirmation.
At the billing counter, Nesayo's FEFO batch selection picks the earliest-expiring batch of an item automatically. Once a batch's expiry month has passed — a pack printed EXP 08/2026 stays sellable through 31 August 2026 — that batch is refused at billing. The pharmacist cannot accidentally bill it out.
For Schedule H1 medicines, the billing screen prompts for the patient name, prescriber name, and quantity at the time of sale. The pharmacist adds the prescriber's address. The record is created at billing, not reconstructed later from memory.
If the pharmacy's current stock lives in an Excel file or a CSV, Nesayo can import it directly on the Essential plan (₹249/month as of October 2026) and during the free trial — so the transition from an Excel-based pharmacy stock register format to batch-level tracking does not require retyping every SKU by hand. On the Full plan (₹549/month as of October 2026), a Tally export is available so the purchase data flows into the accounts without a second entry. Manual billing and manual stock addition remain free forever.
On the Full plan, Nesayo gives reorder suggestions.
---
The choice in front of you
Leaving the register in Excel is not a neutral decision. It is a daily vote for the status quo: expiry losses that stay invisible until month-end, H1 records that depend on one person remembering to fill in six columns consistently, and GST input credit that requires manual reconciliation to claim. The columns described in this post are not difficult to build into a spreadsheet. The difficulty is filling them in correctly, every transaction, every day, without a system that enforces it.
Spend 2 minutes at nesayo.com/demo — a sample pharmacy is pre-loaded, no signup required. See what an expiry queue looks like when every batch has a date, and what the H1 billing screen prompts for at the moment of sale.
---
FAQ
What columns should a medicine stock register in Excel include?
At minimum: item name, HSN code, schedule (H, H1, X, or none), batch number, expiry date, date of purchase, quantity in, quantity out, running balance, supplier name, invoice number, supplier GSTIN, GST rate, and GST amount. For Schedule H1 items, a separate register must also capture the patient name, prescriber name, prescriber address, drug name, and quantity — recorded at the time of supply, per Rule 65(3)(h) of the Drugs and Cosmetics Rules.
How long do I need to keep the pharmacy stock register?
For Schedule H1 drugs, Rule 65(3)(h) of the Drugs and Cosmetics Rules requires records to be "maintained for three years and be open for inspection." For general purchase and sales records, GST law requires retention of six years from the last date of filing the annual return for the relevant year. Check with your CA or state licensing authority for any additional state-level requirements.
Is FEFO a legal requirement in India for retail pharmacies?
There is no single statute that uses the phrase "FEFO" as a retail pharmacy obligation in the way Schedule H1 recordkeeping is prescribed. However, selling expired medicines is an offence under the Drugs and Cosmetics Act, and FEFO is the standard practice recommended in quality management guidelines.
What is the HSN code for medicines in a stock register?
Most pharmaceutical preparations — tablets, capsules, syrups, injectables — fall under HSN 3004. The GST rate on most medicines under HSN 3004 is 5% following the 56th GST Council decision, which withdrew the 12% slab from 22 September 2025. Verify the specific HSN and rate for your product with your distributor's invoice or your CA, since some formulations and medical devices carry different codes.
How do I manage two batches of the same medicine in an Excel stock register?
Each batch should occupy its own row, with its batch number, expiry date, quantity in, quantity out, and running balance tracked separately. When billing, always pick the row with the earlier expiry date first. In a high-volume pharmacy, this is difficult to enforce manually because the billing staff member has to visually check the register for every sale.
Can I claim GST input credit on medicines I purchased if the batch later expires?
If the batch expires and you write it off, any GST input tax credit already claimed on it must be reversed under Section 17(5)(h) of the CGST Act.
Purchase alone is not enough to claim the credit. You must meet every applicable condition of Section 16 of the CGST Act. These include:
- Holding the tax invoice: Section 16(2)(a).
- The supplier reporting the invoice details, and those details being communicated to you: Section 16(2)(aa).
- Receiving the goods: Section 16(2)(b).
- The credit details communicated to you under Section 38 not being restricted: Section 16(2)(ba).
- The tax actually being paid to the government: Section 16(2)(c).
- Filing your return: Section 16(2)(d).
This is not the complete list. Ask your CA to verify the treatment of your expired stock based on your current filing position.
medicine stock register format in excel free download — is a template enough?
A template gives you the column structure, but it cannot enforce that every transaction fills in every column correctly, flag expiries in advance, or prevent a staff member from billing an expired batch. A template is a reasonable starting point for a very low-volume pharmacy or during setup. As transaction volume grows, the gaps in template-based registers compound: incomplete rows, inconsistent batch numbers, and H1 records that are reconstructed after the fact rather than captured at billing.
How do pharmacies in India handle Schedule H1 register audits?
A Drug Inspector visiting the premises can ask to see the Schedule H1 register on the spot. The register must show each supply with the prescriber's name and address, the patient's name, the drug name, and the quantity — recorded at the time of supply. A register with missing prescriber addresses or entries that appear to have been written in bulk rather than transaction by transaction is likely to attract scrutiny.