# Expiry Date Tracker Excel Template (Free)

> A free Excel expiry date tracker: days left, Expired/Critical/Warning status, stock value at risk, a monthly expiry calendar and the batches to act on first.

Source: https://softwiro.com/templates/expiry-date-tracker
Language: English
Softwiro, softwiro.com

---

Free Excel template

# Expiry Date Tracker for Excel (Inventory)

A free Excel expiry date tracker for a shop, pharmacy, cafe, kitchen or stockroom. You list each batch once with its expiry date, quantity and unit cost. The sheet works out the days left, labels every batch Expired, Critical, Warning or OK, adds up how much money sits in each group, shows what expires month by month, and lists the ten batches to deal with first. It opens in Excel, LibreOffice and Google Sheets and works in any currency.

[Download the free sheet (with a worked example)](https://softwiro.com/downloads/templates/softwiro-expiry-date-tracker.xlsx) [Blank copy, no example](https://softwiro.com/downloads/templates/softwiro-expiry-date-tracker-blank.xlsx)

Excel file (.xlsx) · 66 KB · 7 tabs · works in Excel and Google Sheets · no sign-up

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

## 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](https://softwiro.com/tools/reorder-point-calculator).

## How to use it

1. 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).
2. 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.
3. On the Stock sheet add one row per batch: item, SKU, category, location, batch or lot, expiry date, quantity on hand and unit cost.
4. 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.
5. Open Expiry Calendar to see value by category and month for the next 12 months.
6. 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.

[Download the free sheet (with a worked example)](https://softwiro.com/downloads/templates/softwiro-expiry-date-tracker.xlsx) [Blank copy, no example](https://softwiro.com/downloads/templates/softwiro-expiry-date-tracker-blank.xlsx)

Excel file (.xlsx) · 66 KB · 7 tabs · works in Excel and Google Sheets · no sign-up

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

## 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](https://softwiro.com/product/sehaty) · [Ask for a demo](https://softwiro.com/contact)

## When your work grows: a custom system instead of the sheet

A sheet is a great start. When more than one person works on your data, when it grows, or when you need alerts and reports of your own, we build you a custom system at competitive prices. This is what changes:

| In the sheet | In a custom system |
| --- | --- |
| You type each batch and its date | Batches arrive from the delivery note or barcode scan |
| You open the file to see what is close | Alerts are sent to the right person before the date |
| One list for the whole business | Separate stock per branch with a combined view |
| Action chosen from a dropdown | Action followed through: return request, price change, write-off record |
| Charts in the workbook | A live dashboard on your phone for the owner |

### Extra features in the app

- Scan a barcode on delivery and the batch and expiry date are recorded without typing
- Alerts sent to your phone before a batch reaches its date
- First-expiry-first-out picking, so the oldest batch is sold or dispensed first
- Stock counted per branch and per shelf, with one combined view
- Write-off records kept with who decided and why
- Supplier return requests created from the expiring list

[Ask for your custom system](https://softwiro.com/contact)

## 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](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](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)

[All templates](https://softwiro.com/templates) · [Free tools](https://softwiro.com/tools)
