How to Clean Up a COI Tracking Spreadsheet Before You Automate It
A practical guide for project coordinators and contractor administrators who want to improve a COI tracking spreadsheet without automating bad data, unclear statuses, and forgotten follow-ups.
At some point, nearly every COI tracking spreadsheet develops ambitions.
It starts as a reasonable list: subcontractor name, policy expiration date, maybe a status column and a note. Then someone adds conditional formatting. Someone else builds a second tab for requests. A third person connects it to SharePoint. Eventually a project coordinator is asked whether the whole thing can send reminders automatically, pull information from folders, update itself, and perhaps make coffee while it is at it.
Automation sounds like the obvious next step. Sometimes it is. But automating a COI tracker before cleaning up the underlying workflow usually makes the confusion faster rather than making the work easier.
The spreadsheet may already contain duplicate subcontractors, inconsistent company names, several meanings for “pending,” old expiration dates, missing customer context, and notes that only make sense to the person who wrote them. Once those problems are connected to formulas, scripts, notifications, or Power Automate flows, they become harder to see and more annoying to fix.
Before automating anything, take one pass through the tracker as if you had to hand it to a new project coordinator tomorrow morning. Could that person tell what needs attention, who has the next step, and whether a request has already been sent? If the answer is no, the first job is making the tracker tell a reliable story.
Start with the question the spreadsheet is supposed to answer
A COI spreadsheet can be built to answer several different questions, and trouble starts when nobody agrees which one matters most.
Accounting may want to know whether a subcontractor has a certificate on file. A project manager may care whether the certificate will remain current through the job. A contractor administrator may need to know which broker was contacted and when to follow up. A customer may have its own certificate holder wording or submission requirements. Those are related questions, but they do not fit neatly into one “Status” column.
For daily work, the tracker should at least help answer five things: which COIs are missing, expired, or approaching expiration; which customer, project, or internal request each certificate supports; whether an updated certificate has already been requested; who currently has the next step; and when somebody should follow up.
That is enough structure to make the tracker useful without turning it into a homemade insurance administration platform. The spreadsheet does not need to decide whether coverage is legally adequate. It does need to show what your team is tracking and what still requires attention.
Fix the identity problem first
Duplicate names quietly wreck more COI trackers than complicated formulas do.
One row says “ABC Electric.” Another says “ABC Electric LLC.” A SharePoint export says “A.B.C. Electrical, LLC,” and the accounting system has “ABC Electrical.” A person can usually recognize that these refer to the same company. A lookup, automation, or import process may treat them as four separate subcontractors.
Before building reminders or folder connections, choose one consistent company name for every subcontractor or vendor. Keep the legal or formal name if that is the name your team uses elsewhere. If internal systems have a vendor number, subcontract number, or other stable identifier, include it. Names change and get typed creatively. Identifiers are less entertaining.
This cleanup can feel tedious because it is tedious. It is still cheaper than sending three renewal reminders to the same broker while another subcontractor receives none.
The same rule applies to customers and projects. If the spreadsheet needs to distinguish certificates by customer, project, or contract, use consistent names or IDs there too. “City project,” “City of Aurora,” and “Aurora job” should not become three separate contexts unless they really are three separate things.
Give every status one meaning
Most long-lived spreadsheets have a status vocabulary assembled by committee, accident, and mood.
You may find entries such as Current, Good, Complete, Received, Sent, Pending, Waiting, Requested, In Review, Expired, Needs Update, N/A, and blank. Several may describe different stages. Others may mean the same thing. “Pending” could mean waiting on the subcontractor, waiting on the broker, waiting on an internal reviewer, or waiting for a customer portal to update. Once a status has four possible meanings, it is no longer helping.
Use a small set of statuses that describe the current document state. For example, a COI might be Missing, Current, Expiring Soon, Expired, or Not Applicable. Keep the request and follow-up state separate. A certificate can be Expired and also Waiting on Broker. It can be Missing with no request sent yet. It can be Current while a customer-specific replacement is still being reviewed.
Trying to squeeze all of that into one cell creates statuses like “Expired, requested 7/8, broker says tomorrow,” which is useful as a note but terrible as structured data.
A simple tracker often works better with separate columns for document status, waiting on, request date, follow-up date, and latest note. That may look like more columns, but each column has one job. Clear structure is easier to automate later because the automation does not have to guess what a sentence means.
Separate expiration from renewal work
An expiration date tells you when a policy period ends. It does not tell you whether anyone has started the renewal process.
This is one of the biggest weaknesses in date-only COI trackers. A certificate turns yellow 30 days before expiration, but the spreadsheet cannot show whether the broker has already been contacted. The project coordinator sees the warning, searches Outlook, finds an old thread, and tries to work out whether somebody else owns the follow-up. The tracker technically warned everyone. It still failed to organize the work.
Add a request date when someone asks for the renewal. Add a follow-up date for the next review. Record who has the ball. These fields let the expiration date remain what it is, while the workflow fields explain what the team is doing about it.
The follow-up date matters because repeated reminders are not always useful. If the broker replied yesterday and promised the updated certificate next Tuesday, the item may still deserve visibility, but another email today would mostly prove that your automation has no social awareness. A future follow-up date lets the team pause the chase without losing the item.
Preserve the customer or project context
A subcontractor may have one company-level COI on file and still need different certificates for different customers or projects.
The certificate holder may differ. A customer may ask for updated wording. A project may run beyond the current policy expiration. One portal may show the document as submitted while another customer still wants a copy emailed directly. A tracker that only says “ABC Electric, Current through December” can hide all of that.
Decide whether each row represents the subcontractor’s general certificate or a customer-specific obligation. If customer-specific certificates are common, include customer, project, or platform context in the tracker. Do not bury that information in the filename alone.
This is especially important when one general certificate is linked to several customer requirements. The document may be current, but one customer’s requested item may still be open. Keeping those ideas separate helps the team avoid declaring the whole job finished because one PDF arrived.
Make “who has the ball?” visible
A tracker becomes much easier to work when the next responsible party is obvious.
For COI renewals, common waiting parties include the subcontractor, insurance broker, customer, portal reviewer, and internal team. You do not need a complicated ownership system to start. A consistent “Waiting On” column is enough to tell the project coordinator whether the next move belongs inside or outside the company.
Pair that with a short note that records the latest meaningful update. A useful note might say, “Broker requested revised certificate holder wording. Follow up 7/30.” A less useful note says, “Emailed.”
The first note tells the next person what happened and when to look again. The second sends them back into Outlook to begin the investigation from scratch.
Notes should not become miniature novels. The goal is to preserve enough context for someone else to continue the work. If a longer history matters, keep the email thread or activity record somewhere accessible and use the tracker to summarize the current state.
Decide which system owns each field
Automation becomes dangerous when two systems can update the same information without a clear winner.
Suppose the expiration date is maintained in SharePoint, copied into Excel, and manually corrected in the spreadsheet. Which value is authoritative? What happens during the next refresh? Does the SharePoint value overwrite the correction, or does the spreadsheet push the change back?
Before connecting tools, assign a source of truth for each major field. The vendor system may own the formal company name and vendor number. SharePoint may own the stored file and extracted policy dates. The tracker may own waiting status, follow-up date, and operational notes. The customer portal may remain the source for its own review status.
Write those decisions down. They do not need an architecture diagram worthy of NASA. A one-page field map is enough:
| Field | Source of truth | Who updates it |
|---|---|---|
| Subcontractor name | Vendor master | Accounting or vendor admin |
| COI expiration date | Reviewed certificate record | Contractor admin |
| Customer requirement status | Internal tracker | Project coordinator |
| Waiting on | Internal tracker | Person working the item |
| Follow-up date | Internal tracker | Person sending the request |
| Portal review status | Customer portal | Manually recorded after review |
Once ownership is clear, automation can move information in one direction without quietly undoing somebody’s work.
Test the workflow without automation
After cleaning the names, statuses, dates, and ownership fields, use the revised tracker manually for a week or two.
That trial will expose problems much faster than building a flow and hoping the design was correct. You may discover that “Waiting on Subcontractor” is too broad because most requests actually go through brokers. You may find that a customer column is essential, while the project column is rarely used. You may learn that the team needs a “last request sent” date but does not care about the original request date.
Watch for the moments when someone leaves the tracker to search another system. Some of those trips are unavoidable. The spreadsheet will not replace a customer portal or the actual certificate file. Repeated searches often reveal missing context that belongs in the workflow.
Also watch for fields nobody updates. A beautifully designed column that stays blank for two weeks may not belong in the process. Automation will not make an unclear field more meaningful.
Automate the boring, stable parts
Once the workflow survives ordinary use, automation becomes much safer.
Good early automation candidates are repetitive and easy to verify. Pulling a consistent subcontractor name and ID from a vendor list may be useful. Calculating days until expiration is straightforward. Creating a filtered view of certificates expiring within 30, 60, or 90 days can save review time. Flagging follow-up dates that are due today can help the project coordinator start the morning without scanning every row.
Be more careful with actions that change workflow state or contact people. Automatically marking a certificate Current because a file appeared in a folder can be wrong if the file belongs to another customer or contains unexpected dates. Automatically emailing every broker 30 days before expiration can create duplicate requests when someone has already started the renewal manually.
A review step is usually worth keeping. Let the automation surface likely work, prepare a request, or suggest an update. Let a person confirm that the record and customer context are correct before the system changes the visible state or sends a message.
That may sound less impressive than full automation. It is also less likely to make the project coordinator apologize to six brokers on a Thursday afternoon.
Know when the spreadsheet has reached its limit
A cleaned-up spreadsheet can support a surprisingly large amount of COI work. There is no prize for replacing it early.
The limit usually appears when the team spends too much time maintaining the tracker around the work. Requests live in email, current files live in SharePoint, customer requirements live in portal notes, follow-up dates live in personal calendars, and the spreadsheet contains a summary that must be rebuilt every morning. At that point, formulas are only one part of the problem.
Sidecar is designed for that workflow layer. It can keep documents, customer-specific requested items, expiration dates, waiting states, follow-up dates, request history, replies, and activity context tied together. The spreadsheet can still be imported, and customer portals can remain where they are. Sidecar’s role is to make the current operational story easier to trust.
That does not mean it reviews insurance coverage or decides whether a subcontractor meets a customer’s requirements. It helps the contractor administrator or project coordinator see what is being tracked, what appears missing, who has the next step, and what needs attention today.
Whether you stay in Excel or move into dedicated software, the cleanup work is the same. Consistent names. Clear statuses. Separate expiration and follow-up fields. Visible customer context. One source of truth for each field. Automation works much better after those decisions have been made.
Otherwise, you are giving the spreadsheet a motor before checking whether the steering wheel is attached.
Potential Sidecar Feature
A useful import enhancement would be a pre-automation workflow audit for COI spreadsheets.
During import preview, Sidecar could identify duplicate-looking company names, ambiguous statuses, mixed customer and project values, missing follow-up fields, and rows where the expiration date exists but the renewal state is unclear. The user could review those findings before importing anything.
The feature would not determine whether a certificate is acceptable or make insurance decisions. It would help teams see where their current spreadsheet structure may create unreliable workflow data before they carry those problems into a new system.