How do you create a training matrix in Excel?

What Excel setup do you need for a training matrix?

Start with a clean workbook and three logical areas: a People list, a Training catalogue, and the Matrix itself. The matrix is the page managers open on a Monday morning, so keep it uncluttered. Put reference data on separate tabs if the file starts to feel crowded.

On the Matrix sheet, put training topics across row 1 and people down column A from row 2. Freeze panes so the header row and name column stay fixed when you scroll. In Excel, select cell B2, then choose View → Freeze Panes → Freeze Panes. That small step makes a 40-person grid usable on a laptop.

Name your ranges early. A table called tblPeople and another called tblTraining make formulas easier to read and less likely to break when you add rows. Format the matrix as an Excel Table (Ctrl+T) so filters and structured references work consistently.

If you are building from scratch, budget 30 to 45 minutes for structure and another 30 for formulas and colours. If you want a head start, the training matrix template ships with the tabs, headers, and colour rules already wired.

Which columns should a training matrix include?

At minimum, each person-by-training intersection needs a status someone can trust. Most teams capture four data points per cell or on a linked detail row: completion date, expiry date, evidence reference, and provider or trainer name.

Across the top of the matrix, use one column per training topic, not per course instance. "Manual handling", "Fire warden", and "GDPR awareness" are topics. "Manual handling, 14 March 2026, Room 3" belongs in a training log, not as a column header.

Helpful extra columns on a detail or log sheet include:

• Validity period (for example 12 months, 36 months) so expiry calculates automatically.
• Mandatory flag so coverage counts only compulsory items.
• Role requirement so you know which topics apply to which job families.
• Last verified when competence was checked on the job, not only in a classroom.

Resist the urge to add a column for every micro-course in your LMS. A training matrix answers "can this person do the work safely and legally today?" Keep columns aligned to that question. When you need help prioritising what belongs on the grid, pair this page with how to identify training needs.

How do you colour code training status in Excel?

Colour coding turns a wall of dates into a readable heat map. The convention most operations teams recognise is green for current, amber for due within a warning window (often 30 days), and red for overdue or not started. Grey can mean "not required for this role".

Do not colour cells by hand. Manual colours drift within a week. Instead, use Conditional Formatting driven by a status formula in a helper row or hidden column. A simple pattern:

• Green: completion date exists and expiry date is more than 30 days away.
• Amber: expiry is within the next 30 days.
• Red: expiry has passed, or no completion date exists for a mandatory topic.

In Excel, select the matrix body, open Conditional Formatting → New Rule → Use a formula, and point each rule at the status value for that cell. Use the same colours your site already uses for safety or quality dashboards so supervisors do not need a legend every time.

For accessibility, never rely on colour alone. Add a text status in an adjacent helper column ("Current", "Due soon", "Overdue") so the sheet still works when printed in black and white or read by screen-reader users reviewing exported PDFs.

Which Excel formulas track training expiry?

Expiry is where spreadsheets earn their keep. Store the completion date and the validity period in months, then let Excel calculate the expiry date and status.

Assume completion date is in cell B2 and validity is 12 months in cell C2 on a detail sheet. Expiry date:

=EDATE(B2,C2)

Status formula (with today in TODAY()):

=IFS(B2="","Not started", EDATE(B2,C2)<TODAY(),"Overdue", EDATE(B2,C2)-TODAY()<=30,"Due soon", TRUE,"Current")

For the matrix view, use XLOOKUP or INDEX/MATCH to pull each person's status for each topic from the log sheet. That keeps one source of truth and avoids duplicate typing.

Add a coverage row beneath the matrix with COUNTIF to count how many people are "Current" per topic. Divide by headcount for a percentage. One red column with 40% coverage is a clearer board slide than forty individual red cells.

When validity rules differ by topic (annual fire training, three-year first aid), store the months in the Training catalogue row so you change the rule once, not in every formula copy.

How do you measure training coverage in Excel?

Coverage answers a simple question: for each mandatory topic, what share of the relevant population is current right now? Build a small summary block below the matrix or on a Dashboard tab.

For each training column, count cells marked Current and divide by the number of people who require that training. Exclude grey "not required" cells from the denominator. The formula pattern:

=COUNTIF(B2:B41,"Current")/COUNTIF(B2:B41,"<>Not required")

Plot the lowest three coverage percentages on a chart for leadership reviews. Teams that review coverage monthly close twice as many overdue items before audit season, because amber cells get booked into diaries while they are still amber.

Link low-coverage topics to action owners and target dates on the same sheet. A matrix that only shows problems without names and dates becomes wallpaper. A matrix with owners becomes a plan.

How does a training matrix differ from a skills matrix?

A training matrix records whether someone completed a course or holds a valid certificate. A skills matrix records whether they can perform the work to an agreed standard, usually scored on a 0 to 5 scale. Completion is not the same as competence, but both views belong in a well-run L&D function.

Many teams run a training matrix for compliance expiry and a skills matrix for capability gaps. When the two drift apart, you get people who are "green" on paper but cannot cover a shift. After major training, update both sheets, or use a template that links training events to skill ratings.

If your goal is capability heat maps and development plans rather than certificate dates, start with the free skills matrix and read the guides on gap analysis and calibration. If your goal is audit-ready expiry tracking, stay focused on the training matrix layout on this page.

How do you keep a training matrix up to date?

Assign one owner per site or department. Give them 30 minutes on the first Monday of each month to paste completions from the LMS, update expiry statuses, and chase amber cells. Smaller teams can review weekly in a five-minute stand-up if high-risk topics are involved.

Make updates easy at the point training happens. Trainers should hand back a one-line email: name, topic, completion date, certificate ID. The matrix owner copies that into the log sheet; formulas refresh the matrix automatically.

Version-control the file in SharePoint or Teams with a clear "master" copy. Avoid twelve emailed attachments with "final_v3_REAL" in the filename. When audits approach, export a PDF snapshot with the review date in the header.

When overdue rows pile up, feed them into a structured training needs analysis so budget follows evidence. The matrix tells you what is red; the needs analysis tells you what to book first.

Last reviewed: 6 June 2026.