Certificate tracking spreadsheet
One row per sub, with each policy's limits and dates. A limit below what you require turns red, and a policy that expires within 30 days turns amber.
Excel file · opens in Google Sheets too
Free templates
Certificate tracking spreadsheet and templates
A tracking spreadsheet, a letter that asks a sub for a certificate, a sheet of what you require and a renewal request. Free to download, with no email asked.
Request letter for subs
Asks a sub for a certificate: the limits you require, who goes in the holder box, the endorsements, and by when.
Word file
Insurance requirements sheet
One page with the coverage and limits you require, the endorsements and how to name you. Send it to every new sub.
Word file or PDF
Renewal request
A note to send a sub 30 days before a policy expires, asking for the renewed certificate with the same endorsements.
Word file
How the spreadsheet works
Each row is a sub, and row 2 holds your requirements: the limits you ask for, and Yes where you ask for an additional insured endorsement or a waiver. Every row below it is checked against row 2:
- Sub, Trade and Email: who they are and where to ask.
- GL each occurrence and GL aggregate: the general liability limits on their certificate. Each turns red when it's below row 2.
- Auto limit and Employers' liability: the same check for auto, and for the employers' liability part of workers' comp.
- GL expires, Auto expires and WC expires: each date turns amber 30 days before it passes, and red after.
- Additional insured and Waiver: Yes once the endorsement page is in your file. A No under a required Yes turns red.
- Received: the day the certificate came in. It's never flagged.
The colors count from today each time the file opens, with formulas and no macros. It's an Excel file, and Google Sheets opens it too.
An example
The file opens with 5 sample subs, one for each thing it catches:
- Lindvall Concrete has what row 2 asks for, so its row stays plain.
- Aldaco Drywall's general liability and auto policies expire in 23 days, so both dates are amber. Its Waiver is No, so that cell is red.
- Lachance Roofing carries $500,000 each occurrence and $1,000,000 aggregate, under the $1,000,000 and $2,000,000 in row 2, so both are red.
- Blue Creek Electric's employers' liability is $100,000, under $500,000, and its workers' comp expired 5 days ago. Both are red.
- Patel Plumbing hasn't sent a certificate yet, so its row has only a name, a trade and an email.
Delete the sample rows, type your own requirements into row 2, and add a row for each sub.
Where a spreadsheet stops
A spreadsheet works while you can type in each certificate yourself. It can't ask a sub for a certificate or a renewal, read one, or check the endorsement pages behind it.
A blank cell isn't flagged, so a sub who never sent a certificate looks like one you haven't typed in yet. And each row holds one certificate, so typing in a renewal replaces the dates before it. When an auditor asks for a certificate for each date a sub worked, the older ones have to be in your files.
Questions people ask
What should a certificate tracking spreadsheet include?
A row for each sub with the limits and expiration date of each policy you require, whether you have the endorsement pages, and when the certificate came in. Ours puts your requirements in row 2, so a limit that falls short turns red.
How does it warn me before a policy expires?
Each expiration date turns amber 30 days before it passes, and red after. It counts from today each time the file opens.
Can I change what it checks?
Yes. Type your own limits into row 2, and Yes or No under Additional insured and Waiver. Every row below checks against them, down to row 1,000.
Does it use macros?
No. The colors come from formulas and conditional formatting. If Excel opens the file in Protected View, choose Enable Editing so the dates count from today.
What should I ask my subs for?
What your own contracts require. The limits in the templates are samples, so change them to yours.
What are the other three templates for?
The request letter asks a new sub for a certificate before work starts, the requirements sheet lists what you require on one page, and the renewal request goes out 30 days before a policy expires.
Get in first
Coverstamp keeps track of your subs' certificates for you. It asks for each one, checks it against your requirements and follows up on what's missing. It opens in early 2027, and people on the list get in first.