# Barcode batch expiry inventory Excel (free) + online

> Free inventory sheet with barcode, batch and expiry date: FEFO suggestions, balance per batch, near-expiry alerts with value at risk and a scan cell.

Source: https://softwiro.com/blog/batch-expiry-inventory-free-excel-online.html
Language: English
Softwiro, softwiro.com

---

Free Excel template

# Barcode, batch and expiry inventory in Excel (free) + online

Know which batch expires first, what it is worth, and what to sell first, from receipts and issues by batch.

13 November 2026 5 min read

## In short

- Scan or type the barcode into the cell, then log each receipt by batch with its expiry date and unit cost.
- Log issues by batch; the sheet suggests the batch expiring first (FEFO) and warns when you sold another while an older one remained.
- Balance per batch and per item, days to expiry, and statuses: expired, near expiry, fine, finished.
- Alerts list the soonest first with the value at risk; a scan cell shows an item's balance and earliest batch.
- Built for any shop, food store or cosmetics warehouse; the pharmacy sheet is a separate template.

You run a shop, grocery or store with dairy, food and cosmetics, and you want to know without walking the shelves which batch expires soon, what it is worth, and which batch should be sold first. The free [barcode, batch and expiry inventory sheet](https://softwiro.com/templates/batch-expiry-inventory) does this from two logs you type: what you received and what you issued. You can also try it online from the template page.

## The problem it solves

One item arrives in several deliveries with different expiry dates. If you only record an item's balance you cannot tell which delivery is the one expiring, so you sell the newest and leave the oldest to expire on the shelf. The loss gets blamed on slow sales when it is really the wrong picking order. The fix is to keep a balance per batch and let the sheet suggest the batch that expires first.

## How it works, step by step

1. In الإعدادات (Settings) write today's date, the number of days for "near expiry" and your categories.
2. In الأصناف (Items) record barcode, name, category, unit and minimum level. Click the barcode cell and scan with a USB scanner, which types the number like a keyboard and then Enter; some phone apps do the same. The cells are text so leading zeros stay.
3. When goods arrive, write one row per batch in الاستلام (Receipts): barcode, batch number, expiry date, quantity and unit cost.
4. When you sell or write off, write in الصرف (Issues) the barcode, batch, quantity and type. The item name, the suggested batch and a row check appear.
5. Scan any barcode in مسح باركود (Scan) to see the balance and the earliest batch.
6. Open التنبيهات (Alerts) and الملخص (Summary) to see what is expired or near, and its value.

Batch balance = received - issued for the same barcode and batch number. A batch is "expired" once its date has passed, "near expiry" when the set number of days or fewer remain, and "finished" when its balance runs out. Value at risk = expired and near-expiry balance x unit cost.

## How it differs from the pharmacy expiry sheet

We also have the [pharmacy stock and expiry tracker](https://softwiro.com/templates/pharmacy-expiry-tracker), made for pharmacies: each batch is one row with its current quantity and reorder level. This sheet is for any shop, grocery, food store or cosmetics warehouse: it uses barcodes, separates receipts from issues so batch balances come from movements, suggests the oldest-first issue (FEFO) and gives you a scan cell. If your shop does not care about expiry, the [general shop inventory sheet](https://softwiro.com/templates/general-inventory) is simpler.

## What each sheet does

- Settings: date, alert days, first chart month and categories.
- Items: barcode, total balance, earliest expiry, earliest batch and status; 200 items.
- Receipts: batches with balances and statuses; 400 batches.
- Issues: sales and write-offs with the suggestion and warning; 1,000 rows.
- Scan: a quick look-up for any item.
- Alerts: expired and near batches, soonest first.
- Summary: values, percentages and three charts.

## A walk-through with the sample numbers

These are sample data in the sheet, not a real shop: 11 items, 19 batches and 55 issues; today is 13 November 2026 and "near expiry" means 60 days or fewer.

- Milk: 464 received and 170 issued leaves 294, worth 8,716. It has three batches: L2608A holds 20 cartons that expired 8 days ago, worth 570; L2610B holds 74 cartons expiring in 22 days, worth 2,146; L2611C holds 200 cartons expiring in 78 days, so it is fine. Scan the milk barcode and the sheet shows that the earliest batch is expired.
- FEFO: 70 cartons were sold from L2610B while 20 older cartons of L2608A stayed in stock, so two rows read "خالف FEFO" (broke FEFO). Two biscuit rows do too: 4 flagged issues in total.
- Yogurt: batch Z10 is finished, so Z11 (37 days left) becomes the suggestion.
- Green tea: balance 16 against a minimum of 20, so "reorder". Liquid soap is out, and wet wipes have no movement.

In the summary: stock value 24,772.25, of which 660 is expired and 4,197.50 is near expiry, so 4,857.50 is at risk, about 19.6%. Two batches are expired and five near, and 15 of the 19 batches still hold stock. The largest value at risk is in dairy: 3,539.50 of 9,539.50.

## Common mistakes

- Writing a batch number two ways (L2610B and l2610b), so its balance is split. Copy it from Receipts instead of retyping.
- Repeating the same barcode and batch in two receipt rows. Add the quantity to the first row.
- Forgetting to log an issue, so the balance looks higher and the batch looks fresher than it is.
- Not updating today's date, so an expired batch looks fine.
- Relying on "near expiry" without a decision: cut the price or move the batch forward.
- Scanning while the wrong cell is selected, so the number lands elsewhere.

## When to move to a system

The sheet is enough for one store and a few thousand movements. When daily sales grow or you have several branches, look at [Accountwiro](https://softwiro.com/systems/budoor.html), which creates invoices by reading barcodes, updates quantities after each sale and warns when an item falls below the limit, or [ask for software built your way](https://softwiro.com/contact).

## Your next step

[Open the template](https://softwiro.com/templates/batch-expiry-inventory), download the file, clear the examples, record your items and the latest delivery of each with its batch, then open "التنبيهات" to see what should leave the shelf this week.

## Free cheat sheet for this field

Terms, abbreviations, tables and diagrams over several pages, in Arabic and English. Download it as a PDF or images, including a phone-size version.

[![Cheat sheet: stock by batch and expiry (1/3)](https://softwiro.com/uploads/cheatsheets/batch-expiry-inventory/en-1.jpg)](https://softwiro.com/uploads/cheatsheets/batch-expiry-inventory/en-1.jpg)[![Cheat sheet: stock by batch and expiry (2/3)](https://softwiro.com/uploads/cheatsheets/batch-expiry-inventory/en-2.jpg)](https://softwiro.com/uploads/cheatsheets/batch-expiry-inventory/en-2.jpg)[![Cheat sheet: stock by batch and expiry (3/3)](https://softwiro.com/uploads/cheatsheets/batch-expiry-inventory/en-3.jpg)](https://softwiro.com/uploads/cheatsheets/batch-expiry-inventory/en-3.jpg)

0 downloads · 3 pages

## 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 |
| --- | --- |
| One file that one person edits at a time, and copies travel by WhatsApp. | A program with a login for each user and permissions you set; everyone sees the same data live. |
| You type and check every number by hand, and one wrong cell breaks the result. | Screens built around your steps: each person records only their part, and the calculations run on their own. |
| Limited size; the file slows down as the data grows. | Holds years of data without slowing down, with instant search and filters. |
| Fixed columns and reports, as the sheet was designed. | Reports, alerts, printing and WhatsApp built around how you work. |
| Backups and security are your own responsibility. | A secured server with automatic backups and a log of who changed what and when. |

### Extra features in the app

- A login for each user with permissions, and a log of who changed what and when
- Automatic reminders and alerts by WhatsApp and email
- Photos, files and documents kept with each record
- Instant search and filters, with reports exported to PDF and Excel
- Works from a phone anywhere, with automatic backups

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

## Questions this raises

### How do I scan a barcode into an Excel cell?

Click the barcode cell and scan with a USB scanner, which types the number and then Enter. Some phone apps do the same. The cells are text, so leading zeros stay.

### What does FEFO mean?

First expired, first out. For each barcode the sheet suggests the batch with the earliest expiry among those with stock, and warns if you sold another while an older one remains.

### How is it different from the pharmacy expiry sheet?

The pharmacy sheet holds one row per batch with its current quantity and reorder level. This one is for shops and stores generally: barcodes, receipts separate from issues, oldest-first suggestions and a scan cell.

### Can I change the near-expiry days?

Yes, in the Settings sheet. A batch is near expiry when that many days or fewer remain, today included.

### How much does it hold?

200 items, 400 batches and 1,000 issue rows; the alerts list shows the first 60 batches.

## Have a system to build?

Softwiro designs, builds, and operates production systems. Describe what you need and we will come back with a scope rather than a slogan.

Start a project

[All writing](https://softwiro.com/blog.html)
