COI Tracking Spreadsheet Template

What this template is

People looking for a COI tracking spreadsheet, an Excel template, or a certificate of insurance tracking template need a place to see who was asked, what came back, when each required policy ends, and what happens next. A single “expiration” column and a folder of PDFs named certificate.pdf do not do that.

Use this with how to read a certificate so the cells match the fields on the form, and with what COI tracking is for the status words. Vendor requests are on the vendor COI guide. Project subcontractors are on the subcontractor guide. The sheet does not decide whether a limit satisfies a contract. Your contract has the numbers. This page will not invent them, and it will not print a sample policy number. Sample numbers get pasted into live trackers.

One row means one requirement

Make one tracking row per vendor (or subcontractor) per requirement set. The same roofer on two buildings with two different limits is two rows, or one vendor record with two requirement blocks. It is not one row that pretends the stricter job and the lighter job are the same certificate.

Put requirement sets on a second tab, not in people’s heads. Each requirement set needs a short name you can type on the tracking row (“Building A vendors”, “Project 14 subcontract”), the coverage lines that set requires, and whether an endorsement is required or a certificate remark is enough. The limit figures stay in the contract exhibit. The requirement tab points at that exhibit. It does not retype limits from memory.

Recommended columns

Identity, request, and decision columns apply once per row. Coverage columns repeat. Leave a cell blank when you do not know. Blank is a better record than a guess.

Column	What to enter	Why it is there
Vendor legal name	The contracted party’s legal name.	This is who you compare to the named insured. A DBA alone will not match.
Name printed on the certificate	The named insured exactly as printed, if it differs.	A parent, a new entity after a sale, or a trade name is a mismatch until someone accepts it.
Site, project, or contract	The building, job, or agreement this row serves.	Stops one certificate from being treated as proof for every site.
Requirement set	The name from your requirement tab.	Tells the reviewer which lines and which exhibit apply.
Certificate holder expected	The legal name your request asked for.	You compare it to the holder box. See the holder field.
Vendor contact	A live person and email or phone.	The chase dies when the only address is a former employee.
Producer contact	The agency or broker on the certificate, if shown.	They usually issue the correction. They are not the insurer.
Date requested	When you sent the request.	Shows how long a row has been waiting.
Who was asked	The person you wrote to.	So the next reminder does not go to a dead mailbox by habit.
Date received	When this version arrived.	Separates “they promised” from “a file exists.”
File pointer	A path or link to this exact PDF, with a name that includes the vendor and the date.	The cell is not the certificate. Do not overwrite the only copy when a renewal arrives.
Reviewer and review date	The person who compared the file to the requirement, and the day they did it.	A status with no name is not a review.
Status	One of: not requested, requested, received, exception, accepted, expired, waived in writing, inactive.	Use words. Color alone disappears in a printout and means nothing to the next person.
Next action and next action date	The next thing someone will do, and when.	A row without a next action sits until an accident or an audit.
Exception owner and end date	Who accepted a gap, and when that acceptance ends.	An exception with no owner becomes permanent.
Work start	The day the vendor or sub is allowed to begin, if you know it.	A certificate whose effective date is after this day does not cover the start. Unknown stays blank.
Inactive	Yes when the contract for this row is over.	Stops renewal mail to closed work.

Repeat a block for each coverage line

Add a block only for a line your requirement set actually names. Common blocks are commercial general liability, automobile liability, workers’ compensation, umbrella or excess, and professional liability when the work is advisory. Do not add a block because another vendor’s certificate happened to show it.

Column, repeated per line	What to enter
Required	Yes or no, from the requirement set. Not from the certificate.
Insurer as printed	The company tied to that line. On many ACORD 25 certificates that is the insurer letter plus the company name. Copy what is printed.
Policy number as printed	The characters in the policy number field. Do not “clean them up,” and do not borrow an example from a website.
Effective date	The start date for that line.
Expiration date	The end date for that line. Lines often end on different days.
Limits as printed	The labels and figures on that line, as text. A general-liability line has more than one limit. Do not collapse them into a single number you remember.
Meets the contract	Yes, no, or unknown, after you compare the printed figures to the exhibit. Unknown is allowed. This column is a person’s decision.
Endorsement	Not required, on file, missing, or unknown. A remark on the certificate is not automatically “on file.”

Formulas that do not hide a blank

The useful calculations are the earliest required end date, whether any required date is missing, and how many days remain. A suggested status can point at the row. It must not overwrite the reviewer’s status.

Spreadsheet MIN skips blank cells. If general liability has a date and automobile is required but blank, MIN returns the liability date and the missing automobile line disappears. That is the main formula bug in homemade COI trackers. Treat “required and blank” as its own flag. Do not let the minimum date answer that question.

Result	Rule	A careful expression
One line’s days	If that line is required and the expiration cell is empty, the result is missing. Otherwise subtract today from the date.	`=IF(B2="", "Missing date", B2-TODAY())`
Any required date missing	True if any required line has an empty expiration. Check the required flag, not every column on the row.	Count the required lines whose expiration cell is blank. Any count above zero means the row is incomplete.
Earliest required end date	The minimum expiration among required lines that actually have a date.	`MIN` of those date cells only. Keep the missing-date flag beside it so a blank cannot hide inside the minimum.
Days remaining	Earliest required end date minus today, and only when that date exists and no required date is missing.	`=earliest-TODAY()` once those two conditions are true. A positive number is not a review.
Suggested bucket	Missing, if any required date is blank. Expired, if the earliest date is before today. Renew soon, if the earliest date is within 30 days. Otherwise the file can be on hand and still waiting for a person.	Thirty days is an operating habit, not a notice period required by law. Change the number to match how fast your producers answer. Do not write this bucket into the Status column automatically.

In Google Sheets, TODAY() behaves the same way for this purpose. Keep Status as a column a person edits. The suggested bucket can sit in the next column so a filter can show “formula says expired, status still says accepted.” That mismatch is the list you work first.

Filter the working view to rows that are not inactive, then sort by the missing-date flag, then by days remaining. A calendar invite that is not tied to these cells will nag a vendor who already sent a renewal, or it will stay silent when the date cell is still empty.

How to use the sheet

Write the requirement sets before you add vendors. If you do not know which lines a contract requires, the sheet cannot tell you.
Add a row when the contract is signed or the subcontractor is awarded, not when a PDF happens to arrive.
Send the request. Fill date requested and who was asked the same day.
When a file arrives, save it under a name that includes the vendor and the received date. Put that pointer in the row. Read the certificate with the field guide and type what is printed.
Set Status only after the comparison. Received means the file arrived. Accepted means a named reviewer said it meets the requirement set.
On a renewal, add the new dates and a new file pointer. Do not delete the old pointer. If your grid only holds one pointer, keep a notes cell with the prior file name, or keep prior PDFs in a folder the pointer used to reference.
Review on a cadence. Weekly fits subcontractors who turn over with the job. Monthly can fit a stable vendor list whose policies renew once a year. Skipping the review is how the formulas go stale.
When the contract ends, mark the row inactive and clear the next action so you stop chasing.

Hard limits of a spreadsheet

The file is not in the cell. A link rots when someone renames the PDF or emails a newer one that never gets saved.
Two editors, one truth. Two people can “fix” the same expiration and save over each other. The sheet will not tell you whose number matched the PDF.
The formula is only as honest as the required flags. If automobile is required and nobody marked it required, the missing-date flag stays quiet.
There is no permission model. Anyone with the file can change Accepted to make a report look finished.
History is whoever typed last. Unless you keep prior files and prior pointers, you cannot show an owner what you accepted in March after November’s renewal.
Reminders are separate. The sheet does not email the producer. A person, or another tool, still has to send the request and clear it when the new certificate is reviewed.
It does not read the PDF. Hand entry from a bad scan will be wrong with perfect formulas around it.
It does not know your contract. “Meets the contract” is a person’s column. Conditional formatting is not a coverage opinion.

These are the same failure modes as a casual tracker, described so you can build the less careless version. A longer account of why informal sheets fail is in why spreadsheets fail.

When to leave the sheet

Stay on the sheet while one person can see every open row, there is one requirement set or a very small number of them, and a missed week would not put an active vendor or subcontractor on site without a current review. Move the work into software or a tracking service when more than one person must update the record, when required lines expire on different dates across a list you can no longer scan, or when subcontractors turn over faster than a shared file can be trusted. There is no vendor count on this page at which a spreadsheet becomes wrong. That kind of cutoff is a sales claim. We are not supplying one.

If you price a tool, use how to shortlist COI tracking software and pricing questions. Ask whether the product can store the columns above, especially separate dates per line, the file you relied on after a renewal, and a status a person sets. COI Tracker does not sell that product.

Corrections: info@coitracker.com.