The supplier certificate tracking spreadsheet, and the four places it quietly fails

A supplier certificate tracking spreadsheet template needs about eleven columns to earn its place: supplier name, supplier contact name, contact email, certificate type (allergen declaration, certificate of analysis, audit certificate), issuing body or scheme, issue date, expiry date, a link or filename for the document itself, a status cell driven by a formula off the expiry date, the date you last chased, and a notes field. Add conditional formatting on the expiry column so anything inside 90 days turns amber, inside 30 days turns darker, and anything past today turns red, and you have a register that shows on one screen which documents you hold and which are running out. That is a genuinely useful tool. It is free, it takes an afternoon, and it bends to your plant's naming conventions in a way bought software usually will not.

Where it fails is not in the columns. It fails in four specific places, and all four are quiet, which is the problem: nothing in the sheet chases a supplier, the data is only as fresh as the last person who typed in it, one person understands the file, and there is no record of what you actually sent to whom. None of those show up on a Tuesday. They show up when an auditor asks for the current allergen declaration for a supplier you stopped thinking about in March.

The columns a working template actually has

If you are building the sheet from scratch, or inheriting one, these are the fields that carry weight:

• Supplier name and site. One row per supplier site, not per company. Two sites of the same group hold different audit certificates.
• Contact name and contact email. The person who actually sends you documents, not the sales contact. This is the column that decays fastest.
• Certificate type. Keep the list closed. Three or four types consistently named beat fifteen free-text variants.
• Required flag. Which types you require from this supplier. Without it, you cannot tell a missing certificate from one you never needed.
• Issue date and expiry date. Real dates, formatted as dates, not text. A column of text dates breaks every formula downstream.
• Document location. A link or a filename that matches a file in a shared folder, with a naming rule you can state in one sentence.
• Status. A formula off the expiry date, not a hand-typed word. If expiry is blank, status is Missing. If expiry is before today, Expired. Within 30 days, Expiring. Otherwise Current.
• Last chased date and next action. The two fields that turn a register into a worklist.

A fifteen-minute audit of your own sheet

Run these checks on the file you have now, in order. Each one takes a minute or two and each one maps to a failure mode below.

1. Sort by expiry date ascending. Count the rows already in the past. Those are expired certificates sitting on your register today.
2. Filter the status column for blanks and for hand-typed values. Any cell a human typed is a cell that stopped updating.
3. Pick five rows at random and open the linked document. Check the expiry date on the PDF against the expiry date in the sheet.
4. Count the rows with no expiry date at all. Decide, row by row, whether that is a missing certificate or a type you do not require.
5. Look at the last chased column. How many rows have never been chased, and how many were chased so long ago that the chase clearly went nowhere.
6. Ask yourself who else in the plant could answer all five of the questions above from this file without you in the room.

Failure one: nothing chases

Conditional formatting is a signal, not an action. The cell turns amber, and then a human has to notice it, open the supplier record, find the right contact, write the email, and remember to come back. On a normal week that happens. On the week of a customer complaint, a line changeover and a holiday, it does not, and the amber cell quietly goes red.

The honest version of this failure is that the sheet moves the work to the person least likely to have a spare hour. Certly is built to do the chasing itself: expiry tracking with configurable alert thresholds at 90, 60 and 30 days, and automated chase sequences to the supplier contact as a certificate approaches expiry or lapses, each one carrying a one-click upload link. Thresholds are set per certificate type, because a CoA on a twelve-month cycle does not need the same runway as a three-year audit certificate.

Failure two: the data goes stale between openings

A spreadsheet recalculates when someone opens it. Until then, the register is a snapshot of whenever it was last touched. A certificate that expired on the fourteenth still reads Current on the printout you took on the tenth, and if the status column was typed rather than calculated, it reads Current forever.

This is the failure mode that reaches the auditor, because the document you hand over is usually a print or a PDF of the sheet. Certly is built to compute status on a schedule rather than on a page view. When it launches, the status dashboard shows the four states, current, expiring soon, expired and missing, per supplier on one screen, with a PDF export that uses the identical labels and badges as the screen. The export also carries a two-sentence line stating plainly that Certly tracks and reminds on certificate expiry, and does not verify certificate authenticity or guarantee regulatory compliance. That is the right boundary for any tracking tool, spreadsheet included: it tells you what you hold, not whether the document is sound.

Failure three: one owner

Every plant has one person who knows that column M is only populated for the dairy suppliers and that the red rows at the bottom were superseded. When that person is on leave during an unannounced audit, the file is still there and the knowledge is not.

There is no formula for this. The partial fix inside a spreadsheet is a tab that states the rules in plain sentences: what each column means, what a blank means, where the files live, who to chase for what. Write it as though you are handing the file to a colleague on their first day, because one day you will be. A shared system helps for a different reason, which is that the required certificate types live on the supplier record rather than in someone's head, and the upload link goes to the supplier directly instead of through one inbox.

Failure four: no record of what was sent

This is the one that costs money. An auditor asks why a certificate lapsed in February, and the defensible answer is a dated trail: we requested it on these dates, to this contact, and here is the reply. A spreadsheet holds a last chased date, which is a claim without evidence, and your sent folder holds the evidence in a form nobody can search under pressure.

It gets worse quietly. An email that bounced because the contact left the company looks identical, in the sheet, to an email that landed and was ignored. Certly is built to log every chase per send against the supplier and to show the send status, sent, failed or opened, with a resend action next to a failed one, so a bounce reads as a bounce rather than as contact made. There is also a chase now action on each row for the case the sequence does not cover, the supplier you need to push today.

What to keep from the spreadsheet

If you move off the sheet, keep three things. Keep the closed list of certificate types, because it is the hardest part to agree and the easiest to migrate. Keep the required-by-supplier flags, since that is the only way Missing means anything. Keep the expiry dates in a date format, exported as CSV, because every system you might move to will accept them and no system will fix text dates for you.

And keep the spreadsheet itself for a while after. Not as the register, but as the thing you reconcile against for the first month, so that the first time the two disagree you find out why rather than guessing which one to trust.