Build an Employee Training Tracker in Excel

A training tracker in Excel shows who has completed which course, when each certificate expires, and who is overdue. You need one log, three formulas (EDATE, IF with TODAY, and COUNTIFS) and a couple of colour rules. This guide builds the whole tracker step by step on six rows of sample data, so HR staff, team leads and safety coordinators can copy the layout and use it right away.

What a training tracker needs#

A good tracker is a simple list, with one row for each employee and course. Resist the urge to build a big grid with employees down the side and courses across the top. A list is easier to sort, filter, count and extend. You can always turn it into a grid later with a pivot table.

Here are the columns, with the sample data used throughout. Statuses are shown as of 2 November 2026:

You type columns A to E. Columns F and G are formulas.

Step 1: Set up the log as a table#

Type the headers in row 1 and your data below. Then click any cell in it and press Ctrl+T (Mac: ⌘T) to turn it into an Excel table. A table copies formulas down automatically when you add a row, so new training records get an expiry date and status without extra work.

Two small habits prevent most errors later:

• Type real dates in the Completed column, such as 15/09/2025 or 2025-09-15, not text like "Sept 15th".
• Use a drop-down list for Course and Team (Data > Data Validation > List), so "Fire Safety" and "fire safety training" don't end up as two different courses.

Step 2: Calculate the expiry date with EDATE#

EDATE adds a number of whole months to a date. That is exactly what "valid for 12 months" means. In F2:

=EDATE(D2,E2)

For Ana, 15 Sep 2025 plus 12 months gives 15 Sep 2026. Format the column as a date (Ctrl+1, or ⌘1 on a Mac) if you see a number like 46280. EDATE also handles month ends sensibly: 31 January plus 1 month gives 28 February (or 29 in a leap year).

Don't use =D2+365 for a one-year course. It drifts by a day in leap years, and it can't handle 18-month or 3-year renewals as cleanly. The EDATE function guide covers the arguments, and EDATE vs EOMONTH explains when you'd want the month end instead.

Step 3: Flag the status with TODAY#

TODAY returns the current date and updates every time the file recalculates. Compare the expiry date with it to label each row. In G2:

=IF(D2="","Not done",IF(F2<TODAY(),"Expired",IF(F2-TODAY()<=30,"Due soon","OK")))

Excel checks the tests in order:

1. No completion date? The training hasn't been done yet: "Not done".
2. Expiry date before today? "Expired".
3. Expires within 30 days? "Due soon", so there's time to book a session.
4. Anything else is "OK".

On 2 November 2026, Chen's fire safety (20 Nov 2026) is 18 days away and shows "Due soon". Femi's (5 Dec 2026) is 33 days away, so it's still "OK". The first test matters: without it, an empty completion date would give an odd expiry date and a misleading status. The TODAY function guide explains how it recalculates, and the expiry date tracker guide shows a days-left column and other variations.

Step 4: Colour the rows with conditional formatting#

Colour makes the overdue rows jump out. Select A2:G7 (or the whole table) and go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Add one rule per status:

The $ before G locks the column, so every cell in a row looks at that row's status. The row number has no $, so it changes from row to row. The guide on highlighting overdue dates explains this pattern in more detail.

Step 5: Summarise with COUNTIF and COUNTIFS#

A manager usually wants numbers, not rows. Build a small summary next to the log:

To show a completion rate, divide the current records by all records for that course: =COUNTIFS(C2:C7,"Fire safety",G2:G7,"<>Expired")/COUNTIF(C2:C7,"Fire safety") gives 2 ÷ 3 = 67%. Note that "<>Expired" also counts "Not done" rows, so add another condition, G2:G7,"<>Not done", if you have any. The COUNTIFS function guide lists every criteria form.

You can also count straight from the dates without the status column: =COUNTIF(F2:F7,"<"&TODAY()) counts expiry dates before today. The exercise on this page does exactly that, with a fixed report date so the answer doesn't change.

Tips to keep the tracker reliable#

• One fixed date for reports. TODAY changes daily, so a report you send on Monday shows different numbers on Tuesday. For a monthly snapshot, put the report date in a cell and use it instead of TODAY.
• Don't type over formulas. If someone types a date into the Expires column, it stops updating. Lock the formula columns or colour their headers differently.
• Keep history. When someone renews, add a new row rather than overwriting the old date, then filter to the latest record if needed.
• Related trackers use the same pattern; see the leave tracker and the Excel skills for HR jobs.

Practise it#

The tracker rests on three skills you can practise separately. Practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet to work out renewal dates, then count by status and team in the COUNTIFS and SUMIFS lesson. Finish with the conditional formatting lesson to colour rows by rule. Each answer is checked instantly, so start with the exercise on this page.