How to create a Training Matrix Template in Excel

In one of our recent posts, we covered how to build a resource plan Excel template .  One of the fundamentals of such plans is the assumption that your resources have the required skills to undertake the tasks.

But how exactly do you assess this?

In reality, most businesses find themselves with staff that have a mix of competencies from those that are highly skilled to those that require a degree of training.

To assess that, you’ll need to review competencies against a predefined list of required proficiencies/skills that you want to be attained by your workforce.

A training matrix can be a great tool to use in such instances especially where you are analyzing a particular group or team as it enables, at a glance, for people to see/assess the skill level across a number of individuals enabling easy comparison and analysis identifying leaders/knowledge experts in a particular zone and those that are in need of training.

Using a training matrix to Identifying where you’re business is currently placed with regards to skills, is a key challenge but once it’s done it can help you in a variety of ways.

In this article, we’ll cover what a training matrix is and then show you 4 examples.

Benefits of having a Training matrix

Perhaps to understand the benefits that a training matrix can provide you need to, firstly, consider life without one.

For example, without a training matrix:

  • Evaluating your staff is complex
  • You are unable to set and monitor competency benchmarks for given positions
  • Understanding, when training is due for renewal, is difficult and courses can be missed.

Perhaps the most important element to consider is that a Training Matrix shouldn’t be treated as just a “dumb” document, it should promote an action. As ever, capturing the data is one thing but then doing something with it is where the magic happens.

Training matrices are a crucial tool in the arsenal of human resource planners as they help you keep track of:

  • Required / mandatory training (E.g. Health & Safety)
  • Training that might be specific to a location or group
  • Capability against a training need (i.e. Expert/Beginner)

The matrix can be used to both to initiate training where it’s required but also help in organizational planning (for example consider how you’d react if you see pockets of excellence vs areas where skills competency is not suitable).

Also, for example, if you have an employee that shows that they have not yet undertaken a particular element of training that is considered mandatory then you can use the matrix to highlight that enabling you to then liaise with the employee to coordinate/diarise the training.

Another misnomer is that the document is there just to track external training courses, in fact, it’s just as useful to track skills and requirements. For example, you may want to track your onboarding process with new staff. Perhaps there’s a series of induction process/course that needs to happen. A training matrix is an ideal tool on which to capture that information.

To recap a Training Matrix has key benefits to the company enabling it to:

  • Analyze its skills
  • Be aware when training is coming up for renewal
  • Awareness of what skills are required for which roles
  • Company is aware of areas for Employee growth
  • Focus on value
  • Enables the business to review what is required per position

When Excel doesn’t cut it

Below we’ll cover how to build an Excel training matrix template. But it’s important to think where the use of Excel stops and a need for an application begins.

We’ve mentioned this in previous articles and as much as we love Excel there comes a time (usually when your organization reaches a certain size, or if you’re looking for more complex reporting from the data when a dedicated tool is more appropriate. This is especially true where you’re looking for your training matrix to integrate with other corporate applications.

However, if you’re looking to monitor a small organization then Excel is a perfectly adequate tool.

What goes into a Training plan

So let’s start to look at how we’re going to build our Excel Template.
The good news is that creating a training Plan isn’t complicated.

It requires a little bit of planning and as a spread-sheet it is fairly simple to create (requires just some basic formatting skills).

When planning it’s worthwhile to consider ahead what you’re planning to do with the data (i.e. reports/KPI’s etc) that way you can plan on what specific data you need to enter/capture) and what level of detail you’re looking to analyze.

For example, if you’re looking to generate reports on skills gaps in certain departments then it makes sense to capture the relevant information.

Let’s consider the key ingredients

  • Employee names
  • Resource Groups (i.e. Buyers, Warehouse operative etc)
  • Skills/Course Names or Training reference code
  • Competency rating
  • Training periodicity (i.e. does the training require renewing every so often).

Building your Excel training matrix template

Below, we’re going to show four Training Matrix examples, as ever we’ll provide some screenshots of them with column/row labels so that you can easily replicate yourself.

We’ve chosen to track skills in our examples, you could also use specific training courses you want individuals to undertake or another similar attribute that you find useful to track.

Example 1

This is a small matrix just to show how it’s done. Clearly, you can expand both the names & the training attributes to create a bigger table depending on your needs.

Let’s start off with the top row. Here we list the training attributes (here we’ve listed 5 skills). We’ve formatted the cells so the orientation displays the text at around 45-degree angle.
To the left, we’ve listed the names of the Employees we’re interested in.

We’ve then created a key which indicates the competence level vs the skill. Here we’ve opted for 4

Note the first 3 categories are not subjective (either you’ve done it or you haven’t). For the fourth category (that of guru) that ones open to a little interpretation but it’s fairly self-explanatory I think (i.e. you’re not just trained in it but you’re a master/experienced).

We then simply use the colors to grade each staff member against the skill being tracked.

We then format the table with some gridlines to make everything a little easier on the eye and hey presto a simple training matrix.

Example 2

In the second example, we’ll expand on our first simple training matrix and add a bit more detail to create some more functionality.

Here we’ve expanded the matrix and added some “functional” categories.

Let’s assume our 5 workers are employed in Supply Chain and let’s also assume that we want to review training based on a competency category. In this case, we have two

  • Procurement
  • Negotiation

We’ve added two rows for the category types and blocked them out in red. The skills to the right of these are the skills for that category.

Let’s say we also want to know our training rating for each category. We can do that by giving a number to each “grade” in the example we’ve used
0 for Not trained
1 for Familiar
2 for Trained
3 for Guru

We then use the SUM() function to total each column which provides an insight to how the company is covered under a particular competency.

For example, we can use this method to instantly see that we have low skills in terms of Negotiation skill 5.

This method enables us to provide some meaningful management reports around training & competency levels.

Example 3

in our third example let’s assume we want to use our training matrix to help schedule skills training in our organization.

In this example, we’ll replace our “score” from the last example with a date of when the training is due for renewal or “refresher training”.

We can then easily use this to see where dates are not present (or use conditional formatting to show the date in a different font (i.e. red) where the training is either overdue or coming up.

Then instead of totaling our scores as we did in the last example, this time we’ll want to show us the earliest date re-training is now due.

We use the MIN formulae to show us the earliest date from the table ROW in the “next training due” row.
Again a nice easy training matrix that provides useful actionable data.

Example 4

Finally, in our fourth example let’s assume that we want to understand which of the competencies/courses are critical (to help drive our training program). 

And we want to analyze our performacne against each criteria and then provide some analysis behind this.

Let’s take our second example and expand on it.

Firstly we want to know which skills are critical (here we score them 1-5 with 5 being highly critical).

We then add a “reporting” block to the footer of the matrix.

1/ We score each column

2/ We “average” each column.  This gives us an average score accross the personnel for that skill

3/ We then set ourselves a target against each skill (based on the criticality i.e. Skill 1 under Procurement is deememed Critical (skill level 5) and therefore our target competency for the team is an average of 3).

4/ We then use a RAG/Alert to create a visual representation of the status of training vs that skill.  In our example of Procurements skill 1 our average competency is 1.8 against a target of 3 so it’s red.

Again this offers the ability to create a variety of management reports and information whilst keeping the Training Matrix template relatively simple to produce and maintain.

So there are our 4 training matrix examples. If you’ve got some feedback or questions we’d love to hear from you in the comments section below.