Pharmacy expiry tracker: a free Excel template, one row per batch.
A spreadsheet that does the expiry arithmetic for you. Enter each batch with its expiry date, quantity and cost, and it counts the days left, flags expired, critical and near-expiry stock, puts a value at cost on what is at risk, and numbers each item’s batches in the order to sell them. Free, no sign-up, for Excel and Google Sheets.
Download the free pharmacy expiry tracker
Excel workbook (.xlsx, 90 KB). Free, no sign-up. Works in Microsoft Excel and Google Sheets.
Download the template- Batches — One row per batch, with days to expiry, status, value at cost, sell order and what to do.
- Summary — Batches, units and value by status, the value at risk, and what was written off or returned.
- Returns log — Each return to a supplier, write-off or transfer, with its value at cost.
- Settings — Your critical and near-expiry windows: 30 and 60 days to start.
- How to use — The steps, the status rules and the accounting entries.
| Item | Status | Days to expiry | Sell order | Batch | Qty | Value at cost |
|---|---|---|---|---|---|---|
| Paracetamol 500 mg, 16 | Expired | -10 | Do not sell | P-2209 | 8 | 6.80 |
| Paracetamol 500 mg, 16 | Near expiry | 45 | 1 | P-2304 | 24 | 20.40 |
| Paracetamol 500 mg, 16 | OK | 400 | 2 | P-2411 | 60 | 48.00 |
| Amoxicillin 250 mg, 21 | Critical | 20 | 1 | A-1187 | 10 | 24.00 |
| Ibuprofen 200 mg, 24 | OK | 200 | 1 | I-5530 | 30 | 33.00 |
The example rows from the template’s Batches tab. Delete them and add your own.
How to use the expiry tracker.
The workbook keeps one row per batch, not per item, because each batch of a medicine has its own expiry date. You type five things: the item, the batch or lot number, the expiry date, the quantity on hand and the unit cost. The grey columns do the rest. Item code, location and supplier are there if you want them.
- Delete the example rows; their names end in “(example)”
- Enter one row per batch: item, batch number, expiry date, quantity and unit cost
- Sort or filter by status or expiry date to see what needs action this week
- Record each return or write-off on the Returns log and reduce the batch’s quantity
- Set the critical and near-expiry windows on the Settings tab to suit your suppliers
Expired, critical, near expiry: how the flags work.
Days to expiry is the expiry date minus today, so it counts down every time you open the file. Below zero the batch is expired. Inside the critical window, 30 days unless you change it, the batch is critical: return it now if your supplier allows, or make sure it sells first. Inside the near-expiry window, 60 days to start, it is near expiry: sell it first and check the return window. Everything else is OK.
Set the windows from your suppliers’ returns policies rather than a round number. If a wholesaler only takes stock back with a set time left before expiry, make the near-expiry window a few weeks wider than that, so the batch shows up while it can still go back.
First expiry, first out, worked out for each item.
The sell order column numbers each item’s unexpired batches by expiry date: the batch that expires first is 1, the next is 2, and so on. In the example rows, paracetamol has three batches. The one that expired ten days ago shows “Do not sell”, the one with 45 days left is 1, and the one with 400 days left is 2.
This is first expiry, first out (FEFO), and it is not the same as first in, first out. A supplier can ship a short-dated batch after a long-dated one, and selling in order of arrival would leave the short-dated stock to expire on the shelf.
Returns and write-offs in the books.
An expired batch is a loss at its cost. If the supplier takes it back, post a supplier return: the stock leaves inventory and a credit note reduces what you owe. If not, write it off: debit an expired-stock expense account and credit inventory at the batch’s cost, with the batch number on the entry. The Returns log totals both, so the month’s loss is a number you can hand to your accountant.
When the spreadsheet stops being enough.
A spreadsheet only knows what you type into it. Sales do not take stock out of it, so the quantities drift between counts; nothing stops an expired batch being sold at the counter; and every write-off has to be keyed into the books again. Those are the signs it is time for pharmacy inventory software.
In Nonari you switch on batch tracking for the products that need it, and every receipt records the batch number and expiry date. Sales take the earliest-expiring batch first, expired stock is blocked at the sale by default, and the near-expiry report lists every batch inside your window with a supplier return or a write-off one click away, each posted to the same double-entry ledger as the sale.
Expiry, kept current by every sale.
Batch and expiry on every receipt
Switch batch tracking on for the products that need it; each receipt records the batch number and expiry date.
First expiry, first out
Sales take the earliest-expiring batch first, or the first received, with a per-line batch choice on POS and invoices.
Expired stock blocked
By default an expired batch cannot be sold at the counter or on an invoice.
Near-expiry report
Every batch inside your window, 60 days by default, plus anything expired, by branch and category, with return and write-off.
Batches on the paperwork
The batch prints on receipts, invoices and delivery notes.
Branches and transfers
Each branch keeps its own stock, and transfers carry the batch on each line.
Frequently asked.
Is the pharmacy expiry tracker free?
Yes. Download the .xlsx file, with no sign-up or email, and use it for as long as it suits you.
Does the expiry tracker work in Google Sheets?
Yes. Upload the file to Google Drive and open it with Google Sheets. The formulas use standard functions, such as IF, COUNTIFS, SUMIFS and TODAY, that Sheets supports.
How do I track medicine expiry dates in Excel?
Keep one row per batch, not per item: item, batch number, expiry date, quantity and cost. Add a days-to-expiry column (the expiry date minus TODAY()), a status that flags anything inside your windows, and a value column (quantity × cost). Sort by expiry every week and act on the top of the list. The free template sets all of this up.
What formula calculates days to expiry in Excel?
=D2-TODAY(), with the expiry date in D2 and the result formatted as a number. A negative result means the batch has expired. The template wraps it in a check, so a blank or mistyped date shows “Check date” instead of an error.
How far ahead should a pharmacy check expiry dates?
Far enough to act: to return a batch inside your supplier’s returns window, move it to a branch that will sell it, or sell it first. Set the near-expiry window a few weeks wider than your supplier’s cut-off for returns and review the list every week or month. The template starts at 30 days for critical and 60 for near expiry, and you can change both.
When should a pharmacy move from a spreadsheet to software?
When the sheet is always out of date: sales do not take stock out of it, so the quantities drift between counts. A second counter or branch, or books that need the write-offs, are the other signs. Pharmacy inventory software records the batch on every receipt, sells first-expiry-first-out, blocks expired stock and posts write-offs to the ledger.
Let every sale keep the expiry list current.
15-day free trial, no card. Switch on batch tracking, receive one batch with its expiry date, sell from it and open the near-expiry report.