Free COI Tracking Spreadsheet Template (and When You'll Outgrow It)
The CertifiKit team
Certificate-of-insurance desk
A COI tracking spreadsheet needs one row per vendor and columns for each coverage line's limits and expiration dates, plus a conditional-format rule that turns a row red 30 days before any policy expires. Below is the exact column set to copy, the formulas that do the alerting, and — because we'd rather be honest — the math on when the spreadsheet stops working.
What columns should a COI tracking spreadsheet have?
Copy this into row 1 of a fresh sheet:
| Column | Notes |
|---|---|
| Vendor / Sub name | Must match the named insured on the certificate exactly |
| Trade / service | Roofer, landscaper, electrician… |
| GL limit (each occ / aggregate) | e.g. 1,000,000 / 2,000,000 |
| GL expiration | Date format — the alert formulas key off this |
| Workers' comp limit + expiration | The line vendors most often drop |
| Auto limit + expiration | If you require it |
| Umbrella limit + expiration | If you require it |
| Additional insured? (Y/N + form #) | "Y — CG 20 10" beats "Y" |
| Waiver of subrogation? (Y/N) | |
| Cert received date | Your audit trail |
| File link | To the PDF in your drive |
| Status | Formula below |
What formulas make it actually useful?
Days until soonest expiration (assuming GL expiry in column E, WC in G, auto in I):
=MIN(E2,G2,I2)-TODAY()
Status flag:
=IF(MIN(E2,G2,I2)-TODAY()<0,"EXPIRED",IF(MIN(E2,G2,I2)-TODAY()<30,"EXPIRING","OK"))
Add conditional formatting — red for EXPIRED, amber for EXPIRING — and sort by the days column weekly. That's a genuinely workable system. For what "compliant" should mean per row, use our subcontractor insurance requirements guide as the rubric.
What the spreadsheet can't do
The sheet records what you typed; it has no idea what the certificates say. Every certificate still has to be read and re-keyed by hand — limits, dates, endorsements — which is exactly where the process breaks:
- Re-keying errors. One transposed expiration date and your "tracking system" is confidently wrong for a year.
- The chase is still yours. The sheet flags an expiring cert; you still write the emails, wait, re-email, and call the agent. Document chasing eats 8+ hours a week for many office managers.
- Endorsements get reduced to Y/N. Whether the additional insured endorsement is actually the right form, for the right operations, doesn't fit in a cell.
- No audit trail. When a claim hits and a lawyer asks what you verified and when, a spreadsheet cell has no memory.
When do you outgrow the spreadsheet?
The honest rule of thumb: the spreadsheet works up to about 10–15 vendors with one renewal season of discipline. Past that, the failure mode isn't the sheet — it's the hours. Thirty vendors means a renewal email thread roughly every three days, forever.
The enterprise COI platforms solve this starting at thousands of certificates and a sales call. For the 10–300 vendor range there's CertifiKit: it reads each certificate itself, verifies it against your requirements in 60 seconds, emails fix lists to the issuing agents, and keeps the audit trail the spreadsheet never had — from $99/month, self-serve.
Start by seeing what it finds: run any vendor's current certificate through the free COI checker. If it comes back clean, your spreadsheet's doing fine.