Guides / COI tracking
How to track subcontractor certificates of insurance (free spreadsheet template)
A simple, reliable system for small GCs and property managers: what to require, how to set up a tracker that flags expiring coverage, and how to stop chasing the same subs every month.
What a COI is (and isn't)
A certificate of insurance (COI) is a one-page summary showing that a subcontractor or vendor had certain insurance policies on the date it was issued. In the US the most common form for liability coverage is the ACORD 25 "Certificate of Liability Insurance". It lists the insurers, policy numbers, effective and expiration dates, and limits for coverages like general liability, auto, umbrella and workers' compensation.
Two things trip people up:
- A certificate is information, not coverage. The ACORD 25 itself states that it is issued as a matter of information only, confers no rights on the certificate holder, and doesn't amend, extend or alter the policies. What actually protects you is in the policy and its endorsements.
- It goes out of date. Policies renew, get cancelled or change. A certificate that was fine at onboarding can be expired by month six of a job.
That's why tracking COIs is really about tracking expiration dates and requirements over time, not about collecting a PDF once.
Step 1: Write down what you require
Start from your subcontract (and any requirements passed down from the owner or your own insurer). For each trade, note:
- Which coverages you need: typically general liability, workers' comp/employer's liability, auto, and sometimes umbrella or professional.
- Minimum limits for each, e.g. each-occurrence and aggregate for general liability.
- Whether you need to be an additional insured, whether you need a waiver of subrogation, and whether coverage must be primary and non-contributory.
- Exactly how your company should appear as certificate holder.
If you're not sure what your numbers should be, that's a question for your insurance agent and your contract, not for a tracker. See what to check on a COI for what each item means.
Step 2: Collect before work starts
Make a current, compliant COI a condition of mobilising. Ask the sub to have their insurance agent send the certificate directly to you. It's usually faster and harder to alter than a forwarded PDF. Send your written requirements with the request so the agent can issue the right certificate (and endorsements) the first time.
Step 3: Set up the tracker (one row per policy)
The single most useful rule: one row per policy, not one row per subcontractor. A sub with general liability, workers' comp and auto has three expiration dates, and they rarely match. If you squeeze them onto one row, you'll eventually sort by the wrong date.
Useful columns:
| Column | Why it matters |
|---|---|
| Subcontractor (legal name) | Should match the named insured on the certificate and the name on your contract. |
| Coverage type | GL, workers' comp, auto, umbrella… one per row. |
| Insurer & policy number | What your agent or auditor will ask for first. |
| Effective & expiration dates | The expiration date drives everything. |
| Limits vs required minimum | Lets you spot under-insured subs at a glance. |
| Additional insured / waiver / holder correct | Tick only once you've actually checked. |
| COI contact email | The person who sends certificates, often the agent, not the sales rep. |
| Last verified & file link | When you last looked, and where the PDF lives (a shared drive, not your laptop). |
| Status (automatic) | Current / Expiring soon / Expired / Missing. |
The status column is what turns a list into a tracker. In Excel or Google Sheets, with the expiration date in column H:
=IF(H2="","Missing",IF(H2<TODAY(),"Expired",IF(H2<=TODAY()+30,"Expiring soon","Current")))Add conditional formatting so "Expiring soon" rows turn amber and "Expired" rows turn red. Our free template has this built in.
Step 4: Chase renewals on a schedule
A spreadsheet doesn't send reminders, so give yourself a routine:
- Weekly: filter by status and email everyone amber or red.
- A common cadence: request the renewal about 30 days before expiry, follow up around 14 and 7 days, and escalate on the day it lapses.
- Email the agent and the sub together. Agents can usually issue a renewed certificate quickly once the policy has renewed.
- When the new certificate arrives, re-check it. Limits and endorsements can change at renewal.
Step 5: Tie it to payment
Many contractors put a simple rule in the subcontract: no pay application is released while any required coverage is expired or missing. It turns chasing from your problem into theirs, and subs tend to send renewals on their own once they know it holds up payment. Have the clause reviewed as part of your contract documents.
Step 6: Keep history for audits
Don't overwrite old rows. Add a new row for each renewal and keep the old certificate. If your workers' comp or general liability insurer audits your payroll and subcontractor costs, you may be asked to show that subs were insured during the period. Without proof, insurers may treat the payments as uninsured labour, which can affect your premium. Ask your agent how your carrier handles this.
Free COI tracking template
CertRally COI tracker template: one row per policy, an automatic status formula, amber/red highlighting, dropdowns, and a "how to use" tab. Works in Excel, Google Sheets (File → Import) and Numbers.
No email required. The CSV has no formulas; add the status formula above after importing.
When a spreadsheet stops working
For a handful of subs and one tidy owner, a spreadsheet is genuinely fine. Signs you've outgrown it:
- Someone spends several hours a month emailing for renewals.
- You've found an expired policy after the sub was already on site.
- More than one person updates the sheet and nobody trusts it.
- You can't quickly pull every certificate for a past policy period.
At that point it's worth looking at a dedicated tool. Several have free tiers. We compare them honestly in COI tracking software for small contractors, compared.