How to Build an Inventory List With Expiration Dates

An inventory list that tracks expiration dates has one row per lot, meaning one row for each delivery of each dated item, with its use-by date, the quantity left, and a formula that counts the days remaining. Sort it by days left and the top of the sheet is always what needs using first. The layout and formulas below work in Excel and Google Sheets, and take about twenty minutes to set up.

The short version

• One row per lot, never one row per item. "Cream cheese: 9 lb" can't have an expiration date. Three deliveries can.
• Columns: item, lot, location, received, use-by, quantity, unit, unit cost, days left, status.
• Days left is =E2-TODAY(). Status is an IF that says Expired, Use now, Use first or OK.
• Sort by days left. Add color so red means act.
• Ten minutes a week: update quantities, add new deliveries, clear out used-up rows, act on the red ones.

The layout

Here's the sheet on September 21, 2026:

You can download this sheet with the formulas and colors already in place (Excel format, opens in Google Sheets, no sign-up), or build it yourself from what follows.

The lot column is whatever identifies that delivery. Use the supplier's lot number if it's printed on the case. If it isn't, make your own from the item and the date received (BM-0916), and write it on the case with a marker so the shelf and the sheet agree.

Location earns its column the first time you're hunting for nine pounds of cream cheese and it turns out to be in the other fridge.

The formulas

Days left, in I2, copied down:

=E2-TODAY()

Format the column as a plain number. If it shows as a date, that's why it looks like nonsense. (The formulas are taken apart one at a time in Excel formulas to track expiration dates.)

Status, in J2:

=IF(I2<0,"Expired",IF(I2<=3,"Use now",IF(I2<=14,"Use first","OK")))

Change the 3 and the 14 to suit what you make. A creamery might want 2 and 5. A chocolate maker might want 30 and 90.

Color. Select column J, then Format > Conditional formatting. Three rules: text is exactly "Expired" gets a dark red fill, "Use now" gets red, "Use first" gets amber. Now you can read the sheet from across the room.

Money at risk this week, in a cell at the top:

=SUMPRODUCT((I2:I200<=7)*(I2:I200>=0)*F2:F200*H2:H200)

That's quantity times unit cost for every lot with seven days or less to go. On the sheet above it's the buttermilk and the cream cheese: 6 × $1.80 + 9 × $3.40 = $41.40. One number, at the top, that tells you whether this week needs attention.

Sorting. Click any cell in the days-left column and sort A to Z, or set a filter view sorted by that column so it stays sorted. The top rows are your to-do list.

Does the days-left number tell you enough?

Not quite, and this is the part most templates leave out. Nine pounds of cream cheese with four days left is fine if you go through three pounds a day, and a loss if you use one. What you really need to know is whether each lot will be used up before its date, given that older lots of the same thing get used first.

There's a formula for that, and I've worked it through with a full example in how to keep track of expiring ingredients in a bakery. If dated ingredients are a big part of your cost, add that column. If you've only got a handful of dated items, days left plus common sense will do.

Either way, the shelf has to match the sheet. Use stock in date order, soonest first, which is called first-expired, first-out, and put new deliveries behind old ones even when it's a nuisance.

The ten-minute weekly routine

Pick a day, ideally the day before your main order goes in.

1. Add a row for each delivery of dated stock since last week.
2. Update the quantity on every row. This is the step that decides whether the sheet lives or dies. If you're doing a weekly count anyway, do both at once.
3. Delete rows that have hit zero, or move them to an archive tab if you want the history.
4. Look at everything marked Use now or Use first, and decide: use it, move it, freeze it, discount it.
5. Anything Expired comes off the shelf today. Record it as a loss, with the dollar value, before it goes in the bin.
6. Glance at the money-at-risk number. If it's high every week, you're ordering too much or too seldom.

That last point is the payoff. After two months of this you'll know which three items you keep throwing away, and you can fix the ordering, which is cheaper than any amount of clever tracking.

Where this stops working

Step 2. Everything depends on someone updating quantities by hand, every week, for every lot, and that's exactly the kind of task that gets skipped the week you're busiest, which is also the week you're most likely to lose stock. After one missed week the quantities are wrong. After two, nobody trusts the sheet and you're back to opening containers and sniffing.

The sheet also can't see production. When a batch uses four pounds of cream cheese, nothing subtracts it from lot 44817. You do, later, from memory.

How TaroStack does it

In TaroStack this list keeps itself. Each delivery is received as a lot with its use-by date, and from then on the quantity drops as stock is used, because recording a batch takes ingredients from the lot that expires soonest and notes which lot it was. Nobody retypes anything. Expired stock, and anything you've put on hold, stops counting as available, so it can't be promised to a customer or planned into a batch.

You get a notice before things expire, by email or in the app, as it happens or as one daily digest. And the expiring-stock forecast answers the harder question from earlier on its own: for every lot, how much will really be left on its date at the rate you're using it, and what that's worth. If you sell to customers who need a minimum number of days left on what they receive, you can record that per customer, and planning takes it into account.

If you've already built the sheet above, it imports. Lots and expiry tracking are part of every plan, starting at $49 a month. Recording batches against recipes comes with Standard.

Questions people also ask

How do I track expiration dates in Excel?

Put the expiration date in one column and =E2-TODAY() in the next to count the days left. Add conditional formatting so low numbers turn red, and sort by that column. Track each delivery on its own row, since a single row per item can't hold more than one date.

Is there an app to track expiration dates?

Plenty for home kitchens, where a reminder is all you need. A business needs more than a reminder: quantities by lot, stock that comes off automatically as it's used, and expired stock kept out of what you can sell. That's inventory software with lot tracking, which is what TaroStack is.

What does expired stock mean?

Stock that has passed its use-by or best-before date. In your records it should stop counting as available the moment it expires, even before someone physically removes it, and then be written off as a loss so your stock value and your costs stay accurate.

How far ahead should I flag items that are about to expire?

Far enough to do something about it. For dairy that might be three days, which is enough time to plan a special. For packaged goods with months of shelf life, thirty to ninety days gives you time to discount or move them. Match the warning to how long it takes you to act.