COI Tracking Spreadsheet: Template and When to Outgrow It

A good COI tracking spreadsheet uses one row per policy, not one row per vendor, with columns for the insurer, policy number, effective and expiration dates, limits, whether the certificate meets your requirement, and whether endorsements were confirmed. Conditional formatting then colors each row by how close it is to expiring, and one owner reviews the sheet every week. It works well for a dozen vendors and one careful person, and starts to break down when reminders, files, and editors multiply.

Most teams start tracking vendor certificates in a spreadsheet, and that is a reasonable place to start. The trouble is that a loosely built sheet hides problems instead of showing them, so the layout and routine below matter.

This article is general information, not legal or insurance advice. Check your contracts and talk to your insurance broker or attorney about your situation.

Why start with a spreadsheet?

A spreadsheet is free, everyone knows how to use it, and you can set one up in an afternoon. If you have a dozen vendors and one careful person looking after them, it can do the job.

It is also a good way to find out what you actually need. Building the sheet forces you to decide which coverage you require, which dates matter, and who follows up. Those decisions carry over to whatever you use later. For the bigger picture of the process the sheet supports, see the guide to certificate of insurance tracking.

What columns should a COI tracking spreadsheet have?

A COI tracking spreadsheet needs the columns listed below, and the most important design choice is one row per policy, not one row per vendor. A vendor’s general liability, auto, and workers compensation policies usually come from different insurers and often expire on different dates. If you squeeze them into one row, you end up tracking only the earliest date, or worse, only the general liability date.

Here are the recommended columns, with two example rows for made-up vendors. If you’d rather not build it yourself, download the free COI tracking spreadsheet template (Excel and Google Sheets, no sign-up) with these columns, the expiry colors, and dropdowns already set up.

Vendor	Vendor contact email	Coverage type	Insurer	Policy number	Effective date	Expiration date	Each occurrence limit	Aggregate limit	Meets requirement?	Endorsements confirmed?	Certificate file	Last requested	Status	Notes
Ridgeline Electric LLC	office@ridgeline.example	General liability	Insurer A	GL-0000000	2026-03-01	2027-03-01	(per certificate)	(per certificate)	Yes	Yes, endorsement copy reviewed by broker	Ridgeline_GL_2026.pdf	2026-02-10	Current	Additional insured required by contract
Bayview Cleaning Co.	admin@bayview.example	Workers compensation	Insurer B	WC-0000000	2025-11-15	2026-11-15	Statutory	(n/a)	Yes	Waiver of subrogation: not yet confirmed	Bayview_WC_2025.pdf	2026-09-20	Renewal requested	Asked agent for endorsement copy

What each column is for:

• Vendor. The legal name, exactly as it appears in the insured section of the certificate and on the vendor’s Form W-9.
• Vendor contact email. Who you write to when a renewal is due.
• Coverage type. General liability, auto, umbrella, workers compensation, professional liability, and so on. Use a dropdown so the same type is always spelled the same way.
• Insurer and policy number. Copied from the certificate. When a renewal comes in with a different policy number, you know it is a new policy and not a resend of the old certificate.
• Effective and expiration dates. Format these as real dates, not text, or the formatting rules below will not work.
• Limits. Enter the numbers from the certificate. Compare them against what your contract requires for that vendor; the right amounts depend on the contract, the work, and your broker’s advice.
• Meets requirement? Yes or no, after comparing the certificate to the contract. This is your judgment, recorded.
• Endorsements confirmed? If the contract requires additional insured status, a waiver of subrogation, or primary and non-contributory wording, record whether someone has read the actual endorsement. A checkmark on the certificate is not enough.
• Certificate file. A file name or a link to where the PDF is stored.
• Last requested. The date you last asked the vendor for a certificate or renewal.
• Status. A short dropdown: Current, Expiring soon, Renewal requested, Expired, Missing, Replaced.
• Notes. Anything a colleague would need to know if you were out next week.

Keep a second tab with your requirements by vendor type (for example, cleaners, trades, IT consultants) so whoever fills in the “Meets requirement?” column checks against the same standard every time.

If you want a deeper explanation of what each value on the certificate means, see how to read an ACORD 25.

Conditional formatting that flags expiring certificates

A tracking sheet is only useful if problems jump out when you open it. Conditional formatting does that. In both Excel and Google Sheets, select the expiration date column and add rules based on a custom formula. Assuming expiration dates are in column G and your data starts in row 2:

• Expired (red): =$G2<TODAY()
• Expiring within 30 days (orange): =AND($G2>=TODAY(), $G2<=TODAY()+30)
• Expiring within 90 days (yellow): =AND($G2>TODAY()+30, $G2<=TODAY()+90)

Apply the rules to the whole data range (columns A to O). The $ in $G2 keeps every cell in the row looking at the same date, so the entire row changes color, which is easier to scan. Add the expired rule first, or put it at the top of the rule list, so it wins over the others.

A few other tips:

• Add a “Days left” column with =G2-TODAY() and sort by it. The most urgent rows rise to the top.
• Freeze the header row so the column names stay visible as you scroll.
• Use data validation for coverage type and status, so filters work reliably.
• Filter, don’t delete. When a policy is replaced by a renewal, mark the old row as “Replaced” and filter it out. Keep the history.

How do you run a COI spreadsheet week to week?

Run the sheet with one owner, a weekly review, and early renewal requests. The sheet does not do anything on its own, so the routine matters more than the layout:

1. Pick one owner. One person is responsible for the sheet and for follow-up. Others can view it.
2. Review weekly. Sort by days left and work through anything orange or yellow.
3. Request renewals early. For anything expiring in the next 90 days, email the vendor and log the date in “Last requested”. Our post on how to request a certificate of insurance has an email template you can copy.
4. Check before you file. When a new certificate arrives, compare it to the requirement before you mark the row “Current”.
5. Check before you pay. Before approving a vendor invoice, glance at their rows. If anything is expired, raise it before the payment goes out.

For a fuller comparison of ways to keep on top of renewals (calendars, shared drives, software), see how to track vendor document expiration dates.

Where does a COI tracking spreadsheet break down?

A spreadsheet usually breaks down at reminders, file storage, intake, shared editing, and manual typing. The spreadsheet works until it does not, and the usual breaking points are below.

Nobody gets reminded

A red row only helps if someone opens the file. When the owner is on vacation, busy with month-end, or leaves the company, the sheet goes quiet. Calendar reminders help, but they have to be created by hand for every policy, and updated every renewal.

The sheet and the files drift apart

The spreadsheet says Ridgeline’s general liability runs to March. The PDF that proves it is in someone’s inbox, or in a shared drive folder with five versions named “COI final”, “COI final 2”, and “COI new”. When you need to show the certificate quickly, the sheet is not enough.

Vendors send files every which way

One vendor emails the owner, another emails accounts payable, a third sends it to the site supervisor by text. Each certificate has to be found, saved, and typed into the sheet by hand. Some never make it.

More than one person edits it

Two people update the sheet in their own copies, or one overwrites the other’s changes. Soon nobody is sure which version is the record.

Typing everything by hand

Dates, policy numbers, and limits are copied from each certificate. With a few vendors that is fine. With dozens, it takes real time, and one typo in an expiration date means a row that should be red stays white.

Signs you have outgrown the spreadsheet

You do not need to switch the moment you add a tenth vendor. But it is time to look at a dedicated tool if:

• You have caught an expired certificate after work had already started.
• More than one person needs to update or rely on the sheet.
• You spend more time chasing and typing than reviewing.
• Your vendor count is growing, or you are adding new document types such as licenses.
• A client or auditor has asked for proof and it took you a day to find it.

Software built for this keeps the document and its data together, sends the reminders, and gives everyone the same view. In CertiTrack, vendors upload through a secure link (no account needed), the AI reads the dates, policy numbers, insurers, and limits from each certificate for you to confirm, and the vendor gets an email at 90, 60, 30, and 7 days before expiry with a link to upload the renewal (your team gets a copy). It also warns you when you mark a payment as paid to a vendor with an expired document. What it does not do is verify endorsements: that stays a human check, as it does in a spreadsheet.

If you want to try it alongside your sheet, the Free plan covers up to 5 vendors, which is enough to move your highest-risk vendors over first. See pricing for the larger plans.

Frequently asked questions

Should I use Excel or Google Sheets for COI tracking?

Either works. Google Sheets is easier when several people need access at once and keeps one shared version. Excel works well if your team already lives in it and stores the file somewhere shared, such as a single cloud folder.

How do I track certificates for vendors with several policies?

Give each policy its own row, with the vendor name repeated. Then filter by vendor when you need to see everything for one company.

Can I add licenses and other documents to the same sheet?

You can, using the same row-per-document approach with a “Document type” column. Many teams find the sheet gets crowded quickly, which is often the point where they move to a dedicated tool.

How far ahead should the spreadsheet flag an expiring certificate?

At least 30 days, and 90 days for important vendors. Renewals often need the vendor to contact their agent, and that can take time.

CertiTrack keeps every vendor’s insurance, licenses, and renewal reminders in one place. Start free.