Free COI tracking spreadsheet template

How to use it

1. Download the CSV and open it in Excel or Google Sheets.
2. Fill one row per subcontractor.
3. Add the status formula to the Status column (copy it from the box above).
4. Apply conditional formatting: red for Expired, amber for Expiring.
5. Check it every week and store certificates in a folder named after the sub.

How to read the Status column

• Covered: every end date is more than 30 days away.
• Expiring: at least one end date is within 30 days. Chase the renewal now.
• Expired: at least one end date has passed. Treat it as a gap.
• Missing: no dates at all. You don’t have a certificate yet.

The status uses the earliest date in the row, because the first policy to end is the one that matters.

Common mistakes

• Tracking one date per sub instead of one per policy.
• Typing the date from memory instead of from the certificate.
• Keeping the sheet but never opening it. Put a weekly reminder in your calendar.

When does a spreadsheet stop working?

When one person owns it, nobody gets reminded, and the dates are not backed by documents. At that point, compare options or run the free audit.

Two formulas to copy

Put each end date in its own column. Then add a status column. In Excel or Google Sheets, if the date is in column F:

=IF(F2="","MISSING",IF(F2<TODAY(),"EXPIRED",IF(F2<TODAY()+30,"EXPIRING","OK")))

To count how many are expired, use:

=COUNTIF(G2:G200,"EXPIRED")

Add conditional formatting so EXPIRED is red and EXPIRING is yellow. Now the problems stand out when you open the file.

Rules that keep it honest

• One row per sub per project, not one row per sub.
• One column per policy end date.
• Never overwrite an old date. Add a new row or note when a policy renews.
• Pick one person to update it every Monday.