COI tracking spreadsheet template: columns and expiry alerts

A COI tracking spreadsheet template needs one row per subcontractor, the expiry date of their current certificate of insurance, and a formula that flags the row 30 days before it lapses. The date is what matters: a certificate on file with an expired date is a gap in coverage nobody is watching.

This guide gives the columns, the three formulas, a renewal routine and the limits of a sheet. It tracks dates and receipt only. Whether a certificate's limits and endorsements meet your contract is a judgment for you or your insurance advisor, and this guide does not make it.

What columns should a COI tracking spreadsheet have?

Use these eight:

Add a Project column only if one sub's certificate differs per project. Otherwise keep one row per sub, because the certificate belongs to the sub's policy, not the job.

What formulas flag an expiring certificate?

Three formulas do the work, assuming the expiry date is in column D:

1. Days left: =D2-TODAY()
2. Status: =IF(D2="","Missing",IF(D2<TODAY(),"Expired",IF(D2-TODAY()<=30,"Expiring","Current")))
3. Conditional format: red when Status is Expired, amber when Expiring.

Thirty days is a working choice, not a rule: it is long enough for a sub to ask their broker for a new certificate. Pick the lead time that fits your subs and your contract.

How do I check a certificate when it comes in?

The date is the first check, but not the only one. When a certificate arrives:

1. Confirm the name on it matches the sub you contracted with.
2. Read the policy dates against the job dates.
3. Check the limits and any endorsements your contract requires.
4. Enter the earliest expiry date among the policies you rely on.
5. Save the file and paste the link in the sheet.

Steps 1, 3 and 4 are human checks. No spreadsheet formula reads the certificate for you, and a sub typing a date into a form does not prove the date is right. This is why the thread where a poster found a sub's certificate had lapsed unnoticed for three weeks is a tracking problem, not a reading problem: the date was known to nobody who looked.

How do I run the renewal routine?

Once a week, filter Status to Expiring, Expired and Missing, and for each row:

• send the sub a request that names the date and asks for the new certificate;
• write the date you asked in the Note column;
• follow up after a few working days;
• move any sub still expired onto a list for the project manager before they are on site.

The sheet records all of it, but you do the sending. The first week the list is long; after that it is a handful of rows.

When should I stop using a spreadsheet?

When the follow-ups, not the tracking, take the time. A sheet cannot send the request, cannot remind a sub who ignored it, and cannot attach the certificate to the row. If you are doing that for 30 or more subs, look at COI tracking software and compare what each option does about the chase. If your certificates arrive alongside lien waivers each month, the lien waiver tracking spreadsheet lays out the matching sheet.

What does Waiver Chase do with COIs?

Waiver Chase asks each sub for their certificate on the same no-login upload link as their lien waiver. The sub enters the expiry date and uploads the file (PDF, JPG or PNG, up to 4 MB), and a past date is rejected at upload. You review each certificate and accept or reject it with a reason. It stores the expiry date of every accepted certificate, asks the sub for a renewal 30 days before, and shows an expired one in red on the tracker and in your digest the next morning. It does not read the certificate or check coverage limits or endorsements, so you still check those yourself.

The free run on this page builds a COI tracker from up to ten of your own subs, one row per sub, and shows the email each sub would get asking for its certificate.

FAQ

How far ahead should I ask for a renewed certificate?

Thirty days is a common working choice because it leaves time for the sub's broker. Check whether your contract sets a different notice period.

Who is responsible for the certificate being current?

That is set by your contract and the sub's agreement with you. A tracking sheet tells you the date; it does not decide who is responsible.

Can I use one sheet for waivers and certificates?

You can, but filters get awkward. Many teams keep one sheet per requirement, since waivers repeat every month and certificates repeat once a year.