Certificate of Insurance Tracking Spreadsheet: Flag Expiring Sub COIs Before They Cost You

If you run jobs with subcontractors, your single biggest uninsured exposure is not the bid you lost or the change order you ate. It is the moment a sub's general liability policy lapsed two weeks ago and one of their guys puts a circular saw through his thigh on your site. Your insurance gets tapped. Your modifier goes up. Your premiums climb 20 to 40 percent at renewal. And nobody told you the policy had expired because the COI was sitting in an email from nine months ago that you never followed up on.

A certificate of insurance tracking spreadsheet exists to make that conversation impossible. Not because Excel is the perfect tool for insurance management, but because the alternative for most small to mid-size general contractors is worse: a manila folder, a Gmail label, and a memory that fails when you scale past five subs. The point of this article is to show you what an actually useful COI tracker looks like, the formulas that turn it from a static log into an early warning system, and why most contractors get this wrong even when they do build one.

Why COI Tracking Is a Financial Control, Not a Filing Chore

Every GC owner I talk to who has been sued by a subcontractor's injured worker says the same thing afterward: "I thought we had a COI on file." They did. It was from 2022. The job was in 2024. The policy expired six months before the accident, and nobody at the office had a system to catch it.

Run the math on what that costs:

Now compare that to the cost of building and maintaining a real tracking system: roughly four hours of setup, fifteen minutes a week of upkeep, and zero recurring software fees. The ROI on a COI spreadsheet is one of the highest in any construction back office. The fact that most GCs still do not have one is purely an attention problem, not a tooling one.

What Your COI Tracking Spreadsheet Must Actually Track

Most templates floating around online track three fields and call it done: subcontractor name, expiration date, and a "received yes/no" column. That is not tracking, that is a list. A working COI tracker captures the data that lets you answer four questions in under thirty seconds:

1. Whose insurance expires in the next 30, 60, or 90 days?
2. Which active subs are non-compliant right now, and why?
3. For each policy, do we have the right endorsements (additional insured, waiver of subrogation, primary and non-contributory)?
4. What is our total open contract value sitting under non-compliant COIs?

To answer those, your sheet needs columns for the following. Use one row per subcontractor per policy type, since GL, auto, workers comp, and umbrella all expire on different schedules and carry different limits.

The Formulas That Turn a List Into a Warning System

This is where most spreadsheets die. People build the column structure and then check it manually every quarter, which defeats the entire purpose. The formulas below give you a sheet that flags itself.

Days Until Expiration

In column P, calculate days remaining on each policy. Negative numbers mean expired.

=I2-TODAY()

Format this column with conditional formatting: red fill if less than or equal to 0, orange if between 1 and 30, yellow if 31 to 60, no fill above 60. The visual scan replaces a manual review.

Status Flag With Multiple Conditions

Column Q is the workhorse. It tells you in plain English whether you can let this sub on site tomorrow.

=IF(P2<=0,"EXPIRED - STOP WORK",IF(P2<=30,"EXPIRES SOON - REQUEST RENEWAL",IF(OR(L2="No",M2="No",N2="No",O2="No"),"NON-COMPLIANT - MISSING ENDORSEMENT","COMPLIANT")))

This single formula nests four checks: expired, expiring within 30 days, missing required endorsements, or fully compliant. The output is a directive, not a status. "EXPIRED - STOP WORK" tells your super what to do without you having to translate.

Compliance Score

Some owners want a percentage. Column R gives you one based on the four endorsement columns.

=(COUNTIF(L2:O2,"Yes")/4)*100

Anything below 100 is a gap. Use this for monthly reports to owners on OCIP or CCIP projects where compliance scoring is contractually required.

Total Exposure Under Non-Compliant COIs

On a summary tab, this formula tells you exactly how much open contract value is currently riding on insurance you cannot rely on.

=SUMIFS(D:D,Q:Q,"EXPIRED - STOP WORK")+SUMIFS(D:D,Q:Q,"NON-COMPLIANT - MISSING ENDORSEMENT")

If that number is anything other than zero on a Monday morning, your week starts with phone calls to subcontractors. Not bids. Not invoices. Phone calls.

30-Day Renewal Pipeline

Build a separate tab listing every sub whose policy expires in the next 30 days, so you can batch outreach instead of chasing one at a time.

=FILTER(A:I,(P:P<=30)*(P:P>0))

If you are on a version of Excel without dynamic arrays, the SUMIFS and INDEX/MATCH combos work too, but FILTER is cleaner and updates in real time.

The Three Mistakes That Make COI Tracking Useless

Mistake 1: Tracking Only the Expiration Date

An unexpired COI with no waiver of subrogation and no additional insured endorsement gives you almost nothing. The carrier can subrogate against your insurer, and you are not named on the policy so the sub's coverage does not respond on your behalf. You need all four endorsement checks, every time, on every policy. Column structure forces this. A free Word doc COI form does not.

Mistake 2: Not Tracking Contract Value at Risk

If your spreadsheet does not link COIs to active job dollars, you cannot prioritize. A $1,200 plumbing repair under a lapsed COI is irritating. A $340,000 framing contract under a lapsed COI is an emergency. Column D and the SUMIFS exposure formula are what make this distinction obvious. Without them, every non-compliance flag looks the same and you triage badly.

Mistake 3: Logging the COI on Receipt and Never Looking Again

Most tracking spreadsheets are write-once. A sub sends a COI in January, someone enters the data, and the row sits there until someone needs it in November. By then the policy expired in April. The fix is the conditional formatting plus a weekly 15-minute review on Mondays: filter Q for anything that is not "COMPLIANT" and work the list. Build this into your Monday operations meeting and it stops being optional.

Sample Output: One Week of COI Monitoring

Here is what a working COI tracker looks like on a Monday morning for a GC running 11 active subs across four projects:

Total exposure under non-compliant or expired policies in this snapshot: $460,500. That is the number you should be working down to zero every Monday before you do anything else. If your current system cannot produce that figure in less than a minute, your current system is not working, regardless of how organized your file folders look.

When to Move Off a Spreadsheet

For 5 to 50 active subs, Excel is faster, cheaper, and more flexible than any dedicated SaaS. You can change a column, add a project, restructure a tab in five minutes. Software like myCOI or Jones costs $150 to $500 per month, requires onboarding, and locks you into their data model. The break point is usually around 75 to 100 active policies or when an OCIP requires direct integration with the owner's compliance portal. Below that, a well-built spreadsheet outperforms.

The threshold is not the number of subs. It is the number of hours per week you spend on COI work. If you cross three hours, automation pays. Below that, a sheet wins every time.

Build It Once, or Start From Something Built

You can build the structure above from scratch in an afternoon if you know Excel. The formulas are not advanced. The discipline is in the column design and the conditional formatting, which is where most homemade trackers fall apart by the third week. The other path is to start from a tracker that already integrates COI tracking with the rest of your project financials, so the contract values, change orders, and sub compliance live in the same workbook instead of three different files.

The SheetCraft Construction Budget Tracker includes a subcontractor compliance module with the formulas above already wired in, conditional formatting pre-configured, and a one-click summary view showing total dollar exposure under non-compliant COIs across all your active jobs. If your Monday morning currently starts with "I think we have a COI for Reyes somewhere," that is the spreadsheet you want open instead.