Supplier certificate tracking spreadsheet template

A supplier certificate tracking spreadsheet needs thirteen columns and one formula. Left to right: supplier name, contact name, contact email, certificate type, issuing body, certificate number, issue date, expiry date, days to expiry, status, last chased, next chase due, file link. Add an owner column if more than one person in the plant touches the sheet. The rule that matters most is that each row is one supplier and one certificate type, not one supplier. A supplier with an allergen declaration, a CoA and a third-party audit certificate gets three rows, because those three documents expire on three different dates and get chased separately.

The formula goes in the days to expiry column: =IF($H2="","",$H2-TODAY()). That gives you a number you can sort on and colour. Status is either a manual dropdown (current, expiring soon, expired, missing) or a formula off the same date. Next chase due is =IF($H2="","",$H2-90), which puts the first reminder ninety days out and gives a slow supplier three months of runway. That is the whole template. It works, and it works for longer than most people expect. Then it breaks in four predictable ways, and it is worth knowing which one is coming for you.

The columns, and why each one is there

• Supplier name. The legal entity name, not the name your buyer uses. Auditors cross-reference the approved supplier list.
• Contact name and contact email. The named person who actually sends documents, not the general sales inbox. This is the column that decays fastest.
• Certificate type. Constrain it with data validation. Free text here means you cannot filter for every lapsed allergen declaration in one move.
• Issuing body. The certification body or laboratory. An auditor asking who issued this should not have to open the PDF.
• Certificate number. Your reference back to the document if the file link ever breaks.
• Issue date and expiry date. Format both as real dates, not text. Paste a date as text once and the formula column returns errors or, worse, plausible nonsense.
• Days to expiry. The sortable number. Sort ascending every time you open the sheet.
• Status. What you read at a glance, and what you would show someone standing next to you.
• Last chased and next chase due. The two columns people leave out, and the two that turn the sheet from a record into a workflow.
• File link. A link to the stored PDF. Not the PDF itself, and not saved in someone's email.

The conditional formatting that does the work

Apply three rules to the whole data range, in this order, with the formula anchored to the column letter only.

1. Expired: =AND($H2<>"", $H2<TODAY()). Red fill, dark red text.
2. Inside thirty days: =AND($H2<>"", $H2-TODAY()<=30). Amber fill.
3. Inside ninety days: =AND($H2<>"", $H2-TODAY()<=90). Pale yellow fill.

Add a fourth rule for the case nobody colours: =$H2="", grey fill. A missing certificate is not a neutral blank, it is the same problem as an expired one, and an uncoloured empty cell is the most common way a gap survives to audit day.

Set the ninety, sixty and thirty day marks deliberately rather than by habit. Ninety days is right for a third-party audit certificate where the supplier has to book an audit. Thirty is usually enough for an allergen declaration they can sign and return the same week.

The setup checklist before you trust it

1. Freeze the header row and the supplier name column.
2. Add data validation dropdowns on certificate type and status.
3. Force the date columns to a date format, then test with a date typed the wrong way round to confirm it rejects or reformats.
4. Sort by days to expiry ascending and leave it that way.
5. Create a matching folder structure for the PDFs, named supplier then certificate type, so a broken link is recoverable.
6. Put a weekly recurring calendar entry on the sheet owner and a second one on a named deputy.
7. Protect the formula columns so a well-meaning colleague cannot paste over them.
8. Add a date this row was last verified column. It is the only honest way to know whether the sheet reflects reality.

The four places it breaks

1. No automated chase

The sheet tells you a certificate expires in sixty days. It does not tell the supplier. Every reminder is an email you write by hand, at a moment when something else in the plant is on fire, to a contact who may not be the right person any more. The failure is not forgetting once. It is that chasing is always postponable, so it gets postponed until the certificate has already lapsed.

2. Stale data

A spreadsheet is a snapshot of the day someone last edited it. TODAY() recalculates when the file opens, so the colours look alive even when the underlying dates are eight months old. A certificate renewed in April and emailed to a colleague who did not update the row still shows red. Green cells nobody has checked are worse than red ones, because red gets acted on.

3. A single owner

One person built the sheet, knows which tab is live, knows that column M is deliberately blank for two suppliers, and knows that row 34 is a special case. When that person is on holiday during an unannounced audit, none of that knowledge is in the file. Shared ownership of a spreadsheet tends to produce two versions of it within a quarter.

4. No send log

An auditor asks what you did about the lapsed CoA. Without a log, the answer is that you chased them, and the evidence is somewhere in a sent items folder. The sheet records the state of the certificate but not the history of your actions, and your actions are the part that demonstrates control. You also cannot tell a chase that was sent from a chase that bounced because the contact left.

When the spreadsheet is still the right answer

Under roughly fifteen suppliers, with one or two certificate types each, one owner and a calendar reminder, the sheet is genuinely fine. The cost of a system is not worth paying yet. The point at which it stops being fine is usually a count, not a feeling: somewhere past forty or fifty supplier and certificate combinations, the weekly review takes longer than an hour and you start skipping it.

What a purpose-built tracker changes

Certly is built around the same columns and the same ninety, sixty and thirty day marks, and it closes three of the four failure modes above. Suppliers and their required certificate types live in a supplier database, certificates are stored against a supplier and a type, and expiry thresholds are computed by a scheduled job rather than by a formula that only runs when someone opens a file, which is the stale data problem. Thresholds are configurable per certificate type from a settings screen, so the ninety day rule for audit certificates and the thirty day rule for declarations are both real rather than a note in your head.

The chase is automated. When a certificate approaches expiry or lapses, an email sequence goes to the supplier contact with a one-click upload link, and every send is logged. The supplier does not need a login, because the link is single-use and expires. There is a chase-now button for the day you need to act immediately, and the log shows whether each send was delivered or failed, so a bounced address surfaces as a badge rather than as silence. The status view is a single screen across current, expiring soon, expired and missing, exportable as a PDF for an auditor to read.

The fourth failure mode, one person holding the conventions of the file in their head, is not one software closes on its own. A tracker can take the private parts out: the thresholds, the status of every certificate and the chase history are recorded rather than remembered. Who covers the record when the owner is away is still a decision you have to write down.

Two things it does not do, stated plainly because the distinction matters when an auditor is in the room: Certly tracks and reminds on certificate expiry, and it does not verify certificate authenticity or guarantee regulatory compliance. The judgement about whether a document is valid stays with you. What changes is that you make that judgement on a current list instead of reconstructing one the week before an audit. Certly is in waitlist stage today, so build the sheet, because at your current size it is the right tool, and because the records it holds, supplier, certificate type, expiry date and chase history, are the same records a tracker keeps when it launches.