Using Microsoft Excel to build a Training Matrix

Using a training matrix is an effective way to ensure staff have attend the training courses needed for the job they do. This might include health and safety training, training related to compliance with legislation, and product (or process) related training. A training matrix organises training records into a grid layout making the information very easy to understand.

Introduction

A training matrix can offer a range of benefits. Once of the most obvious is that it provides a clear overview of training activities. Gaps in staff training records can be identified quickly. If training courses have not been attended, or perhaps where training has expired, this information can be highlighted within the grid.

This page shows you how to create a Training Matrix using Microsoft Excel. Note that a fully-features training matrix tool is available as part of the People Inc. system.

How does a training matrix work?

Combining a list of employees and a list of courses, a training matrix is a grid that shows which employees have attended which courses. This is the most basic form of a training matrix.

Training Matrix – A Simple Example

Each row in the spreadsheet shows the training records for a particular employee (the courses they have attended), and each column shows which employees have attended a particular course.

Training, Skills and Qualifications

In the image shown above, when an employee has attended a training course this is indicated in the appropriate cell in the grid. In addition to course attendance, there are a number of further measures (or attributes) that can be used to help with staff development. These include competencies, experience, and qualifications. The training matrix we have shown does not focus on these.

While attending training is an essential part of the staff development process, it is important to note that employees who have been on a course are quite often at the beginning of their journey. Having attended training is of course no guarantee that they have a level of competence within the discipline in question.

While the matrix shown above is based on training course attendance, it is also possible to build a matrix using competencies, skills, experience, or qualifications.

Training Needs

Identifying training needs is an important part of the staff development process. Individual employees will each have different training needs, and these will depend on their current knowledge and experience, on the demands of the job they do, and on the plans and strategic direction taken by the organisation.

Some training needs can be identified very easily; it may be that legislation dictates an employee must attend a course. Training needs may also be associated with a particular piece of equipment, or a particular role. Lastly, training needs can be identified during a staff review (as part of the personal development plan for an employee).

Development needs can be satisfied in a number of different ways. It may be that a training course is the most appropriate way forward, but mentoring, shadowing, a secondment, or private study could also be effective approach.

Where training needs analysis is based on a competency framework, a formal assessment process is often used to establish the level of each employee (for each competency). This can then be used to generate training needs.

Training Matrix – Showing Training Needs

Organising your Training Records

You may want to add some additional columns to your spreadsheet so that you can filter the list of employees. This is useful if you want to look at a particular team or department, or if you want to look at all the people who work in a particular role. For example, you may need to identify staff in a particular area who have attended a certain course; adding more information about each employee will enable you to do this.

In addition, categorising your training courses (and re-ordering the columns so that they are grouped by course category) will often make the training information easier to use (particularly if staff within your organisation attend a significant number of different training courses). Moe detail can be added as required; you may want to add the level of each course, or perhaps details of the training provider you use.

Training Matrix – Showing Additional Employee Information and Course Categories

Course Dates (and Course Expiry)

You can use a training matrix to record additional information about the training, for example, when employees attended training courses, or when the training course will expire.

Training Matrix – Showing Course Date

If you show the expiry date in the cell (rather than the course date), you can use the conditional formatting feature in Excel to highlight courses that have expired (and therefore need to be re-booked).

A further date-related enhancement would be to add a target date for training needs. Where a training need has been identified, setting a target date for the completion of the course will help with prioritising the scheduling of development activities.

In the example shown below we have split the cell in the grid into two. This enables us to record both the course date, and the expiry date (note that not all training requires a refresher course so for most courses the expiry date is blank). The expiry date is automatically highlighted in red if it is overdue.

In addition, we have added a border to cells to denote mandatory training (these cells are outlined in red). This helps to ensure that a training need is recorded where a particular course must be attended by employees. Note that mandatory training can of course vary depending on the job that an employee does.

Training Matrix – Showing Course Date and Expiry Date

Advanced Features

With a strong foundation in place, you can use Excel to add further features and benefits (perhaps functionality that is specific to your organisation). For example, you can add a calculation to generate the expiry date (based on the number of years before a refresher course is due).

The People Inc. Training Matrix

The People Inc. training matrix feature is designed to provide very similar functionality to the spreadsheet we have described above. The matrix is generated automatically using training records recorded within the People Inc. system. Users can update training records directly from the Training Matrix itself, and the information can be exported to Excel if required. A web-browser version of the training matrix is also available; this is often used to provide line managers with information on staff training. Click on the blue button to learn more.

Learn More

More information about the Training Matrix

Training Matrix

More Information

If you would like more information about building a training matrix using Excel (or if you would like to take a look at the People Inc. training matrix tool), please contact us on 01908 265111, or click the button below to request a call-back.

Discover more about these topics

• Training Matrix
• Training Needs
• Excel
• Managing Training
• Staff Development
• Course Booking

Related Features

People Inc. provides a variety of tools to help manage training (and training needs). Some examples are provided below:

Training Matrix

The People Inc. training matrix plug-in provides an overview of employee training.

Staff Appraisals

Record and manage employee performance appraisals for your organisation. Click to view.

Training Wizards

Managing training needs, course bookings and completed training for groups of employees.

External Resources

The following links provide additional information about Competency Management. The links are to pages on websites over which we have no control. This information is provided for reference only.

• Training & Competence (www.hse.gov.uk) - External Link.