COI tracking spreadsheet template (free Excel)
An Excel workbook for whoever tracks subcontractor certificates of insurance in a general contractor's office: one row per subcontractor, with each policy's limits and expiration date. It opens in Google Sheets too, and there is no form in front of the download.
What is in the workbook
The download is one Excel file with four sheets. Nothing stands between you and it: there is no form to fill in and no email address to give.
| Sheet | What it holds |
|---|---|
| Tracker | One row for each subcontractor. Six example rows with made-up companies show the format, and the 200 rows under them are already formatted. |
| Requirements | Two starter requirement sets, one for a trade working on site and one for a vendor or supplier, each with its limits and endorsements. They are starting points, to be replaced with what your subcontracts and your owner contract require. |
| Monthly numbers | Seven numbers to fill in at the end of each month, with a column for each month: active subcontractors, the share fully compliant, expiring in 30 days, deficient, paid while non-compliant, average days to onboard and open exceptions. |
| How to use | Nine short steps, for whoever opens the file after you. |
The columns of the Tracker sheet
The Tracker has 21 columns to fill in and two that work themselves out. Each policy has an expiration date of its own, because a subcontractor’s policies do not always end on the same day.
| Heading | What goes in it |
|---|---|
| Company Name | The subcontractor, as it appears on their certificate. The one column every row needs. |
| Trade | What they do for you: electrical, roofing, concrete. |
| Contact Name | Who to ask for a renewal. |
| Where a request for a new certificate goes. | |
| Phone | For your own use. |
| Insurer | The insurance company named on the certificate. |
| GL Policy Number | The general liability policy number. |
| GL Each Occurrence | The general liability each occurrence limit. |
| GL Aggregate | The general liability general aggregate limit. |
| GL Expiration Date | The day the general liability policy ends. |
| Workers Comp Each Accident | The employers' liability each accident limit on the workers' compensation policy. |
| Workers Comp Expiration Date | The day the workers' compensation policy ends. |
| Auto Combined Single Limit | The automobile liability combined single limit. |
| Auto Expiration Date | The day the automobile liability policy ends. |
| Umbrella Each Occurrence | The umbrella or excess each occurrence limit. Leave it empty where none is carried. |
| Umbrella Aggregate | The umbrella or excess aggregate limit. |
| Umbrella Expiration Date | The day the umbrella or excess policy ends. |
| Additional Insured | Yes when the certificate marks you as an additional insured, No when it does not. |
| Waiver of Subrogation | Yes when the certificate marks a waiver of subrogation, No when it does not. |
| Primary & Non-Contributory | Yes when the certificate states primary and non-contributory wording, No when it does not. |
| Notes | What the other columns cannot hold: an endorsement form you are waiting for, what you asked for and when. |
| First policy ends (calculated) | Worked out for you: the earliest of the row's four expiration dates. |
| Status (calculated) | Worked out for you: Expired, Expires within 30 days or Current. |
How to fill it in
Type over the six example rows, or delete them
Every company, person and policy number in them is made up. Keep the heading row as it is.Add one row for each subcontractor
Fill it in from the certificate in front of you, not from memory. Leave a cell empty when the certificate does not say: an empty cell is honest, and a guess is not.Type each limit as a plain amount and each date as a date
An amount typed as 1000000 shows as $1,000,000. A date typed as 31-Dec-2027 or 12/31/2027 shows as 31-Dec-2027.Choose Yes or No in the three endorsement columns
A Yes records that the certificate says so, and nothing more. The endorsement form itself is the evidence, so use the Notes column to say whether you hold it. Leave the cell empty when you do not know.
What the two calculated columns do
The two grey columns at the far right are formulas. Do not type in them.
- First policy ends (calculated) is the earliest of the row’s four expiration dates.
- Status (calculated) reads Expired, Expires within 30 days or Current. It is worked out from today’s date each time the file is opened, and the cell is coloured to match the word.
Use the filter arrows in the heading row to sort or filter by Status. Status looks at the dates and at nothing else: it does not read a limit or an endorsement.
Opening it in Google Sheets
The workbook opens in Google Sheets through File, Import. Choose Upload, pick the file, and import it as a new spreadsheet.
The second download is a plain CSV, for a program that will not take a workbook. Its layout is simpler: 17 columns, one expiration date for the whole row and three example rows, with no formulas and no other sheets.
Check the sheet for free, or import it
The free spreadsheet check reads this layout as it stands. Choose your filled-in file there and each row is compared with a standard set of subcontractor requirements: which rows are expired, which are short on a limit, and which rest on a box that was only marked. It needs no account, and the sheet is not kept on our side. The check reads the first sheet of a workbook, so keep Tracker first.
The same file imports into WatchMyCover. Delete the example rows first. Each row becomes a subcontractor, and a row with an expiration date and at least one limit also gets a certificate record, so it is checked against your own requirements as soon as the import finishes. The help center has every column and format the importer reads.
What a spreadsheet cannot do for you
- It does not chase. Nobody is emailed when a date comes near. Someone has to open the file, read the Status column and write to the subcontractor. The request emails are there to copy when you do.
- It records what someone typed, not what the certificate says. A limit copied wrong, or a Yes entered from memory, looks the same in a cell as a right one.
- It does not compare. A row can read Current while a limit on it is under what your subcontract requires.
Related
Check every certificate against your own requirements, not a standard set.
WatchMyCover holds what your subcontracts require and measures each certificate against it, with the reason for every verdict. Or check one certificate now, free and with no account.