How to Create a Certificate Expiry Tracker in Excel

1. Choose a flat table, not a pretty dashboard

Start with columns you can sort. Fancy dashboards fail when the underlying dates are wrong. Use one sheet as the system of record.

2. Enter dates properly

Format the expiry column as Date. Type DD/MM/YYYY. If you only know month and year, pick a conservative day and note uncertainty in a comments column — do not store “Sep 26” as text.

3. Calculate status with TODAY()

Example logic: if the expiry cell is blank → Missing; if expiry < TODAY() → Expired; if expiry ≤ TODAY()+30 → Due soon; else Current. Adjust the 30-day window to match how long renewals take in your trade.

4. Conditional formatting

• Expired: strong alert colour on the status or date cell
• Due soon: amber on the same cells
• Missing: distinct from current so blanks cannot hide
• Never rely on someone remembering to recolour after an edit

5. Separate “not required”

If a certificate does not apply to a person, either omit the row or mark Not required. Leaving blanks for both “missing” and “N/A” is how false confidence creeps in. Requirements vary by role, activity, customer and site.

6. Evidence column

Paste a SharePoint / Drive link, or a short path convention such as /certs/surname-cscs.pdf. A tracker without files becomes a rumour list under client scrutiny.

7. Put the review on the calendar

Excel will not email you. Book a recurring review, or move the file into a certificate expiry tracker that sends reminders. Download a template if you want conditional formatting already wired — then upload the same sheet when multiple editors break the formulas.

1. Sort by expiry ascending every review.
2. Book renewals for the next 30–60 days.
3. Chase missing evidence as its own queue.
4. Remove leavers promptly.