Free COI tracking spreadsheet for Excel and Google Sheets

Updated October 2026. Written for US construction and property firms.

This is a free certificate of insurance tracking template. It works in Excel and Google Sheets. Expiration dates turn red, yellow or green on their own. There is no form to fill in and no email required. Click a link below and the file is yours.

What is in this free COI tracking template?

The template has 14 columns for one row per insurance policy. It tracks the subcontractor, the policy, the dates, the limits and the endorsements you need. The Expiration Date column changes color by itself. Three example rows show red, yellow and green so you can see it working.

The Excel file also has a short "How to use" tab. Delete the three example rows when you start. Their dates are formulas, so they always show one of each color no matter when you open the file.

One row per policy matters. Most subs carry at least two policies, general liability and workers' comp. Each policy has its own expiration date. If you squeeze them into one row, the earlier date gets missed.

How do I use the template in Excel?

Download the .xlsx file and open it in Excel. The header row is frozen, filters are on, and the color rules are already set up. Delete the three example rows. Then type in one row per policy from each certificate you have on file. The colors update every time you open the file.

The two Y/N columns have dropdowns, so nobody types "yes" in one row and "Y" in the next. Dollar limits are formatted as currency. Dates are formatted as month, day and year.

How do I use the template in Google Sheets?

Download the .csv file. In Google Sheets, create a blank spreadsheet and import the file. A CSV file cannot carry colors, so you add three conditional formatting rules by hand. It takes about two minutes. The exact menu steps and formulas are below. Copy them exactly as written.

Step 1: import the file

1. Open a blank Google Sheet.
2. Click File, then Import, then the Upload tab, and choose the .csv file.
3. For Import location, pick Replace current sheet. Leave "Convert text to numbers, dates, and formulas" ticked. Click Import data.
4. Click View, then Freeze, then 1 row, so the headers stay put when you scroll.
5. Select columns G, H and M. Click Format, then Number, then Date.

Step 2: add the three color rules

1. Select the range H2:H500. Column H is Expiration Date.
2. Click Format, then Conditional formatting. A panel opens on the right.
3. Under Format rules, open the dropdown and pick Custom formula is.
4. Paste the red formula below. Set the fill color to light red. Click Done.
5. Click Add another rule. Repeat with the yellow formula and a light yellow fill.
6. Add a third rule with the green formula and a light green fill.

Keep the rules in that order, red at the top. Google Sheets uses the first rule that matches, so an expired date shows red and not yellow. The $H2<>"" part keeps empty rows white.

What goes in each column?

Each column maps to a box on the ACORD 25 certificate, the standard one-page form insurance agents use. You copy the details from the certificate into one row per policy. The walkthrough below says where to find each item and what to watch for. Insurance terms are explained in plain words.

1. Subcontractor. The legal business name exactly as it appears in the "Insured" box. It should match the name on your subcontract.
2. Trade. Electrical, roofing, plumbing and so on. Trade tells you which coverage to expect. Roofers usually need more than painters.
3. Contact Email. The person who answers for insurance at the sub's office. This is who you chase for renewals.
4. Policy Type. General liability (covers injuries and property damage the sub causes), workers' comp (covers the sub's injured employees), commercial auto, or umbrella (extra coverage on top of the others).
5. Carrier. The insurance company itself, listed under "Insurer A", "Insurer B" and so on. It is not the agent.
6. Policy Number. Copy it exactly. You need it if you ever have to report a claim.
7. Effective Date. The day the policy started. If it starts after the job starts, the early days were not covered.
8. Expiration Date. The day coverage ends. This is the column that changes color. It is the most important cell in the row.
9. Each Occurrence Limit. The most the policy pays for one incident. $1,000,000 is the common minimum for general liability.
10. Aggregate Limit. The most the policy pays in total for the whole policy year. $2,000,000 is common. Workers' comp usually shows "Statutory" instead, which means the limit your state law sets.
11. Additional Insured (Y/N). Y if your company is added to the sub's policy, so you can claim on it directly. Ask for the endorsement too, which is the written change to the policy. See additional insured vs certificate holder.
12. Waiver of Subrogation (Y/N). Y if the sub's insurer has agreed not to sue you to get its money back after paying a claim.
13. Last Reminder Sent. The date you last emailed the sub about a renewal. It shows who you are chasing and for how long.
14. Notes. Anything else. Missing endorsements, a new agent's name, or "off site until renewed".

How do the red, yellow and green colors work?

The colors come from conditional formatting rules that compare each expiration date to today's date. Red means the date has passed. Yellow means it falls within the next 30 days. Green means more than 30 days remain. Because the rules use TODAY(), the colors update by themselves every time you open the file.

Thirty days is the standard warning window, because brokers can take a week or two to issue a renewal certificate. If you want the Excel rules in your own sheet, select the Expiration Date column. Then click Home, Conditional Formatting, New Rule, and Use a formula to determine which cells to format. Enter the same three formulas without changing them. Then open Manage Rules and tick Stop If True on each one.

How should I run the spreadsheet each week?

Set a fixed 15 minutes every Monday. Sort by Expiration Date so the oldest dates rise to the top. Email every sub in red or yellow and log the date in Last Reminder Sent. Anyone still red should stay off site until a current certificate arrives. Then file the new certificates and update their rows.

1. Sort by Expiration Date, oldest first.
2. For each red or yellow row, send a renewal request. Our COI request email template is ready to copy.
3. Write today's date in Last Reminder Sent.
4. Tell your site lead which subs are red. No current certificate means no site access.
5. When a renewal certificate arrives, update the dates. The cell turns green.

When does this spreadsheet stop working?

A spreadsheet is fine with eight subcontractors. At thirty it becomes the bottleneck. It cannot email a sub when a date turns yellow. It cannot notice that nobody opened the file for three weeks. Every reminder depends on one person remembering. Missed renewals happen silently, usually right before an audit or a claim.

Here is how to tell you have outgrown it.

• You spend more than an hour a week chasing certificates.
• A policy lapsed and nobody noticed until someone asked.
• Only one person in the office knows how the sheet works.
• Your premium audit turned up subs with no certificate on file. Carriers add those payments to your own premium.

That is the point where CoiPatrol pays for itself. You upload the ACORD 25 PDF and it reads the dates and limits for you. Reminders go out at 30, 14 and 7 days without anyone remembering. It is free for up to 10 subs and $49 a month for up to 50. Our Excel vs software comparison covers the decision in more detail.

How do I track subcontractor certificates of insurance?

Collect a COI before the sub gets site access, log the coverage types, limits, and expiration dates, verify them against your contract requirements, and request renewals about 30 days before expiry. A spreadsheet works up to roughly 10 to 15 active subs. Past that, dedicated tracking software prevents missed renewals.

Is Excel good enough for COI tracking?

For a handful of subs, yes: a sheet with expiry dates and conditional formatting does the job. It breaks down past a few dozen vendors because it cannot send renewal reminders, chase subs automatically, or flag coverage gaps, and missed renewals happen silently.

How far ahead of expiration should I request a renewal COI?

30 days before the policy expiration date is standard. Some firms start at 45 or 60 days for slow-responding subs. Automated reminders at 30, 14, and 7 days are a common cadence. In this template, yellow starts exactly 30 days before expiry, so yellow means it is time to send the request.