ZorantaGuides
CertAlert

CertAlert › Guides

Certification Tracking Spreadsheet: Columns and Formulas

A certification tracking spreadsheet needs one row per credential and, at minimum, five columns: the credential name, who issued it, the expiration date, the number of days left and the date you plan to start renewing. Add a formula for days left and a color rule, sort by expiration date, and the next deadline sits at the top every time you open the file.

Below is a layout you can copy into Excel or Google Sheets, the formulas that make it work, and the habit that keeps it from going stale.

A document wallet with ID cards, a passport and certificates beside a phone

A certification tracking spreadsheet layout

Put each license, certificate, card or permit on its own row. If a credential has a separate card (for example a CPR card and the instructor certificate behind it), give each one a row, because they can expire on different dates.

ColumnWhat goes in itExample
CredentialThe name as it appears on the cardBLS Provider
IssuerBoard, agency or training bodyAmerican Heart Association
ID numberLicense or certificate numberBLS-48213
IssuedIssue date, entered as a real date03/14
ExpiresExpiration date, entered as a real date03/31
Days leftFormula (see below)41
Start renewal byExpires minus your lead time01/30
Renewal needsCourse, exam, CE hours, fee, formSkills check + online module
Renewal costFees you expect to pay65.00
Where the file isFolder or photo of the cardDrive / Certs / BLS

The dates in the example column are shown without the year to keep the table short. In your sheet, enter full dates so the formulas can work with them.

CertAlert

CertAlert keeps the same list on your phone: each credential's expiration date, a reminder set days before it, and a dashboard colored by how soon each one expires. Get it on Google Play

Formulas for days left and renewal start

Both Excel and Google Sheets treat dates as numbers, so subtracting today's date from the expiration date gives the days left. If the expiration date is in column E:

Format the Days left column as a plain number, not a date, or the sheet will show a strange date instead of a count. If a date was typed as text (for example "March 31" with no year), the formula returns an error. Re-enter it with the date picker or in your regional date format.

Color rules

Add conditional formatting to the Days left column so urgency shows at a glance:

  1. Select the Days left column.
  2. Add a rule: less than 0, red fill.
  3. Add a rule: between 0 and 30, orange fill.
  4. Add a rule: between 31 and 90, blue or yellow fill.
  5. Sort the whole sheet by the Expires column, earliest first.

How to keep track of certifications without missing one

A spreadsheet only tells you something when you open it. The weak point is rarely the formula; it is the month nobody opened the file. Three habits close that gap.

Pick a lead time per credential, not one for all

A first aid card may need a single class. A professional license can need continuing education, a background check and board processing time. Set the Start renewal by date from what the renewal actually involves, and check the issuer's site for its renewal window, since some boards only open renewal a set time before expiry.

Review on a fixed day each month

Choose one date, such as the first working day of the month, and put a repeating calendar event on it. Open the sheet, look at anything under 90 days, and do one concrete thing for each: book the class, order the transcript, pay the fee.

Update the row the day you renew

When the new card arrives, change the expiration date, attach a photo or scan of the new card, and note what you paid. An old date left in the sheet is how a renewed credential later looks expired, and how an expired one looks fine.

When a certification tracker template stops being enough

A template works well for a handful of credentials checked by one person. It gets harder when:

At that point some people add calendar events for every Start renewal by date, and others move the list into a tool built for expiration reminders. Either way, keep the same fields: name, issuer, expiration date, what renewal needs and where the proof is.

CertAlert

Let the reminder find you

Add a credential to CertAlert with a name and expiration date, and it schedules a reminder ahead of the deadline. When you record a renewal, it suggests the next expiry date and moves the reminder.

Get it on Google Play

iPhone version coming soon

More guides

All guides

Published by Zoranta, the developer of CertAlert.