Softwiro
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.

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

Example from the “Dashboard” tab, with the numbers and formulas that are in the file.
As of46342Type the date on the Settings sheet each time you open the file. Alert windows are set there too.
Batches tracked26
Units on hand666
Stock value2,023.60
Value at risk (expired + critical)488.20
Share of stock value at risk24.1%
StatusBatchesUnitsValue
Expired445128.10
Critical6115360.10
Warning7208598.40
OK9298937.00
Total266662,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

  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.

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 sheetIn 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

All templates · Free tools