Ankeshan

Batch & Expiry Tracking in Excel (Pharma/FMCG)

Last updated: 27 June 2026

For pharma and FMCG businesses, each stock lot must carry a batch number and expiry date — Excel tracks both using a batch-level transactions table, TODAY() to calculate days-to-expiry in real time, and conditional formatting to colour-code items expiring within 30, 60 and 90 days. This guide builds a complete batch register: one row per batch per SKU, with live expiry alerts and FIFO-ready issue tracking.

Key takeaways

  • Record every goods receipt at the batch level: one row per batch, with Batch No, Mfg Date, Expiry Date, Qty and Supplier.
  • Use =Expiry_Date - TODAY() to calculate days-to-expiry dynamically — no manual updates needed.
  • Apply three-tier conditional formatting: red (expiring within 30 days), orange (31–60 days), yellow (61–90 days).
  • Issue stock in FIFO order (nearest expiry first) — this is both a regulatory requirement and common sense.
  • A "Near Expiry" alert column (=IF(Days_to_Expiry<=30,"NEAR EXPIRY","OK")) feeds a summary count for the buyer.
  • Batch traceability is mandatory for drug-licensed businesses and increasingly expected for food businesses under FSSAI.

Fact box. Under the Drugs and Cosmetics Act 1940 and Rules 1945, licensed pharmaceutical manufacturers, wholesalers and distributors are required to maintain a batch register recording batch number, manufacturing date, expiry date, quantity received and quantity issued for every drug product. An Excel register covering these fields is fully acceptable provided it is kept up to date and available for inspection.

Why does batch tracking matter for Indian pharma and FMCG businesses?

Three regulatory and commercial reasons:

  1. Drug License compliance: Pharma wholesalers and retailers must maintain batch-wise records. During a drug inspector's visit, you must be able to show for any batch: when it came in, from whom, what quantity, and where it went.
  2. FSSAI and food safety: Food businesses under FSSAI must maintain traceability records. In a product recall, you need to identify which customers received a specific batch — batch records make this possible.
  3. ITC and stock reconciliation: When a batch expires and is written off, the corresponding Input Tax Credit may need to be reversed. Batch records let you match the write-off to the original purchase invoice and GST amount.

How do I set up a batch register in Excel?

Step 1 — Build the Batch Receipt sheet

Create a sheet named BatchReceipts with these columns, converted to an Excel Table:

Batch ID Item Code Item Name Batch No Mfg Date Expiry Date Supplier GRN No Receipt Date Qty Received Qty Remaining Days to Expiry Alert
  • Batch ID: a unique identifier for this specific receipt (e.g. BR-001, BR-002). This is your internal tracking key, distinct from the supplier's Batch No.
  • Batch No: the manufacturer's batch or lot number exactly as printed on the pack.
  • Qty Remaining: starts equal to Qty Received; updated when stock is issued (see Step 3).

Step 2 — Add the Days to Expiry formula

In the Days to Expiry column (L2):

=IF([@[Expiry Date]]="", "", [@[Expiry Date]] - TODAY())

This calculates a live countdown. A negative value means the batch has already expired.

Format the column as Number (0 decimal places) — not as Date.

Step 3 — Add the Alert column

In the Alert column (M2):

=IF([@[Qty Remaining]]<=0, "CLEARED",
  IF([@[Expiry Date]]="", "",
    IF([@[Days to Expiry]]<=0, "EXPIRED",
      IF([@[Days to Expiry]]<=30, "NEAR EXPIRY",
        IF([@[Days to Expiry]]<=90, "WATCH", "OK")))))

This gives five clear states:

  • CLEARED — batch fully issued; ignore.
  • EXPIRED — past expiry date; must be quarantined or returned.
  • NEAR EXPIRY — 30 days or fewer remaining.
  • WATCH — 31–90 days remaining.
  • OK — more than 90 days remaining.

Fact box. The 30-day and 90-day alert thresholds above are a practical default, not a regulatory requirement. Businesses dealing in short-shelf-life products (fresh food, some biologics) should set tighter thresholds — for example, 7 days "near expiry" and 21 days "watch". Adjust the formula numbers to match your actual product shelf life.

How do I apply colour-coded conditional formatting for expiry?

Select the entire table range (excluding headers). Apply these four rules in order (manage them in Home → Conditional Formatting → Manage Rules):

Rule 1 — Red: Expired

  • Formula: =$M2="EXPIRED"
  • Format: Red fill, white bold font.

Rule 2 — Dark Orange: Near Expiry

  • Formula: =$M2="NEAR EXPIRY"
  • Format: Orange fill, black bold font.

Rule 3 — Yellow: Watch

  • Formula: =$M2="WATCH"
  • Format: Yellow fill, black font.

Rule 4 — Grey: Cleared

  • Formula: =$M2="CLEARED"
  • Format: Light grey fill, grey font (de-emphasise fully issued batches).

Ensure Rule 1 (Expired) is at the top of the rules list so it takes priority.

How do I track batch issues (FIFO)?

Create a second sheet named BatchIssues:

Issue Date Sales Invoice No Item Code Batch ID (from receipt) Qty Issued Issued to (Customer/Dept)

When issuing stock:

  1. Always issue from the batch with the nearest expiry date first (FIFO on expiry date).
  2. Look up the Batch ID from BatchReceipts for the item you are issuing.
  3. Record the Batch ID in the BatchIssues sheet.
  4. Update Qty Remaining in BatchReceipts for that Batch ID.

Qty Remaining formula (in BatchReceipts):

=[@[Qty Received]] - SUMIF(BatchIssues[Batch ID], [@[Batch ID]], BatchIssues[Qty Issued])

This auto-calculates how much remains in each batch after all recorded issues.

How do I build a near-expiry summary report?

Add a summary block on a Dashboard sheet or at the top of BatchReceipts:

Count of batches expiring within 30 days:

=COUNTIFS(BatchReceipts[Alert], "NEAR EXPIRY")

Count of expired batches with remaining stock:

=COUNTIFS(BatchReceipts[Alert], "EXPIRED", BatchReceipts[Qty Remaining], ">"&0)

Total qty of near-expiry stock:

=SUMIF(BatchReceipts[Alert], "NEAR EXPIRY", BatchReceipts[Qty Remaining])

For a near-expiry report list (Excel 365/2019+):

=FILTER(BatchReceipts, (BatchReceipts[Alert]="NEAR EXPIRY")+(BatchReceipts[Alert]="EXPIRED"), "No near-expiry items")

How do I handle GST when expired stock is written off?

When a batch expires and is written off:

  1. Record the write-off in BatchIssues with Issue Type = "WRITE-OFF".
  2. The original ITC claimed on the purchase of that batch must be reversed, because the goods were not used in the course of business (Section 17(5)(h) of CGST Act — goods lost, stolen or destroyed).
  3. Calculate the ITC to reverse: Write-off Qty × Original Unit Cost × GST Rate%.
  4. Record this reversal in Table 4(B)(2) of your next GSTR-3B.
  5. Interest @ 24% p.a. applies under Section 50 of the CGST Act on wrongly availed/retained ITC, from the month of original claim to the month of reversal.

Free batch and expiry tracking template

The free Excel template includes:

  • BatchReceipts sheet with all formulas pre-built.
  • BatchIssues sheet linked back to update remaining quantities.
  • Dashboard with near-expiry summary counts.
  • Conditional formatting rules pre-applied (Expired/Near Expiry/Watch/OK).
  • Sample data for three SKUs with three batches each.

Free to download, no sign-up.

Download the Batch & Expiry Tracking Excel Template »

How Ankeshan helps: Ankeshan tracks batch numbers and expiry dates inside Excel and automatically flags near-expiry stock in the inventory dashboard, with a one-click ITC reversal calculator for expired write-offs. It's launching soon; join the waitlist.

Frequently asked questions

Is batch tracking mandatory for all pharma businesses in India? Yes, for drug-licensed establishments — manufacturers, distributors and retailers under the Drugs and Cosmetics Act. The licence condition requires batch-wise records. For OTC cosmetics and food businesses, FSSAI traceability requirements apply, though batch tracking at the retail level is not always explicitly mandated. Consult your drug/food inspector or CA for your specific category.

How many digits should a batch number be? The supplier or manufacturer assigns batch numbers — use exactly what is printed on the pack. Do not create your own batch number for a supplier's batch; use the manufacturer's batch number. Your internal Batch ID is a separate tracking key for your own register.

What if a supplier sends multiple batches in one shipment? Record each batch as a separate row in BatchReceipts, even if they arrived on the same GRN. Use the same GRN number in both rows but different Batch IDs and Batch Nos.

How do I handle stock returned by a customer — does the batch go back into the register? Yes. Record an issue with Type = "SALES RETURN" in BatchIssues (as a negative qty or as a separate "Qty Returned" column). Update the Qty Remaining formula in BatchReceipts. Check expiry dates carefully on returned stock before returning it to saleable inventory.

Can I use this template for food products (FSSAI-regulated)? Yes, the same structure works. Add columns for FSSAI Licence No of supplier and your own FSSAI registration number on the receipt sheet. For quick-service restaurants or cloud kitchens, adapt the template to raw material lots rather than finished goods batches.

Sources

  • Drugs and Cosmetics Act 1940 and Drugs and Cosmetics Rules 1945 — CDSCO (cdsco.gov.in).
  • FSSAI Food Safety and Standards Act 2006 — fssai.gov.in.
  • CGST Act 2017, Section 17(5)(h) — Blocked credits on goods lost/destroyed.
  • CBIC GST rate notifications effective 22 September 2025 (56th GST Council).

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.

Related: Inventory Management pillar » · FIFO & Weighted-Average Valuation » · Stock Register with GST in Excel » · Dead-Stock / Slow-Moving Report »