Keep one register sheet with a row per delegate, build a single certificate template with merge fields for name, course, date, expiry and number, and generate each batch from the sheet. A second sheet column calculates the renewal date, and AI drafts three reminder emails. Check your awarding body's rules first; they decide what the certificate must say.
That is the whole loop, and most first aid trainers already do each step by hand: copy names from a booking email, paste them into a Word file, save as PDF, attach, send, and hope someone remembers to chase the renewals in three years. Set up once, the same steps become a routine of about ten minutes per course. The tool does the typing, a person checks the output, and AI is used where it is strong: the wording.
A small trainer's week, before and after
To make this concrete, take a made-up first aid training company with two trainers running about eight courses a month for local employers and open bookings. Each course has up to 12 delegates. Today, after each course, the admin person spends about 40 minutes: typing names into a certificate document, correcting the course title, saving PDFs with sensible names, emailing each delegate and the employer, and updating a spreadsheet that nobody trusts. Renewals are chased when someone remembers, which is when a client rings to say the certificates have lapsed.
With the loop below, the same course takes about ten minutes: tick attendance, run the merge, scan a sample, send. Renewal emails go out on dates calculated from the sheet. Over eight courses a month, that is roughly four hours saved, and more importantly renewals stop depending on memory. All times are illustrative; time your own process before and after.
Check the awarding body's rules before you design anything
Certificates are only worth something if they meet the rules of whoever awards or regulates the qualification. Those rules may specify what the certificate must contain, how long it is valid, what a lapse means, whether training records must be kept and for how long, and which wording or logos you may use. They differ by course type, so read the current handbook or guidance from the awarding body for each course you run, and note the date you read it.
Make a short rules sheet with one row per course: course title exactly as the awarding body writes it, validity period, required certificate fields, record-keeping period, renewal or requalification rule, and the link to the page you read. It is the single source for your template and your reminder dates. If you cannot find a rule, ask the body in writing and keep the reply. Do not let an AI tool tell you the rule: it may sound sure and be wrong, which is the sort of error that gets a business into trouble.
Validity periods are not universal. They differ by course, by awarding body and over time, and a refresher or requalification rule may sit on top, so confirm each one on the body's own current page before you put a number in a formula. The column that holds the validity period in your register should pull from the rules sheet, so a rule change is made in one place.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowThe booking sheet that everything else hangs on
One register, one row per delegate per course, built in a spreadsheet you already have. Suggested columns:
| Column | Example | Notes |
|---|---|---|
| Delegate ID | D-2051 | Stays with the person across courses |
| Full name | Taken from booking | Confirm spelling on the day; this is what prints |
| Delegate's own where possible | Plus employer contact in a separate column | |
| Employer | Company or "self-booked" | For group reports |
| Course code | FA-1DAY | Matches the rules sheet |
| Course date | 12 March 2027 | Date format fixed across the sheet |
| Trainer | Name as on the awarding body's approval | Printed if your awarding body requires it |
| Attendance | Full / partial / no show | Only "full" goes to merge |
| Assessment result | Pass / not yet competent | Only "pass" goes to merge |
| Certificate number | FA-2027-0143 | Generated by a formula, never typed |
| Expiry date | Date plus validity from rules sheet | Formula |
| Reminder dates | Expiry minus 90, 30 and 14 days | Formulas |
| Status | Issued / reminded / renewed | Updated as the loop runs |
Three details save most of the pain. First, make the certificate number a formula such as the prefix, the year and a running count, so no number is ever duplicated or skipped. Second, make the expiry a formula from the course date and the validity period on the rules sheet. Third, validate the date columns so nobody can type "next Tuesday". A register with clean columns also feeds the corporate client reports described in AI for corporate training companies.
Data protection matters even here. The register holds names, contact details and training records of identifiable people. Limit who can open it, keep it in an account with two-step sign-in, and agree how long records are kept with reference to the awarding body's record-keeping rule. Do not paste the whole register into a chat tool; the AI work below uses invented sample rows or a single de-identified row.
One certificate template with merge fields
A certificate template is a page with fixed design and variable fields. Build it once in a word processor or presentation tool you can merge into, and mark each variable with a field name. The required fields vary by awarding body, but a typical set looks like this:
CERTIFICATE OF ATTENDANCE AND ASSESSMENT
This is to certify that
«Full_name»
has completed and passed
«Course_title»
Date of training: «Course_date»
Valid until: «Expiry_date»
Certificate number: «Certificate_number»
Trainer: «Trainer_name»
Training provider: [Company name], [approval or centre number]Design points that matter in practice: use one clear typeface; leave room for long names (test with the longest name you have ever had); keep the awarding body's logo and wording only as it permits; and embed the certificate number as plain text so it can be searched. Put the version date of the template in the footer in small type, so when the rules change you can tell which certificates were issued under which template.
Save a PDF of every issued certificate with a file name built from the number and the delegate's name, such as FA-2027-0143_Jordan-Smith.pdf. That file name is also generated by the merge, never typed.
Running the merge, with a pre-flight check
The mechanics differ by software, but the principle is the same everywhere: a filtered list of rows goes in, one file per row comes out. Use a filter so that only rows with attendance "full" and result "pass" are included. A delegate who attended partly or has not yet been assessed as competent must never receive a certificate by accident.
Before each run, do a pre-flight check. It takes three minutes and catches nearly every error:
- Sort the sheet by surname and read the names aloud against the attendance sheet from the day.
- Check that no certificate number is blank or duplicated (a duplicate count formula does this).
- Confirm the expiry date on the first and last rows by hand.
- Confirm the trainer name matches the approval.
- Run the merge for two rows only, open both files and look.
- Run the rest, then open one file in the middle of the batch.
A realistic mistake shows why the sample steps matter. A trainer merged a batch where a delegate's name had been entered in capitals and a double-barrelled surname had lost its hyphen. The certificates went out. Two weeks later the delegate's employer rejected one as not matching the person's ID, and the reprint cost a morning. A read-aloud pass against the day's sign-in sheet would have caught it.
If the certificates go out by email, the same mail-merge idea sends them one per delegate with the right attachment. For the sending side, including how to avoid spam filters, see mail merge with Power Automate. Always send a test to yourself first, and attach only the delegate's own certificate.
Where AI helps: the emails around the certificate
AI earns its place in the wording, not the data. Three messages around each course are worth drafting well, once, and reusing.
The certificate cover email
Ask for a short, friendly email that tells the delegate what is attached, when it expires, and what to do next. Prompt:
Write a short email (under 120 words) from a first aid training company to a delegate
who has just passed a one-day course. Use plain English, a warm and professional tone.
Include merge placeholders: [first name], [course title], [expiry date].
Say their certificate is attached, that it is valid until the date shown, and that we
will send a reminder before it runs out. Do not promise anything about legal
requirements. End with the company name placeholder [company].A realistic result from a chat tool is a neat paragraph, though you will often need to fix two things: it may add a line such as "this certificate meets all legal requirements", which you must delete, and it may use a different spelling or a hyphenated course title that differs from the awarding body's wording. Edit to match the rules sheet.
The employer summary
For group bookings, the employer wants a list of who passed, when each certificate expires, and who needs to retake. Build this from the register with a filter, then ask the AI only to tidy the covering note, using a made-up table rather than the real one.
Not-yet-competent follow-up
The sensitive message is the one to a delegate who did not pass. Draft it with AI, then have the trainer edit it: kind, specific about what to practise, and clear on rebooking terms. Never send an AI draft of this message unedited.
The renewal reminder sequence
Renewals are where a first aid company makes repeat bookings, and where manual systems fail. The sequence below is a sensible starting point; change the timing to suit the rules for each course.
| Touch | When | Purpose | Sent to |
|---|---|---|---|
| 1 | 90 days before expiry | Heads-up, offer dates | Delegate, employer in copy where agreed |
| 2 | 30 days before expiry | Show next available courses, how to book | Delegate |
| 3 | 14 days before expiry | Plain reminder of the date | Delegate and employer |
| 4 | After expiry, once | Say it has lapsed and what the rule is for rebooking | Delegate |
Here is a filled-in version of touch 1, as a draft you might generate and then edit. It is an illustrative sample, with "[first name]" standing for the merge field:
Subject: Your first aid certificate expires on [expiry date]
Hello [first name],
Your first aid certificate from [company] is valid until [expiry date], which is about three months away.
If you would like to renew, we are running refresher courses on [date 1] and [date 2]. You can book here: [booking link].
If you have already booked elsewhere, or no longer need the certificate, you can ignore this message or reply and we will stop the reminders.
[your name]The final line matters: an easy way out is courteous and, in many places, a legal requirement for marketing-style messages. Reminders that are closely tied to a service you provided are often treated differently from marketing, but rules vary, so ask your data protection adviser and put the opt-out in anyway.
Each reminder date should be a column in the register, so a daily check can list the rows that are due. A scheduled sheet filter, or a simple automation that reads the "reminder due today" rows and sends the right template, does the job. If the automation is new to you, start with a weekly manual run: filter the sheet every Monday, merge, send, and mark the status column. Move to a scheduled one when the manual run has been dull and error-free for a month. The general pattern is described in building a compliance calendar, and a spreadsheet build for staff expiry is in expiry date tracking with AI.
Using AI to audit the register
A second good use is a monthly health check. Copy a de-identified extract (case codes in place of names, no email addresses) into a chat tool on a business plan and ask it to look for problems. A prompt such as the following works:
Here is a table with columns: Delegate ID, Course code, Course date, Expiry date, Certificate number, Status.
Find rows where: the expiry date is not the course date plus the validity period for the course code;
a certificate number is duplicated or out of sequence; the status is "Issued" but the course date is
more than 120 days ago and no reminder is logged. List the Delegate IDs and the reason for each.The tool will usually find the obvious gaps. Do not trust it to be complete: a spreadsheet formula or filter finds duplicates more reliably than a language model does, so use AI to suggest the checks and formulas, then build them as permanent formulas. The model is a good colleague for thinking up what could go wrong; the sheet is a better colleague for checking it every month.
One course walked through, with numbers
Take a made-up Friday course on 12 March 2027 with 11 delegates: nine from one employer and two self-booked. The trainer collects a paper sign-in sheet at the start and notes assessment results at the end, with nine passes and two "not yet competent" who will return for reassessment.
On Monday morning the admin person opens the register. The nine passes are marked "full" and "pass"; the two others are marked "pass pending". The pre-flight read-aloud takes four minutes and finds one problem: a delegate's surname is spelt with an extra letter on the booking sheet compared with the sign-in sheet, where the delegate wrote it themselves. The admin corrects the register to match the sign-in sheet. The certificate numbers FA-2027-0143 to FA-2027-0151 are generated by formula, the expiry dates read 12 March 2030 (the date plus three years, using the validity on the rules sheet for this illustration), and the three reminder dates fill in on their own.
The merge produces nine PDFs in about a minute. The admin opens the first, the fifth and the last, then sends emails: nine certificate covers to delegates, and one summary to the employer listing seven names and expiry dates for the ones that passed and the two who will return. Total time: 14 minutes including the correction. The same day, the two "pass pending" rows get a different, human-written message from the trainer. Three weeks later they pass, certificates FA-2027-0152 and FA-2027-0153 are issued the same way, and the employer receives an updated list.
What a renewal reminder is worth
A quick sum shows why this loop is more than tidiness. Say the company has 300 delegates whose certificates expire in the next twelve months, and a typical renewal course earns $90 a delegate. If the old, memory-based process brings back 20 per cent of them, that is 60 renewals and $5,400. If a three-touch sequence lifts the rate to 30 per cent, which is a guess you should test rather than rely on, that is 90 renewals and $8,100, a difference of $2,700 for a few hours of set-up. Treat the percentages as placeholders: track your own renewals within 30 days of each reminder for a few months to find the real figures.
The more reliable gain is that clients stop finding out about lapses from an inspection or an insurer. Employers who book renewals for a whole team value the summary email, and a short line at the end such as "we can also hold a date for your whole team" often earns a booking from a message you were sending anyway.
Two ways this goes wrong that are easy to miss
The first is template drift. Someone edits the certificate file directly to fix one name, saves over the template, and every later batch carries the one-off change. Protect the template: keep it read-only, have a separate working copy for experiments, and make the merge always point at the protected one. The second is silent date errors. A spreadsheet that reads dates as text can calculate an expiry in the wrong year without any visible warning. After any change to the sheet, test the formulas with a row dated at the end of a month and one at the end of a year, and check the expiry by hand.
Duplicates, corrections and re-issues
Three situations come up in any training business and each needs a written routine.
- A name was misspelt. Correct it in the register, re-issue with the same certificate number marked "re-issued", log the date and reason, and ask the delegate to destroy the old copy.
- A delegate lost the certificate. Confirm identity from the booking record, send a copy marked as a duplicate, and log it.
- An employer asks you to confirm a certificate is genuine. Answer from the register: name, course, date, expiry, number. Share nothing else about the person without their permission.
Write these as three lines in the admin handbook so any team member can follow them. It is worth adding a QR code or a short URL on the template that leads to a simple verification page only if you can run it securely; many small companies do without, and answer verification requests by email from the register.
How to tell the loop is working
After a month, run these checks. Every certificate issued has a PDF stored under its number. No certificate number appears twice. Every delegate whose reminder fell due that month has a status showing they were reminded. The time from course end to certificate sent is under one working day. And there are fewer client calls asking "do you have my certificate?". Count renewals booked within 30 days of a reminder, and compare to the months before; that single number tells you whether the reminders earn their keep.
If the numbers look good, extend the idea: send a short feedback form with the certificate email and have AI summarise the answers, as covered in summarising training feedback forms for a client report. Keep each new automation separate until it has run cleanly for a month. And when you are choosing what to automate next across the whole business, the AI hub lists the rest of the series.
Questions trainers ask about certificates and reminders
Can AI fill in the certificates for me?
AI is good at drafting the wording and checking the sheet for gaps, but the certificate data should flow from your register by mail merge, not be retyped by a chat tool. A typo in a name or an expiry date is a defect in a document someone may show an employer, so the merge, not the AI, produces the final file.
How far ahead should a renewal reminder go out?
A workable pattern is three touches: about three months before expiry, one month before and a final note two weeks before. Your awarding body or regulator may set when a certificate can be renewed or what lapse rules apply, so check their current guidance and build the dates around that.
Do I need the delegate's consent to send reminders?
You hold their contact details to deliver a course, and a renewal reminder is closely related, but rules differ and some require a clear opt-out. Put a line on the booking form explaining that reminders will be sent, add an unsubscribe route to every email, and ask your data protection adviser to confirm your basis.
What if a delegate loses their certificate?
Keep a master register with certificate numbers and a stored copy of each issued file. Re-issue only after checking identity against the booking record, mark the new copy as a duplicate with the original number, and log the date. Never edit the register to cover a gap.
Further reads
- Best AI for Business Training: Onboarding, Compliance or Skills Gaps? — How corporate clients choose compliance training.
- How to Build a Staff Training Matrix With AI — The client-side view: who needs which training and when.
- How to Track Contract Renewals and Deadlines With AI — A renewal-tracking pattern for contracts.
- AI for Sports Clubs: Fixtures, Renewals and Other Committee Admin — Another renewals routine, run by volunteers.
- Forest School Risk Assessment: Site Checks and Session Plans With AI — Outdoor session planning that relies on first aid cover.
- Tutoring Centre Admin With AI: Enquiries, Timetables, Invoices — Bookings and invoices for another course business.
- Apprentice Off-the-Job Training Log: What to Record and How AI Helps — An employer-side training log: the fields, a filled-in week, sign-offs, a month-end check against the provider's records, and where AI helps.
- Training Course Cancellation Policy: Terms Drafted With AI — How a training company drafts cancellation terms with AI: notice tiers, substitutes, venue costs, postponement and refund wording for review.
- How to Start a Training Company: Accreditation, Insurance and AI — First-year costs, how to pick an accreditation route, which insurance to price, and the proposal, booking and certificate admin AI can handle.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: no vendor or awarding-body figures are stated; validity periods, certificate fields and record-keeping rules must be read on each awarding body's own current pages before use.