How to Track Batch Inventory & Expiry Dates in Excel

Keep the item, lot and movement separate

An item identifies what you hold. A lot identifies a particular batch of that item. A movement explains why that lot's quantity changed. If you combine all batches into one stock number, you lose the information needed to investigate a short balance or an approaching expiry date.

Choose one unit for each item, such as units, kilograms or liters. Convert a receipt into that unit before adding it. A box of twelve containers is not one container, and a material weight cannot be subtracted from a finished-product count.

• Item: stable item ID, description and stock unit.
• Lot: unique lot ID, source reference and your applicable expiry date.
• Movement: date, lot ID, quantity and a reason such as receipt, use, waste or adjustment.

Reconcile the on-hand balance

The free starter uses separate positive columns for opening, received and used quantities. Its on-hand formula is =D2+E2-F2. The complete inventory workbook instead records signed movements; the starter is not a direct import format for that ledger.

If a physical count finds 92 units, record and investigate the three-unit difference. Do not change the opening figure simply to make the balance match. Check missing usage, damaged stock, the wrong lot ID and unit conversion before recording a dated adjustment.

Review expiry at the lot level

The fictional starter has 95 units in lot A with an expiry date of 2026-11-30 and 70 units in lot B with an expiry date of 2026-12-15. For eligible stock of the same item, first-expired, first-out (FEFO) prioritizes the earlier expiry. This differs from first-in, first-out, which orders stock by receipt date.

An expiry date is one input, not a complete release decision. Review quarantine or hold status, condition, the product's own storage requirements and your lot records before allocating stock. Unknown dates need investigation; a blank cell does not mean unlimited shelf life.

Use the free CSV starter

1. Open the CSV in Excel, or import it as comma-separated UTF-8 text. Preserve the two example rows until you have checked the 95- and 70-unit balances.
2. Replace the item, lot, ISO-format date and quantity inputs with your own records. Enter received and used quantities as positive values in this starter.
3. If the formula column imports as text, re-enter the formula with its leading equals sign. When adding rows, copy the formula so the row references change.
4. Save an XLSX working copy if you want formatting and filters. CSV itself does not retain those spreadsheet features.
5. Compare the calculated lot balances with a physical count and document differences.

The dates and quantities are examples, not an automatic expiry alert. This small sheet does not include duplicate-ID checks, a movement history, an audit trail or a stock-value dashboard.

When to use the complete workbook

The Batch Inventory & Expiry Tracker adds 100 lot records, 500 signed movements, on-hand balances, expiry action tags, stock value and a checks dashboard. Its blank and worked-example XLSX files are designed for Microsoft Excel with automatic calculation enabled.

If you also need to cost what you make, read the handmade pricing guide. Purchasing and costing are related records, but they do not automatically update this manual stock ledger.