Lot Tracking Spreadsheet: Free Template With a Trace Tab

A lot tracking spreadsheet needs three lists and the links between them: what came in, with the supplier's lot number; what went into each batch, lot by lot; and where each batch went. Keep those three up to date and you can answer the question lot tracking exists for ("who has product made from this lot?") in a couple of minutes. The template below is filled in with a real example, and it has a trace cell: type a lot code and every row that lot touches is marked.

Download the lot tracking spreadsheet. It's an Excel file that opens in Google Sheets and LibreOffice, with no macros and no sign-up.

The short version

• Receiving: one row per delivery, with the lot printed on the package, or your own code if there isn't one.
• Batch inputs: one row for every lot that went into every batch. Tedious. It's the row that saves you.
• Shipments: one row per batch per customer. The lot goes on the invoice too.
• Trace: type a lot code, and the sheet marks the batches, the shipments, and what's still on your shelf.
• Type lot codes as text, and copy them rather than retyping. A code with a typo is a lot that doesn't exist.
• When the batch inputs tab starts falling behind, TaroStack writes it for you. More at the end.

What's in the template

Five tabs. One of them is a read-me, so really four.

Receiving is the door. Date, item, supplier, the lot on the package, your code, quantity. When something arrives with no lot at all (the gallon jugs of vinegar from the restaurant supply store, say), give it your own code from the store and the date, like RD-260918, and write it on each jug with a marker. Packaging often has no lot either, and the purchase order number does the job fine.

Batch inputs is the backward link. For the hot sauce batch 260921B it's five rows: habanero mash HM-0911, mango purée 48377, vinegar RD-260918, bottles PO-1184, caps PO-1190. Yes, five rows for one batch. Two batches a day for a month is three hundred rows, and it's still the most useful thing you'll ever type.

Batches is one row per batch: what you made, how many, the best-by date. Two columns fill themselves in from the shipments tab, bottles shipped and bottles still on hand, so the sheet always knows what hasn't left yet.

Shipments is the forward link. Date, batch, customer, cases, invoice number. It's the one that goes missing first in real life, because it happens at the packing table, often in somebody else's hands.

Here's that tab from the example, a small hot sauce maker's week in September:

Batch 260921A made 288 bottles, and 120 have gone out, so 168 are on the shelf. Batch 260921B made 264 and all 264 have left. The sheet works both of those out; nobody types them.

A trace, start to finish

Three weeks later the purée supplier emails. Lot 48377 is being recalled.

You open the Trace tab and type 48377 in the yellow cell. The summary under it reads: one batch, 264 bottles made, 22 cases shipped, three shipments to look at, nothing left on your shelf. On the other tabs, the purée row in batch 260921B's inputs is shaded, so is the batch, and so are its three shipments: ten cases to the co-op, eight to the independent grocery, four to your own stall.

So it's two phone calls and a note at the stall next Saturday. The grocery call has a detail worth noticing: they also bought six cases of 260921A, which used the other purée lot. Because the lot code is on every bottle and every invoice, they pull 260921B and leave 260921A where it is. Without codes, they'd pull all fourteen cases, and you'd be the vendor who cost them a shelf.

(The same drill, timed and written up the way a buyer wants to see it, is in the mock recall example.)

Tracing works backward too. A customer complains about a bottle from 260921A. Type the batch code, and the batch inputs tab shades the five lots that went into it, which tells you which supplier to phone about what.

Five rules that keep it trustworthy

Write the batch inputs at the time, from the containers in front of you. The end-of-day version is where the second bag of sugar goes missing.

Copy lot codes, don't retype them. Paste from the receiving tab. 48377 and 48337 look identical at 5 a.m.

Keep the lot columns as text. Spreadsheets love to turn 00417 into 417 and a long code into something with an E in it. The template's lot columns are already formatted as text, and any column you add should be too.

Put the lot on the invoice as well as on the sheet. The spreadsheet is your copy of the forward link; the invoice is the customer's, and it's the one they'll read back to you on the phone.

Never delete a row because the batch is finished. Finished batches are exactly the ones you'll trace. Keep the file by year and start a new one in January.

Excel or Google Sheets?

Either. The marks are ordinary IF and COUNTIFS formulas with conditional formatting, with nothing that only one program understands. Upload the file to Google Drive and open it with Sheets, or open it in Excel, and the trace cell works the same. If two people will be typing at once, Sheets handles that better than a file emailed around.

The one formula worth knowing, from the shipments tab: a row is marked when its batch is the traced code, or when the batch inputs tab has a row for that batch using the traced lot.

=IF(OR(B2=Trace!$B$2, COUNTIFS('Batch inputs'!$A:$A, B2, 'Batch inputs'!$E:$E, Trace!$B$2)>0), "◀", "")

(The template adds &"" to both sides so a number and a text code still match. That's the only trick in it.)

Where this stops working

The formulas never break. The typing does. The trace is only as good as the batch inputs tab, and that tab is filled in by whoever is mixing, with sticky hands and a timer going. The day it's skipped, that batch can't be traced backward, and you won't find out until you need it.

The shipments tab has the same problem at the other end, plus a quieter one: market sales. The sheet can say four cases went to the stall, but not which customers bought which bottle, and nothing ever will. That's fine for most rules, which stop at the business you sold to, but it's worth knowing.

Then there's speed. A trace that takes two minutes at your desk takes twenty when the file lives on the office computer and you're at the kitchen. And a second person editing a downloaded copy means two versions of the truth. Inventory software vs a spreadsheet is about when that trade tips.

How TaroStack does it

The row the short version calls tedious, one line for every lot that went into every batch, is the row TaroStack writes for you. Receiving a delivery records the supplier's lot, or gives the delivery your own code. Recording a batch takes ingredients from the lots that expire first and notes which lots it used, so the batch inputs tab exists without anybody typing it, and the codes come from what was received rather than being retyped. When an order ships, the lot goes with it.

So the trace in this article is one report: the ingredient lots behind a batch, the customers in front of it, what's still on your own shelves, and who to call. Timed, logged drills give you the date and the minutes for a buyer's form, and lot labels with GS1-128 barcodes cover the cases. The records stop depending on the most careful person in the kitchen being the one who's there.

Lots, expiry and recalls are on every plan, from $49 a month, and recording batches against recipes is on Standard at $99. If you've started this spreadsheet, its lists import from CSV. The first 30 days are free. If you'd like a hand setting up, ask, and we'll do it with you.

Questions people also ask

How do I track lot numbers in Excel?

Keep three lists: deliveries with their lot numbers, the lots used in each batch, and each batch's shipments. Link them with the batch code, and use COUNTIFS to mark every row a lot touches. The template above has all three, plus the trace cell, already set up.

Is there a free lot tracking spreadsheet template?

Yes, the one on this page: no email, no sign-up, and it opens in Excel, Google Sheets and LibreOffice. It comes filled in with an example you can overwrite. There's also a filled-in batch record template if you want one page per batch as well.

What should an ingredient lot tracking form include?

For each ingredient used in a batch: the batch code, the ingredient, the lot you used (the supplier's, or your own code), and how much. Add the date and who recorded it. FDA's records rule for most food manufacturers and distributors (21 CFR 1.337) asks for the lot or code number "to the extent this information exists", and your own code is how you make it exist when the supplier gives you nothing. More on codes in how to create lot numbers.

Can I use Google Sheets for lot tracking?

Yes, and for a small team it's the better choice, because everyone edits one copy. Keep the lot columns as text, and protect the header rows so nobody sorts one tab out from under the formulas.