# Certificate of insurance tracker in SharePoint

> For the full documentation index, see https://250highstreet.com/llms.txt

> Build a certificate of insurance tracker in a SharePoint list: one row per vendor, expiry and warning dates, a file for each certificate, and a reminder.

Short answer: make one SharePoint list with one row per vendor certificate. Add columns for the expiry date, a warning date, an owner, a Yes/No column for "renewal received" and the certificate file. Save one view that shows only rows that are past the warning date and not yet renewed. Then look at that view on a set day each week, or have a scheduled flow send it to you.

This guide is only the tracker. It does not explain what coverage a vendor should carry, and it gives no legal or insurance advice. Ask your insurance broker or a lawyer what your contracts require. Menu names in Microsoft 365 change from time to time, so the exact names may differ from the ones below.

## Why a spreadsheet of certificates slips

A certificate of insurance is a document that a vendor sends you to show a policy exists. Each one has an end date. If the list of those dates lives in a spreadsheet, three problems show up:

- The expiry date is a cell. Nothing looks at it until a person does.
- The certificate file is somewhere else, in an email or a folder, so the date and the proof are never in the same place.
- Nobody owns the chase. The row says "Vendor 12 expires in March" but not who asks Vendor 12 for the new one.

A list fixes the second and third problems right away. The first one needs a view and, if you want, a reminder.

## Signs you need a tracker

- You search your inbox to find out if a vendor sent a certificate.
- You learn a certificate ended only when someone asks for it.
- Two people each keep their own copy of the dates.
- A vendor's new certificate arrives and nobody knows which old row it replaces.

## Before you start

You need a Microsoft 365 account that can create a list on a SharePoint site or in the Lists app. For the click steps to create a blank list, see "[How to set up a vendor list with renewal dates in SharePoint](/guides/setup-vendor-list-renewal-dates-sharepoint/)". This guide picks up after the blank list exists.

One way to start is with five to ten vendors. Add the rest once the view works.

## Step 1. Plan one row per certificate

One row is one certificate for one vendor. If Vendor 12 gives you a general liability certificate and a separate auto certificate, that is two rows. This keeps each expiry date on its own row, so each one can be late on its own.

## Step 2. Add the columns

Microsoft says "You can add most types of columns without leaving your list or library." Its steps for a new column are: in the dropdown, "select the type of column you want", enter the other details, and "Select Save." The names below are examples. Use words your team already says.

1.  Title. Every list has a Title column. SharePoint makes it required by default, so either rename it to "Vendor" or turn off Require for it. If you keep it required, every row must have a value in it.
2.  Coverage type. Type: Choice. Number the choices so they keep your order: 1 General liability, 2 Workers compensation, 3 Auto, 4 Umbrella, 5 Other.
3.  Insurer. Type: Single line of text.
4.  Expiry date. Type: Date and Time. Microsoft lists Date and Time as a column type. Plain text would not sort as a date.
5.  Warning date. Type: Date and Time. You type the day you want to start chasing, for example 30 days before the expiry date. Our advice, not a Microsoft rule: type this date by hand. It keeps the list simple, and you can pick a different lead time for each vendor.
6.  Owner. Type: Person. This is the one person who asks the vendor for the new certificate.
7.  Renewal received. Type: Yes/No. Set the default value in the column settings so a new row never starts blank.
8.  Status. Type: Choice: 1 Current, 2 Requested, 3 Received, 4 Lapsed. Set a default of 1 Current. A blank status would hide the row from any view that filters on status.
9.  Notes. Type: Multiple lines of text.

You do not need all nine. If you add only four, add Vendor (the renamed Title), Expiry date, Owner and Renewal received. Those four make the list useful.

## Step 3. Store the certificate file

Microsoft says you "can also attach one or more files to a list item to provide additional details". So the simplest way to keep the proof next to the date is to attach the certificate file to its row.

Attach the new file to the same row, or add a new row, depending on how you want to see history. One way to start is a new row for each new certificate and the old row marked Status 4 Lapsed or deleted by the list owner. This is our advice, not a Microsoft rule.

If your team already keeps vendor files in a document library, another option is to paste the link to the file in a text column and keep the date in the list. Pick one place for the file and tell everyone where it is.

## Step 4. Add a "needs chasing" view

This view shows only the rows that need action: past the warning date and not yet renewed.

1.  In the list, open the view menu and choose to create a new view, or edit your current one. Name it "Needs chasing".
2.  Filter 1: Warning date is less than \[Today\]. Microsoft says "The \[Today\] keyword works on date-based columns."
3.  Filter 2: Renewal received is equal to No.
4.  Connect the filters with And.
5.  Sort by Expiry date, oldest first.

\[Today\] and \[Me\] filters need the classic view editor. Microsoft says "You must use the classic view editor to filter columns using these keywords." and "It is not possible to use the \[Today\] or \[Me\] filters when filtering using a column header." Our advice, not a Microsoft rule: set blank and "is not equal to" filters in the classic view editor too.

The Renewal received column is what makes old rows drop off. When a new certificate comes in, set Renewal received to Yes and the row leaves the view. Without that column, a row would stay in the view forever, because its warning date never moves into the future.

## Step 5. Add a second view for the next quarter

A view for the full picture helps when you plan ahead. Create a view called "All certificates" that sorts by Expiry date, soonest first, with no filter. Look at it at the start of each quarter. Group it by Coverage type if you want to see one kind at a time.

## Step 6. Test with one real row

1.  Add a row for Vendor 12 with a warning date of yesterday and Renewal received set to No.
2.  Open "Needs chasing". The row should show.
3.  Set Renewal received to Yes. Refresh the view. The row should be gone.
4.  Delete the test row if it is not real.

Do this before you enter the rest of the vendors. If the row does not appear, check that the filters are joined with And and that the date column is the Date and Time type.

## Step 7. Get a reminder (optional)

A view only helps if someone opens it. Microsoft says you can create a flow that runs "Once a day, an hour, or a minute." and "On a date that you specify." Its steps for a scheduled flow: sign in at [https://make.powerautomate.com](https://make.powerautomate.com), select Create from blank, search for Recurrence, then set the Interval and Frequency. For example, Microsoft says "to run the flow once a week, use Interval = 1 and Frequency = Week."

Then add a step that gets the rows from your list and a step that sends an email to the owner. The full build is in "[Send a reminder before a due date in a SharePoint list](/guides/send-reminder-before-due-date/)". If your list grows past a few hundred rows and the flow stops short, see "[Power Automate Get items stops at 100 rows: how to get them all](/guides/power-automate-get-items-only-returns-100/)". When you build the Get items step, set Top Count and turn Pagination on.

Make the flow ask only for rows where Renewal received is No, or it will email about rows that are done.

## What to avoid

- Do not put two certificates in one row. Each one needs its own expiry date.
- Do not use plain text for dates. Use Date and Time.
- Do not leave Owner blank. A row with no owner has no one to chase.
- Do not leave Status or Renewal received without a default value.
- Do not use a formula for the warning date. Type it, or check any formula against a calendar on at least three rows.
- Do not rely on a view to hide a certificate from people. Views are not security. If a certificate or note should be private, see "[People see only their own items in a SharePoint list](/guides/people-see-only-their-own-items-sharepoint-list/)".
- Do not store bank details or tax numbers in the notes.
- Do not enter every vendor on day one. Test with a short list first.

## Honest limits

A list holds dates and files. It does not read a certificate, and it does not know whether the coverage is right. A person must check the document. The list also does not send anything by itself unless you build the flow in Step 7. For a related look at renewal dates in contracts, see "[Contract renewal dates missed: how to track them so none slip](/guides/contract-renewal-dates-missed/)".

## When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: \$5,000 for one, built in 2 weeks. See "[What a SharePoint tracker costs to build](/guides/what-a-sharepoint-tracker-costs/)". Bring one example of the problem to a free call.

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: [Tools I built](/portfolio/)

## FAQ

### Can SharePoint warn me before a certificate expires?

A list stores the dates. A view can show rows past a warning date, and a scheduled Power Automate flow can email the owner. Both are in this guide. Exact names may differ in your tenant.

### Where should I store the certificate file?

Microsoft says you can attach files to a list item, which keeps the file next to its dates. If your team keeps vendor files in a document library, link to the file from the list instead. Pick one place.

### Why do I need a Renewal received column?

Without it, a row stays in the "needs chasing" view after the new certificate arrives, because its warning date is still in the past. Setting the column to Yes takes the row out of the view.

### Can I filter on today's date in a view?

Yes, with \[Today\], but Microsoft says you must use the classic view editor for that. A calculated column cannot use today's date, so type the warning date by hand.

### Can I use Excel instead?

For a short list with one editor, yes. A shared list is easier when several people update it. See "[SharePoint list vs Excel](/guides/sharepoint-list-vs-excel/)".

## Sources

- [Microsoft Support, Introduction to lists](https://support.microsoft.com/en-us/office/introduction-to-lists-0a1c3ace-def0-44af-b225-cfa8d92c52d7)
- [Microsoft Support, Create a column in a list or library](https://support.microsoft.com/en-us/office/create-a-column-in-a-list-or-library-2b0361ae-1bd3-41a3-8329-269e5f81cfa2)
- [Microsoft Support, Use filtering to modify a SharePoint view](https://support.microsoft.com/en-us/office/use-filtering-to-modify-a-sharepoint-view-3d8efc52-0808-4731-8f9b-3dfaeacea3d4)
- [Microsoft Learn, Run a cloud flow on a schedule in Power Automate](https://learn.microsoft.com/en-us/power-automate/run-scheduled-tasks)

## Related guides

- [Insurance policy renewals missed or found late](/guides/insurance-agency-renewal-tracker-sharepoint/)
- [A calibration log in SharePoint](/guides/calibration-log-sharepoint/)
- [How to set up a vendor list with renewal dates in SharePoint](/guides/setup-vendor-list-renewal-dates-sharepoint/)
- [Vendor W-9 and 1099 tracker in SharePoint before January](/guides/sharepoint-vendor-w9-1099-tracker/)

Source: https://250highstreet.com/guides/certificate-of-insurance-tracker-sharepoint/