Try the sheet here, on the example
The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.
Example from the sheet
| As of | 46342 | Type the date on the Settings sheet each time you open the file. Alert windows are set there too. | |
|---|---|---|---|
| Batches tracked | 26 | ||
| Units on hand | 666 | ||
| Stock value | 2,023.60 | ||
| Value at risk (expired + critical) | 488.20 | ||
| Share of stock value at risk | 24.1% | ||
| Status | Batches | Units | Value |
| Expired | 4 | 45 | 128.10 |
| Critical | 6 | 115 | 360.10 |
| Warning | 7 | 208 | 598.40 |
| OK | 9 | 298 | 937.00 |
| Total | 26 | 666 | 2,023.60 |
Example from the “Dashboard” tab, with the numbers and formulas that are in the file.
What is inside
- Dashboard: Stock value, units and batches by status, the value at risk (expired plus critical) and its share of the stock, stock value by expiry month, value at risk by category, and the ten batches with the fewest days left, with three charts.
- Stock: The working list: item, SKU, category, location, batch or lot, expiry date, quantity, unit cost and the action you chose. It adds days left, status, value and a sort key. Room for 500 batches.
- Expiry Calendar: Stock value by category across the next twelve months, with a column for anything already past and one for later, and a total row for all stock.
- Lists: Your categories, shelf locations and the actions you take on stock close to its date. They feed the dropdowns on the Stock sheet.
- Settings: Business name, currency symbol, the as-of date and the two alert windows in days.
The file opens on a start tab (“Start here”) that explains how to use it.
What makes it different
Most free expiry trackers stop at a date column and a colour. This one also puts a money figure on the problem, shows what is coming month by month, and picks out the ten batches to act on first. In the sample, 4 batches are past their date (128.10), 6 are critical (360.10), and 488.20 of the 2,023.60 in stock is at risk.
How do I track expiry dates in Excel?
Make one row per batch, not per product: the same item bought twice usually has two dates. Store the expiry as a real date, then subtract the date you are checking against to get the days left, and sort or filter on that column.
This sheet does that for you. Type the as-of date on Settings, list the batches on Stock, and the status column sorts them into Expired, Critical (within 30 days by default), Warning (within 90 days) and OK. Change the two windows to suit your products: a dairy shelf needs a shorter window than tinned food.
Why does the sheet not use TODAY()?
TODAY() changes every time the file opens, so a report you saved last week silently shows different numbers, and a copy opened in another program can disagree. Here you type the as-of date once, so the sheet shows exactly what it showed when you saved it. On the day you check stock, type that day's date.
What should I do with stock that is close to its date?
The Action column gives you a short list to pick from: discount it, move it to the front, return it to the supplier, donate it or discard it. Which is allowed depends on the product and your local rules, so check them, especially for medicines and food. To plan how much to reorder so less goes out of date, see the reorder point calculator.
How to use it
- On the Settings sheet type your business name, currency symbol, the "as of" date (type today's date when you open the file) and the two alert windows: Critical (days) and Warning (days).
- On the Lists sheet keep your categories, shelf locations and the actions you take on stock close to its date. The samples can be renamed.
- On the Stock sheet add one row per batch: item, SKU, category, location, batch or lot, expiry date, quantity on hand and unit cost.
- Days left, Status and Value appear by themselves. Expired is red, Critical orange, Warning yellow. Pick an Action from the dropdown once you decide what to do.
- Open Expiry Calendar to see value by category and month for the next 12 months.
- Open Dashboard for the totals, the charts and the ten batches to act on first. When a batch is sold, returned or discarded, change its quantity to 0 or delete the row.
Before you start:
- Capacity: 500 batches, 30 categories, 20 locations and 10 actions.
- The sheet does not use TODAY(), so numbers never change by themselves. Type the as-of date on Settings every time you open the file.
- Critical means the batch expires within the Critical window (default 30 days) and is not expired yet. Warning means within the Warning window (default 90 days).
- A batch with quantity 0 still counts as a batch but adds no units or value. Delete the row once it is gone.
- Expiry Calendar lists the first 30 categories on the Lists sheet. A batch whose category is not on the list is counted on the Dashboard but not in the calendar rows.
- Use one currency in one workbook. The currency symbol on Settings is only a label.
Still running this on Excel?
This sheet depends on someone checking the shelves and typing in the items by hand. Sehaty tracks the pharmacy's stock item by item, warns you before an item runs out rather than after, and records dispensing against the e-prescription itself.
| In this sheet | In MyClinic (Sehaty) — Clinic, Pharmacy and Supplier Platform |
|---|---|
| You discover an item is short when a patient asks for it. | Sehaty tracks stock item by item and warns you before an item runs out, not after. |
| The prescription is a paper that can get lost or be hard to read, and no trace of dispensing remains. | The consultation produces an e-prescription in the patient's medical file, and the pharmacy records dispensing against that same prescription. |
| You learn what pharmacies need late, through reports and phone calls. | The supplier sees a digital catalog that reaches pharmacies and warehouses, and sees demand as it comes in. |
| The patient phones several clinics to book. | The patient searches for appointments by specialty and location and books without a phone call. |
See MyClinic (Sehaty) — Clinic, Pharmacy and Supplier Platform · Ask for a demo
Frequently asked questions
Is this expiry date tracker really free?
Yes. Download it, use it for your business and change anything you like. There is nothing to sign up for to open the file.
Does it work in Google Sheets and LibreOffice?
Yes. It uses only ordinary formulas such as SUMIFS, COUNTIFS, INDEX and MATCH, so it works in Excel 2016 and later, LibreOffice and Google Sheets.
How many batches can it hold?
500 batches, 30 categories, 20 locations and 10 actions. When it is full, remove the rows for stock that has been sold or discarded.
Can I change the 30 and 90 day alert windows?
Yes. They are two cells on the Settings sheet. Every status, total and chart follows them.
What do I do when a batch is sold or thrown away?
Set its quantity to 0 or delete the row. A batch with quantity 0 adds no units or value.
Is the template free?
Yes, with no sign-up and no email address. Use it, edit it and share it as you like.
Does it work in Google Sheets and on mobile?
The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.
What is the difference between the white and the gray cells?
You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.
Can I use the sheet from an AI assistant?
Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html
More free templates for: Pharmacies and clinics
More free templates
- Dental Practice Treatment Plan Tracker for Excel
- HOA Dues Tracking Spreadsheet for Excel
- HOA Budget Template for Excel
- Car Rental Booking Tracker for Excel
- Employee Leave Tracker Template for Excel
- Monthly Habit Tracker Template for Excel (With Streaks)
- General Journal and Trial Balance Template for Excel
- Delivery Tracking Spreadsheet for Excel (Dispatch Tracker)
- Real Estate Lead Tracking Spreadsheet (Excel)