A supplier raises a concern about a raw-material lot. Your team needs to answer a practical question: which production jobs used it, which output lots resulted, and where did those goods go?
That is the real test of a batch traceability spreadsheet. Recording a lot number is not enough. The record needs to connect receipt, consumption, transformation, quality decisions and dispatch so that someone can follow the history without relying on memory, disconnected workbooks or email searches.
For many UK manufacturers, Excel is a sensible starting point for defining the process. It can become fragile when it is also the live record used by several people to receive materials, record production, approve batches and investigate exceptions.
What a batch traceability spreadsheet needs to answer
A useful traceability record should support both directions of investigation.
Forward trace
Starting with a supplier or internal input lot, identify:
- The receipt record and supplier reference
- Every production job or batch that consumed the lot
- Any intermediate or finished output lots created
- Quality decisions, rework or deviations connected to those lots
- Dispatches and recipients associated with the affected output
Backward trace
Starting with a finished lot, identify:
- The production job that created it
- The component lots and quantities used
- The suppliers and receipt records for those inputs
- Relevant inspection, release or exception records
- The people, locations and dates associated with each event
A batch number on a production report does not provide this on its own. The important part is the relationship between records.
Keep traceability events separate from reference data
A dependable spreadsheet structure separates relatively stable reference information from events that happen during production.
Reference data
Reference data may include:
- Product codes and descriptions
- Suppliers and customers
- Manufacturing sites, lines or locations
- Units of measure
- Approved statuses and reason codes
- Employees or responsible roles
Event data
Event data records what actually happened, such as:
- Material receipt
- Material issue or consumption
- Production or transformation
- Quality hold, release or rejection
- Rework and disposal
- Shipment or transfer
This distinction helps prevent repeated typing and inconsistent labels. A supplier record can hold the standard supplier identity, while a receipt event records the supplier's lot reference, quantity, date and related internal lot code.
A practical batch traceability spreadsheet layout
Avoid putting every process step into one long batch register. A single row per batch may look simple, but it becomes difficult to maintain when lots are split, combined, reworked or corrected.
Instead, use separate tables or worksheets for controlled reference data, event records and reporting.
| Table or area | Purpose |
|---|---|
| Product register | Product codes, descriptions and units |
| Supplier and customer register | Controlled business identities and contact references |
| Lot register | Internal lot identifiers and current status |
| Receipt events | Incoming lots, supplier lot codes, quantities and receipt dates |
| Production events | Input lots, output lots, jobs, quantities and locations |
| Quality events | Holds, checks, releases, deviations and approvals |
| Dispatch events | Output lots, shipment references and recipients |
| Recall report | Forward and backward trace results for investigation |
Fields for each traceability event
Each event should have a stable identifier and enough context to explain the relationship later.
| Field | Why it matters |
|---|---|
| Event ID | Identifies the individual transaction or record |
| Event type | Distinguishes receipt, consumption, production, release or dispatch |
| Parent input lot | Shows the material or intermediate being used |
| Child output lot | Shows the resulting intermediate or finished lot |
| Production job reference | Connects the event to the relevant work |
| Quantity and unit | Explains how much material moved through the event |
| Date and time | Establishes when the event occurred |
| Site, line or location | Shows where it occurred |
| Responsible person or role | Makes ownership visible |
| Quality or document reference | Connects supporting evidence to the event |
A production event can contain one input lot and one output lot relationship. Where several lots are consumed in one job, create a record for each input relationship rather than listing multiple lot numbers in one cell. Where one lot is split across jobs, record each consumption separately.
That structure makes it possible to filter from a suspect input lot to affected jobs and output lots, then continue through the dispatch records.
Use controlled lot identifiers
Supplier lot codes and internal lot codes often need to coexist. A supplier's reference may be useful for incoming documentation, while an internal code gives your team a consistent way to identify material through production.
Keep both values where relevant, with a clear relationship between them. Do not silently replace one with the other when the supplier reference changes format or contains an error.
Useful controls include:
- Validation lists for products, suppliers, sites and units
- Required fields for lot code, quantity, date and responsible role
- A unique ID for each transaction event
- Clear status values such as pending, held, released or rejected
- Document references for certificates, inspection records or deviation records
- Checks for missing parent lots, duplicate identifiers or unlinked output lots
The aim is not to make a spreadsheet complicated. It is to make the correct record easier to enter and easier to investigate.
Treat corrections as events, not silent edits
Traceability depends on being able to explain why a record changed. If an original entry was wrong, replacing it in place can leave the team unable to show what happened or who made the correction.
A controlled correction process should record:
- The original event or value being corrected
- The revised information
- The reason for the change
- The person responsible for the correction
- The relevant approval or supporting reference, where needed
Excel can support a limited, disciplined process, but it is less reliable when many people can overwrite shared rows, formulas or historical data. If changes are being managed through file copies, email instructions or informal comments, the recall record is already harder to defend.
Run a recall drill against the real workbook
The most useful test is not whether the spreadsheet has all the expected columns. It is whether another responsible person can answer a traceability question using the records available.
Choose a real input lot or finished lot and test both directions.
- Start with an input lot. Identify all jobs that used it, the output lots produced and the related dispatches.
- Start with a finished lot. Identify its input lots, suppliers, production events and quality records.
- Check exceptions. Include rework, partial consumption, substitutions or quality holds where they exist.
- Review the evidence. Confirm that the result can be understood without relying on one person's memory or private files.
- Record gaps. Note every manual search, missing relationship, unclear abbreviation or external file needed to complete the answer.
A recall drill is useful because it reveals the difference between a spreadsheet that contains information and a process that produces a repeatable answer.
Common spreadsheet weaknesses in manufacturing traceability
Excel remains useful for modelling, investigation and ad-hoc reporting. Problems tend to appear when the file becomes the shared transaction system for receiving, production, quality and dispatch.
Watch for these warning signs:
- Different departments maintain separate copies of the same batch information
- Lot codes are typed in free text with inconsistent formats
- A summary report depends on hidden sheets, manual links or copied values
- Quality approvals are recorded outside the traceability file
- Staff need to ask a particular colleague how to interpret a record
- Corrections overwrite the original evidence
- Rework, split lots or combined lots require workarounds
- A recall query requires searching several files before a result can be trusted
These are traceability issues rather than ordinary stock-control issues. Stock control focuses on what appears to be available. Traceability focuses on the history and relationships behind a material, production and dispatch record.
For related manufacturing workflow guidance, see Spreadsheet Upgrade's manufacturing resources.
When Excel remains appropriate
A spreadsheet can remain appropriate when it supports analysis rather than live operational control. For example, a quality or production manager may use Excel to:
- Review yields and investigate trends
- Analyse production or quality data exported from another system
- Prepare one-off reports
- Test a proposed event structure before formalising the workflow
- Maintain a tightly controlled record with limited process complexity
The key distinction is whether the spreadsheet is being used to analyse established records or to operate the process itself.
When a managed application may be a better fit
A managed custom application may be worth considering when the traceability workflow needs to guide several people through repeatable operational steps.
This is often the case where the process needs:
- Individual user access rather than shared file editing
- Role-specific entry and approval steps
- Controlled relationships between input and output lots
- Consistent handling of holds, releases, rework and exceptions
- A visible record of activity and corrections
- Searchable genealogy across receiving, production and dispatch
- Reporting that draws from one live operational record
Spreadsheet Upgrade provides fully managed custom web applications for business-critical operational Excel workflows. For a batch traceability process, the goal is not to replace Excel simply because it is Excel. It is to move a fragile live workflow into a system that can guide record entry and preserve the relationships needed for an investigation.
Next steps for a more defensible traceability process
Start with the recall question your business must be able to answer: which jobs used this lot, which output lots resulted, and where did they go?
Then review whether your current spreadsheet captures the event relationships, identifiers, quantities, people and supporting records needed to answer it consistently. Strengthen the workbook where it remains suitable for controlled analysis. If it has become the live operational system and the process depends on manual reconstruction, a managed application may provide a more reliable route.