COI tracking spreadsheet: the columns that catch a lapse in time

Most general contractors track subcontractor certificates of insurance in a spreadsheet, and for a lot of offices that is the right call. The trouble is that most COI spreadsheets are built to answer "do we have a certificate?" when the question that matters is "which policy expires next, and has anyone asked for the renewal?"

This guide gives you a layout that answers the second question: the columns, two formulas, the formatting, and a fifteen-minute weekly routine. It works in Excel or Google Sheets.

One row per policy, not one row per sub

This is the decision that makes or breaks the sheet. A certificate usually lists several policies, such as general liability, auto, workers' compensation and umbrella, and they often renew on different dates. If you keep one row per sub with a single expiration date, you will either record the earliest date and lose the others, or record the general liability date and miss the workers' compensation policy that lapses two months earlier.

Give every policy its own row. A sub with four policies gets four rows. Sorting by expiration date then shows you exactly what to chase next.

The columns

The two formulas

Assuming the expiration date is in column H and the sheet starts at row 2.

Days left until the policy expires:

=IF(H2="","",H2-TODAY())

Status, with 30 days as the warning window:

=IF(H2="","Missing",IF(H2<TODAY(),"Expired",IF(H2-TODAY()<=30,"Expiring","Current")))

Format the expiration date column as a date and the days-left column as a plain number. If your dates were pasted in as text, the formulas will return errors; re-enter them as dates.

Formatting that does the looking for you

Add conditional formatting to the Status column so the sheet tells you where to look:

• Expired: red fill.
• Expiring: yellow fill.
• Missing: gray fill with bold text.
• Current: no fill.

Then sort the whole sheet by Days left, smallest first. Rows with no expiration date sort to the bottom, so check the Missing rows separately. Freeze the header row and the Subcontractor column so they stay visible as you scroll.

The weekly routine

Pick a time, such as Monday morning, and spend fifteen minutes:

1. Sort by Days left. Everything Expired or Expiring is now at the top; Missing rows are at the bottom.
2. Send requests for anything inside 30 days. Email the sub and copy their agent. Put today's date in Last request sent.
3. Follow up on old requests. Anything with a request more than a week old and no new certificate gets a second email, this time with a phone call to the agent.
4. Log what came in. Save the new PDF next to the old one, update the policy number and dates, and check the limits and endorsements again; coverage can change at renewal.
5. Tell the field about lapses. If a sub on an active job is Expired, whoever runs that job needs to know today. What happens next, such as holding payment or stopping work, depends on what your subcontract allows.

Mistakes that make the sheet lie

• Tracking the date the certificate was issued. The certificate date at the top of the form is when the agent printed it, not when coverage ends.
• Overwriting old certificates. If a claim comes in about work done last year, you need the certificate that was current then.
• Leaving out the agent. Subs often forward renewal requests to nobody. The agent issues certificates every day and usually answers faster.
• Checking dates but not limits. A renewed policy can come back with different limits or without an endorsement it had last year.
• One owner, no backup. If the person who keeps the sheet is out for two weeks, the routine stops. Write the routine down where someone else can follow it.

When the spreadsheet stops being enough

The sheet tells you who to chase; it does not do the chasing. Once you have more subs than you can work through in fifteen minutes a week, or the reminder emails are what keep slipping, it is time to look at COI tracking software.

COI Nudge keeps this same list for you: subs upload certificates through one link, it reads the expiry dates from each certificate, and it emails the sub and their insurance agent at 30, 14 and 7 days before a policy lapses, until a new certificate arrives. You see one list of who is current, expiring and missing.