Training Matrix Excel - Free Template

1. Open the Instructions tab first to review the workbook layout, input cells, status choices, formula columns, and prepared record ranges.
2. On the Training Matrix tab, replace the sample employee records or enter employees in the pale yellow input cells from columns A through L and column R.
3. Choose Completed, In Progress, Not Started, Expired, or Not Required for each applicable course in columns H through L.
4. Leave columns M through Q intact. Their formulas calculate course counts, completion percentage, overall status, and next training due.
5. Update the Course Catalog with course names, categories, required status, renewal periods, delivery methods, providers, standard costs, and active status.
6. Review the Dashboard for total employees, average completion, fully complete employees, employees requiring action, department results, and course totals.

Human resources coordinators, operations managers, safety administrators, and department supervisors use a training matrix when they need a current view of employee readiness. A warehouse with 20 employees may need to track Safety Orientation, OSHA/Safety Refresher, First Aid/CPR, Data Security, and Harassment Prevention without maintaining five separate lists.

Onboarding And Recurring Training

The Training Matrix tab combines employee profile fields in columns A through G with course status fields in H through L. A new hire can be entered with an employee ID, job title, department, location, manager, and hire date, then assigned statuses for the five visible courses. The sample records show how a completed onboarding record differs from an employee with an In Progress module or an Expired course.

Department Reviews

Supervisors can use the Dashboard during a weekly HR review or monthly operations meeting. It counts employees, calculates average completion, identifies fully complete employees, and counts employees requiring action. Department summaries cover Administration, Compliance, Customer Service, Finance, Human Resources, Information Technology, Operations, and Sales.

The workbook is prepared for up to 100 employee records and 100 course records. That makes it suitable for a small business, local nonprofit, or individual site before a larger learning-management system becomes necessary.

The workbook uses visible Excel formulas rather than requiring manual percentage calculations. For each employee, the Course Completion Count uses COUNTIF to count Completed entries across the five course columns. Required Course Count counts every course status except Not Required, so a course that does not apply does not reduce the employee's completion percentage.

Completion And Overall Status

Completion % divides completed courses by required courses and uses IFERROR to avoid a division error when no required course is recorded. Overall Status returns Complete when the percentage reaches 100%; otherwise, Expired produces Action Required, In Progress produces In Progress, and remaining records show Not Started.

Course Catalog Controls

The Course Catalog provides Yes or No choices for Required? and Active?, plus eLearning, Instructor-Led, Blended, or Workshop for Delivery Method. Its Cost Lookup column uses VLOOKUP against the Course ID and catalog range. For example, the populated catalog shows Workplace Safety at $125.00, Data Security Awareness at $49.00, and Harassment Prevention at $39.00; these are workbook values, not universal market rates.

Next Training Due is formula-driven: an expired record uses TODAY()-7, a record with completed training uses TODAY()+90, and other populated records use TODAY()+30. Treat those dates as the workbook's planning logic and adjust the formulas if your organization's renewal policy uses different intervals.

The most damaging error is overwriting formula columns M through Q on the Training Matrix. If someone types a number over Completion %, the Dashboard may still display a value, but the employee-level result will no longer respond correctly to course status changes. Keep calculated columns separate from pale yellow input cells.

Inconsistent Status Entries

Typing variations such as Complete, Done, or Pending breaks the COUNTIF logic because the formulas recognize the listed status values. Use the dropdown in H2:L101 so each entry remains Completed, In Progress, Not Started, Expired, or Not Required. A single wrong label can change both the employee's percentage and the department average.

Misusing Not Required

Not Required removes a course from the denominator. Marking three of five courses Not Required leaves only two required courses, so one completion equals 50% and two completions equal 100%. That is useful when assignments genuinely differ by job, but it can overstate readiness if the status is chosen merely to improve the percentage.

Another failure occurs when a Course ID is changed in the catalog but not kept consistent with the lookup range. The Cost Lookup formula may return zero when it cannot find a match. Review course IDs, providers, renewal periods, and active status together during each catalog update instead of changing only the course name.

Assign one owner for the workbook and give that person a fixed review time, such as the first Monday of each month. During the review, update employee statuses, check records marked Action Required, and compare the Dashboard's department averages with the prior review. The workbook's formulas recalculate when the underlying entries change.

A Simple Review Cycle

• After onboarding: enter the employee profile and select the status for every applicable course.
• Before a training session: change Not Started to In Progress only when the employee has actually begun the course.
• After completion: record Completed and retain the supporting certificate or attendance record outside this workbook.
• At month-end: review Expired statuses and the Dashboard's Employees Requiring Action count.

Keep Inputs Controlled

Use the existing dropdowns rather than free-typing statuses, and preserve the MM/DD/YYYY date format. Update the Course Catalog before changing employee assignments so course names, providers, renewal periods, and costs remain aligned.

The prepared ranges contain 100 rows for employees and courses. When your organization exceeds those ranges, do not assume formulas, formatting, and validation will continue automatically; test the added rows before relying on the results. For larger teams, frequent assignment changes, or certificate storage, a dedicated learning-management system will be more reliable than a manually maintained XLSX file.