Mastering Skill Matrix Creation with Excel: Your Step-by-Step Guide to Success

Unlocking the full potential of your team begins with understanding their skills and competencies. In this comprehensive guide, we will delve into the art of crafting two distinct skill matrices, each designed to empower you with insights to boost productivity and performance.

We will use two sheets in one workbook, where each sheets will display a different type of skill matrix. The first matrix is titled Skill Matrix I (Advanced).

The first matrix will be filled with the skill set and each employee’s proficiency score. The creation of the matrix is determined based on several things such as the purpose of the skill matrix, how the organization is structured, and how interactions between departments and seniority levels are organized.

In this first matrix, We will focus on two things: departments and job levels. The purpose of this matrix is to display the skill data from each employee in one department at the different position / job level.

First, create a department profile table filled with the department name, total number of employees, total number of skills, and the date to know when the matrix was created or updated.

A Department Profile Table

Second, create parameters or grades for the competency or skill. You can name it with number to make easier to analyse.

A Grade Table

Third, create a grid that contains the employee’s name, job level or position, and skills or competencies. For the skill column, divide it into two more columns to place the target and current of score proficiency.

An Employee Data Table (before)

Fourth, fill in the Target column with the targeted value for each skill considering each employee’s role or position. For the Current column, fill in with the results of the evaluation/assessment of their skills based on the parameters/grading that has been made.

To make it easier to fill in and avoid input errors, you can create a drop-down list. So, how to do that?

1. Select the cells that need to be filled with the proficiency score, either the target or current score.
2. On the ribbon, select DATA > Data Validation.
3. For Source, hover over the grade table that contains the grade number. Block the entire number. Here, block from number 0 to 4. Selech OK.

You can use Excel features to make the matrix display easier to read. It is important to know which employee’s current score is low and which one is high. Because that is one of the purposes of this matrix. The feature I am referring to is Conditional Formatting. And this is how it works:

1. Select the range of cells that contains only the Current Score.
2. On the Home tab, click Conditional Formatting.
3. Choose Highlights Cells Rules > Text That Contains.
4. Write the number corresponding to the grade number and choose what kind of result you want to get.
5. Repeat this method as many times as there are grade numbers.

An Employee Data Table (after)

Fifth, this stage and the next will focus on data analysis. The analysis will be divided into two tables. The first table is to analyze the skill gap of each employee based on the comparison between their target score and current score. After knowing the results, you can find out which employees are still not reaching the target.

An Employee Proficiency Table

Avg of Target Proficiency: This data is taken from the average target score of all required skills. For a target score that contains 0, it means that the skill is not a target that needs to be mastered by the employee, but it is a good thing if the employee actually masters the skill. You can use the sumif and countifs functions to get the average number of proficiency targets.

=SUMIF($C$11:$J$11,"Target",C12:J12)/($B$5-(COUNTIFS($C$11:$J$11,"Target",C12:J12,0)))

$C$11:$J$11 = range of criteria from coloumn target and current
C12:J12 = row of score per employee
$B$5 = cell of total number of skills

Avg of Current Proficiency: This data is taken from the average current score of all existing skills. You can use the averageif function to get the average score of the current proficiency.

Skill Gap: This data is taken from the score average of current proficiency minus the score average of target proficiency. A minus result means that the employee has not reached the target given by the company.

Sixth, if the first table focuses more on analyzing per employee, the second table focuses on analyzing one department. This table analyzes how the target and current score of all employees in each skill set.

A Department Proficiency Table

Last but not least, you can display the top data and bottom data of the overall score. The top data consists of a list of skills that are strong in the department. On the other hand, the bottom data is a list of skills that are weak and need immediate attention such as the procurement of training or development programs.

Top and Bottom Tables

To find out the top 2 and bottom 2 data more easily, you can combine excel functions, which are: Index and match with large for the top data and small for the bottom data.

=INDEX($Q$12:$Q$15, MATCH(LARGE($V$12:$V$15, 1), $V$12:$V$15, 0))

$Q$12:$Q$15 = list of coloumn skills
$V$12:$V$15 = list of coloumn skill gap score
1 = find in coloumn skill gap score for the largest number 1

Done with the first matrix, let’s continue working on the second matrix. The second matrix is titled Skill Matrix II (Basic).

Although it is titled basic, actually in terms of analysis it will be much more complex than the first matrix. However, this type of matrix is more widely used by companies that have a simpler structure and division of roles than the previous matrix.

Slightly different from the first matrix, this second matrix consists of current proficiency score and interest score. Because we consider the level of interest, we can easily identify individuals who are experts and highly interested in specific skills. This information can be used for decision-making, such as selecting team members for a project, identifying candidates for training or mentorship, and aligning individuals with tasks that match their expertise and interests.

Skills and interests can change over time, so it’s important to periodically update the matrix. Regularly reviewing and updating the matrix helps in keeping the information accurate and relevant.

Here is an example of forming a team by combining 3 departments, which are: Sales, Marketing, Accounting and Finance. This is the first formation, where if the team leader or HR has a better candidate, whether based on the employee’s proficiency level or interest level, then it is very permissible to change. We have time until this project is actually running for the first time.

First, enter the name of the team or project.

Second, enter the names and roles of those involved in the team or project.

Third, enter the skills required for the team or project.

Fourth, create parameters or grades for the competency or skill. Also, scale the employee’s interest.

Fifth, select the proficiency and interest level for each team member from the drop down list.

Sixth, on completion, review the skills matrix to assess skills gaps.

Skill Matrix II

To make analysis esier, you can mark or sign each proficiency level for each skill, which one has the highest value and which one has the lowest. This is to see the value of each employee. Make sure there is at least one skill that has a high-level proficiency score/value. For the interest level, you can standardize per employee with a maximum of only two zeroes score by considering the proficiency level is sufficient for the project needs.

Choose the type of matrix that suits your company’s needs. Hope you do well and Good Luck!✨