COI Tracking Spreadsheet (Excel)
Track every vendor and subcontractor certificate of insurance in one workbook. Enter the limits a certificate shows, and the sheet compares them to the minimums you set by vendor tier, flags gaps, counts down days to expiration, and rolls everything up on a dashboard.
Excel
Who it is for
Risk managers, property managers, general contractors, HOA and condo boards, franchise operators, and anyone who collects certificates from vendors and gets asked by auditors or counsel whether they are compliant.
What is inside
- Vendors sheet with 30+ columns: contract, trade, carrier, policy numbers, effective and expiration dates, GL occurrence and aggregate, auto CSL, umbrella, WC/EL, additional insured with form number, waiver of subrogation, primary and noncontributory, notice of cancellation days
- Requirements sheet with default minimums by vendor tier that drive every comparison
- Status, days-to-expiration, and gap formulas (provided vs required) on every row
- Conditional formatting that turns rows amber at 60 days and red at 30 days or when non-compliant
- Dashboard with COUNTIFS for expiring in 30/60/90 days and non-compliant count
- Dropdowns for tier, Y/N fields, and status so data stays clean
- Notes sheet documenting every assumption and a legend row naming the cells you fill
AI prompt pack: fill this template with ChatGPT or Claude
Paste the prompt below into ChatGPT or Claude together with your own documents (declarations pages, certificates, P&L, contracts). It is written to return results in this template's column order.
Check anything an AI tool returns against the source document before you rely on it. The prompt is a starting point, not a substitute for reading the policy.
Questions
Does a certificate of insurance prove coverage?
No. An ACORD 25 certificate is issued for information only and confers no rights on the holder. It is evidence that a policy existed on the date it was issued, not that it still does or that it covers your claim. For rights you need to be an additional insured by endorsement, which is why the tracker records the endorsement form number and not just a Y/N.
What should I do when a certificate shows lower limits than the contract requires?
Flag it, notify the vendor in writing, and decide whether to withhold payment, require an excess purchase, or accept the deviation with sign-off. The tracker's gap flags compare each limit to the minimums on the Requirements sheet so the shortfall is visible, and the Status column carries the outcome.
How far ahead should I chase renewals?
Thirty days is the minimum because many policies renew on the expiration date and certificates arrive a week or more after binding. The Dashboard counts expirations in 30, 60, and 90 days so you can batch requests.
Can I change the required minimums?
Yes. The Requirements sheet holds the minimums for each tier, and every comparison on the Vendors sheet reads from there. Change one cell and every vendor in that tier is re-evaluated.
Does the tracker work in Google Sheets?
The formulas (COUNTIFS, IF, TODAY, dropdowns) all work in Google Sheets. Conditional formatting rules import correctly in current versions; check the colors after uploading.
Related templates
Vendor Insurance Requirements
An insurance exhibit you attach to vendor and subcontractor agreements. It sets required coverages and minimum limits for three vendor tiers, spells out...
Business Income Worksheet
Estimate how much business income coverage you actually need. The workbook follows the ISO CP 15 15 worksheet structure, calculates net income plus continuing...
Renewal Checklist
Enter your renewal date once and every task gets a due date. The timeline covers what to do at 120, 90, 60, 30, and 0 days: exposure updates, loss runs,...
Free insurance tools
Use the letter generators in your browser, or download the free home inventory and proof-of-loss guide.