Training Matrix instructions v 6
Training Matrix
A training matrix is a useful tool that will support managers to:
-Identify mandatory, role specific and individual training needs
-Create new entrant training plans
-Plan team training needs
-Ensure all mandatory training is completed
-Record mandatory training requirements
A basic training matrix will be supplied by the Learning and Development team. This will be an Excel workbook prepopulated with the available core and role specific mandatory training and will contain two worksheets – Master training matrix and Team training matrix.
In order to complete a training matrix:
-select the Master training matrix tab
-list the team role titles in column A
-review the training requirements for each role inserting a for required training and a x for training that is not applicable
Core mandatory training - must be completed by all staff.
Role specific mandatory training - all training that is a regulatory or legislative requirement of undertaking a particular role
Role specific training - all non regulatory or legislative training that is required to fulfil a specific role
Individual training needs - non role specific training that will enhance an individual’s performance
-insert new columns for any additional training that is not listed and populate with a or xas appropriate
-remember to save periodically
This is the Master training matrix.Save the workbook as training matrix v1.
Team training matrix
To complete the Team training matrix:
-select the Master training matrix tab
-select the home cell (top left hand corner as indicated below) and copy
-select the Team training matrix tab
-select the home cell and paste
-if the training row is compressed hover the cursor over the line between rows 1 and 2 and a cross head arrow will appear
-double click the mouse and the cell will become full size
-select cell B:1 right click select insert
-select entire column and click ok
-click the F4 key to repeat the process
-select cell B:1 and input the header Surname
-select cell C:1 and input the header First Name
-insert the staff members’ names next to the appropriate role. If there is more than one staff member undertaking the same role additional rows will need to be inserted and the training information copied into the row.
This is the team training matrix. Save the workbook.
Using the matrix
-applying a filter will allow selection of individual roles or staff members
-when training has been completed overtype the with the date completed
-each time amendments are made to the workbook the version number must be changed
-old versions of the workbook should be saved to an archive file for reference
-access to the workbook should be restricted to line managers only to ensure the workbook is amended correctly.
Adding new team members
-when new staff members join a team insert a new row on the team training matrix worksheet under the relevant role
-select the master training matrix tab and copy the row relevant to the new staff member’s role
-select the team training matrix tab and paste into the newly inserted row. This information will form the basis of the new staff member’s training plan
-save the workbook
Refresher training
Some training once completed will require refresher training at a later date. In order to identify when the training is due:
select the column or a cell in the column immediately to the right of where you want to insert the new column
right click and select insert
select entire column and click ok
copy and paste the text from the existing column rows 1, 2 & 3 into the new column
in row 4 insert the text training next due
In order to calculate when the training is next due use a function called EDATE.
select the cell in which the next due date is to be displayed
on the toolbar select Formulas/Date and Time/EDATE
select the cell displaying the date the training was last completed
in the months cell (indicated below),input the number of months until the training is next due. In the example below the training is due to be refreshed every 3 years therefore 36 has been input in the months cell.
select ok
the next due date of the training will now be populated on the matrix. If the date does not show correctly select the cell displaying the date the training was last completed. Right click and select format cells/date/ok. The cell displaying the next due date of the training will now be displayed.
This formula can then be copied into the rest of the training next due column
select the cell displaying the next due date of the training.
hover the cursor over the bottom right hand corner of the cell and a cross head arrow will appear
left click and drag the mouse down the column. This will copy the formula into these cells.
It is also possible to format the cells so that a RAG status will appear – Red, Amber, Green. This will be a visual prompt to indicate when refresher training is due or has passed its date.
To format the cells:
highlight the range of cells that you want to apply the formatting to
select conditional formatting and then new rule
select format only cells that contain
in the rule description window select cell value/greater than and then insert the formula
=NOW()+61
select format
select the fill tab and then select green.
select ok.
upon returning to the new formatting rule window select ok.
These steps will then need to be repeated for the amber and red formatting.
Amber (training next due in 60 days or less)
select conditional formatting and then new rule
select format only cells that contain
in the rule description window select cell value/less than and then insert the formula
=NOW()+61
select format
select the fill tab and then select amber
select ok
upon returning to the new formatting rule window select ok.
Red (training overdue)
select conditional formatting and then new rule
select format only cells that contain
in the rule description window select cell value/less than and then insert the formula
=NOW()
select format
select the fill tab and then select red
select ok
upon returning to the new formatting rule window select ok.
Cells which have no data will also require formatting in order that they stay blank.
select conditional formatting and then new rule
select use a formula to determine which cells to format
insert the formula =ISBLANK(A1)=TRUE
select format
select the fill tab and then select white
select ok
upon returning to the new formatting rule window select ok.
Formulas can be copied across to new columns if the refresher period is the same e.g. 3 years.
1