The ultimate guide to creating an Excel skills matrix

Chapter 1 – Getting started with a skills matrixCopied

Before creating a skills matrix, it’s helpful to understand why you need one. Here, we’ll take a quick look at the purpose of skills matrices.

What is a skills matrix?

A skills matrix is a document that contains a record of each employee’s skills and qualifications.

They are typically used by training coordinators, operations managers, team leaders, and project leaders, and are an essential tools for organizations that have embraced skill pool management philosophies.

With a clear overview of the skills that are present in your organization or team – as well as those that are missing – you can easily identify and close skills gaps, ensuring that you continue to operate as efficiently and effectively as possible.

Why are skills matrices important?

Skills matrices are important because they provide clear, easy-to-understand overviews of the skills and knowledge possessed by individual employees within an organization. This information can be used for a variety of purposes, such as:

• Identifying skills gaps. A clear overview of the skills present in an organization – as well as those that are missing – enables managers and team leaders to identify and close skills gaps, or avoid them entirely.
• Workforce planning. By understanding the skills and knowledge gaps within their workforce, organizations can plan for future training and development programs or recruitment efforts.
• Compliance. Making sure that you have an adequately trained and compliant workforce.
• Performance (self) evaluations. Skills matrices allow managers to evaluate the performance of their team members – and employees to conduct self-assessments – based on their skills and knowledge.
• Resource allocation. By understanding the skills and knowledge of their workforce, organizations can allocate resources more effectively, ensuring that tasks are assigned to individuals with the appropriate skills and knowledge.
• Avoiding overtraining. With a full overview of the skills your employees already possess, you can solely focus on developing those you need. This will help you better allocate resources – and ensure that you avoid costly overtraining.
• Succession planning. Skills matrices can help identify individuals and their skills associated with critical roles within an organization.

Want to learn more about skills matrices? Read our blog, “Everything you need to know about skills matrices.” You’ll emerge with a strong understanding of how skills matrices work, their importance, and the benefits you stand to gain by using them.

Examples of skills matrices

You can find numerous examples of Excel skills matrices on the “Templates” section of our website. There, you can browse through industry- and role-specific Excel skills matrices, populated with common certifications and skills you’ll need to keep your organization running smoothly. Once you find what you’re looking for, you can download it for free, forever, no strings attached.

With our free maintenance, cybersecurity, and lean manufacturing skills matrix templates, for example, you will receive a clear overview of the skills present in your organization that are related to these industries or sectors, as well as those that are missing.

Using this information, you can develop and implement a plan to ensure that your employees skills, certifications, and qualifications are current, comprehensive, compliant, and ready for the future.

Chapter 2 – How to create a skills matrix in excelCopied

Creating a skills matrix in Excel is a relatively straightforward process. Here, we’ll look at what you need to get started. After compiling this information, you can use it to populate your Excel skills matrix.

What you need to get started

• Identify the roles and positions within your organization. List all the job titles and positions that exist within your company. Depending on the scope of your skills matrix, this could mean creating a list of all employees, or only those on specific teams.
• Define the competencies required for each role. What skills, certifications, or other qualifications are required for a person to be successful in a specific role? Competencies could include technical skills, soft skills, and knowledge of specific tools or systems that are used.
• Create a skills inventory. A skills inventory assesses the skills, competencies, and certifications that your current employees possess. Skills inventories can be taken through surveys, self-assessments, or managerial assessments. Each additional column will correspond with a specific skill.
• Define proficiency levels for each competency. Common methods of defining proficiency levels include numerical scales and descriptive terms (such as “Beginner”, “Intermediate,” and “Advanced”). The specific method is up to you, although it is important to be clear and consistent in using the same method for all competencies in the skills matrix.
• Map employee skills to job requirements. Once you have created a skills inventory, you can use it to map employee skills to the competencies required for their roles. This will allow you to identify any gaps in knowledge or skills that need to be addressed.

How to populate your Excel skills matrix

Now, you can begin using the above information to populate your Excel file:

1. In the first column, input all relevant employee names.
2. In the second column, list the employee’s job title.
3. For each column following, list a specific competency, qualification, certification, or skill. Moving down the row, input the proficiency level for each as it corresponds to individual employees.

Once your Excel skills matrix has been populated with this data, it will look similar to the image above. As you can see, each row corresponds to an individual employee. Moving along the row, you can easily see the skills they do – and do not – possess.

Chapter 3 – Skills matrix scoring systemsCopied

In the previous chapter, we briefly touched on defining proficiency levels for the competencies listed in your skills matrix. However, there are several ways to do so. Here, we’ll look at two of the most common methods of scoring a skills matrix.

Numerical scoring

Assigning a numerical value to skills or competencies is perhaps the most obvious method of scoring a skills matrix. These values should reflect the level of required proficiency, and can be based on a scale of 1-5, with 1 representing a basic level of proficiency and 5 representing an expert level. You can also consider factors such as the importance of the skill to the job, or the amount of training required to achieve competency.

Again, as stated in Chapter 2, the approach you take to numerical scoring is up to you. Whatever makes most sense – and results in a skills matrix that is both easy to use and understand – is the best method.

What is important, however, is creating a scoring system that is objective and consistent. You can achieve this by clearly defining score levels, and using specific examples to illustrate what each level of proficiency looks like in practice.

Should you not want to use a numerical scoring system, you can also use icon sets to create a more visual representation of proficiency levels in your organization or team.

Icon sets for skills matrices

Excel contains several features that will enable you to easily identify skills gaps. We find applying formatting rules and Excel’s built-in icon sets to be the most effective.

Using icon sets

Icon sets in Microsoft Excel allows you to add visual cues to data based on the values within a range of cells. Using icon sets, you can quickly and easily highlight important information and identify trends in data. A green check, for example, can be used to indicate employee skills proficiency, while a red X could show that an employee does not possess a certain skill.

To use icon sets:

• Select the range of cells to which you want to apply an icon set.
• Select the “Home” tab, then choose “Conditional Formatting” on the top ribbon.
• Select “Icon Sets” and choose the set of icons that you want to use. Excel offers several pre-designed sets from which you can choose.
• Select the threshold values for each icon by clicking on the arrow next to the icon set and choosing “More Rules.” Here, you can choose the values that will correspond to each icon in the set. For example, you could assign a green checkmark for values greater than or equal to 80, a yellow exclamation mark for values between 60 and 79, and a red X for values less than 60. These will correspond to employees’ proficiency levels for certain skills.
• Once you have selected the icon set and set the threshold values, click “OK” to apply the formatting to your selected range of cells.
• The icon set will now be applied to your data, and you will see the corresponding icons displayed next to each value in the range. You can customize the appearance of the icons by changing their color, size, or shape, or by using a different set of icons altogether.

Now that you’ve created icon sets for proficiency levels, you can begin adding formulas to cells that will help you perform certain tasks, such as finding totals or individual employees with certain skills.

Chapter 4 – Using a skills matrixCopied

Now that you have a strong foundation for an Excel skills matrix, and have added all necessary information, formulas, and icon sets, you can begin to use your skills matrix for a variety of purposes. We’ll start with one of its most important uses: identifying skills gaps.

First, however, let’s take a look at the concept of a skills gap and the challenges they pose to organizations.

What is a skills gap?

The term “skills gap” refers to the difference between the skills that organizations or teams require and the skills that those organizations or teams currently possess. Skills gaps can occur for several reasons, such as a lack of experience in certain areas, or outdated qualifications or certifications that prevent employees from undertaking certain roles, tasks, or duties.

Skills gaps pose challenges to any organization, but especially those that require highly skilled positions that may be difficult to replace. By taking steps to close skills gaps – or better, to prevent them from occurring entirely – you increase operational efficiency, productivity, employee satisfaction, workplace safety, and regulatory compliance.

Once you have formatted icon sets and input formulas, you are ready to use your skills matrix to identify skills gaps.

First, you’ll need to add three new rows to your skills matrix. Do this under the last employee in the second column of the matrix.

Label them as such:

Calculating a skills gap

A skills gap is the achieved skill level minus the required skill level.

To calculate skills gaps, subtract the numbers in the “Required” cells from those in the “Current” cells. Here, four employees are proficient, while there are three required. This results in a “positive” skills gap denoted by a green cell, meaning there is an additional proficient employee in this area.

The following formula will automatically subtract numbers in columns. For this example, we’ll say we are identifying a skills gap in Column C. In this case, you would input this formula into the formula bar for the “Gap” cell: “=C1-C2”.

In the below example, the required level for this engineering team is 10, meaning that everyone on the team should have a Professional Engineering degree. Jared and Rachel, however, do not possess the required skill level, hence the gap of -2 for this skill.

How to use your skills matrix

In addition to helping you identify skills gaps, your skills matrix provides you with a clear overview of the required skills for a process, project or team. With this information, you can now more easily:

• Develop training plans. By focusing on specific skills and knowledge areas where employees need improvement, you can provide more effective training and development opportunities.
• Support career development. By identifying the skills and knowledge required for different roles within the organization, you can help employees to develop the skills needed for future career progression.
• Performance management. By setting clear expectations for the skills and knowledge required for each role, you can measure employee performance against these expectations.
• Succession planning. By identifying the skills and knowledge required for different roles within the organization, you can identify potential successors and develop plans to ensure smooth transitions when key employees leave the organization.

Chapter 5 – Advanced formulas for skills matricesCopied

Now that you’ve created and begun using your skills matrix, you may be ready to take steps toward refining it. To do so, there are several Excel formulas you can use to help you perform certain tasks, such as finding totals or individual employees with certain skills.

Because skills matrices require a basic working knowledge of Excel, we will assume that you know how to add formulas to cells, giving us more room to fully focus on the formulas and their uses.

For this section, we’ll assume that employees are listed in Column A and job titles are listed in Column B, with skills in C3-K3.

COUNTIF

You can use COUNTIF to count the number of times a particular skill or competency appears across your skills matrix.

For example, to count how many times the skill “Auditor” occurs in Row 3, use the following formula: =COUNTIF(C3:K3,”Project Management”).

This will return the number of times the word “ISO” occurred in Row 3 (your skills).

You can also watch a short video tutorial here.

SUMIF

SUMIF helps you calculate the total number of times a team member possesses a certain skill.

Let’s say you want to calculate how many skills an employee named John possesses in Row 3. To do so, use the following formula: =SUMIF(C3:K3,”x”). This will return the total number of skills John possesses in Row 3.

You can also watch a short video tutorial here.

AVERAGE

The AVERAGE formula helps you find the average skill level of a team and see if it improves over time.

To do so, the AVERAGE formula calculates the average value of a range of cells.

To calculate the average skill level for a proficiency, or example, you can use the following formula: =AVERAGE(D4:D13). This will return the average proficiency level for the skill in column D.

VLOOKUP

Use VLOOKUP to search for a value in a table and return a corresponding value from the same row. This helps find a team member’s skill level based on their name or ID number.

For example, if you want to find John’s skill level based on his name in Column A, you can use the following formula: =VLOOKUP(“John”,A2:B10,2,FALSE). This will return John’s skill level in Column B.

You can also watch a short video tutorial here.

INDEX/MATCH

INDEX/MATCH is useful for finding an employee’s skill level based on their name or ID number, then return the corresponding skill description, such as beginner, intermediate, or advanced (or a numerical value, depending on your method of defining a proficiency levels, as described in Chapter 2).

For example, if you want to find John’s skill description based on his name in Column A, you can use the following formula: =INDEX(C2:C10,MATCH(“John”,A2:A10,0)).

This will return John’s skill description in Column C.

Creating pivot tables

When working with large spreadsheets of data in Excel, pivot tables can help you organize and analyze information quickly and easily.

To create a pivot table, select the desired data from the spreadsheet, then click on the Insert tab and select Pivot Table. The fields you want to include and any calculations you want to perform can be chosen from this menu.

Pivot tables are great for grouping data by categories, such as department or skill, and calculating statistics such as averages or totals. In addition, they provide an effective way to display that data, so it’s simple to interpret.

You can watch a short video tutorial on how to create pivot tables here.

Using macros 

Macros are an efficient and effective way to automate repetitive tasks.

With Excel macros, you can extract data from a skill matrix in no time without having to manually filter, sort, and calculate data.

To create a macro in Excel, you must first enable the Developer tab in the ribbon. Then select Record Macro to record your actions, so the macro can play it back and extract data from your skill matrix quickly and easily.

Thanks to macros, mundane tasks that used to be time-consuming can now be done with a single click.

You can watch a short video tutorial on using macros here.

Exporting data to other programs

Exporting data is a great way to obtain more value from a skill matrix in Excel.

By sending data to other programs, such as databases or statistics software, you can further analyze it – or share it with others in different formats.

To export data from an Excel sheet, use the Save As function and choose your desired format, such as CSV, XLSX, or TXT. This will make it easier for you to work with the data outside of Excel and get even more out of your skill matrix.

Chapter 6 – Best practices for skills matrices Copied

To wrap things up, here are a few tips, tricks, and best practices for skills matrices hat will help you increase the usability of your Excel skills matrix.

• For easier navigation. Use the Freeze Panes feature to keep column and row headings visible as you scroll through your skills matrix.
• For consistency. Use “Data Validation” to ensure that the data entered into the matrix is accurate and consistent. For example, you can use data validation to ensure that only numbers between 1 and 5 are entered for proficiency levels.
• To keep your skills matrix current. Use conditional formatting to highlight cells that have not been recently updated. This will help you identify cells that need to be updated.
• To ensure data remains accurate. Use the “Protect Sheet” feature to prevent others from accidentally deleting or modifying the information in your skills matrix.
• To keep your skills matrix concise and readable. Use abbreviations for skill. For example, instead of writing “Project Management,” use “PM.”

Chapter 7 – Challenges in creating and using skills matricesCopied

In this chapter, we’ll focus on a few of the common challenges that users face when creating and using skills matrices.

Defining skills and competencies

 Defining what skills and competencies are required for a job can be difficult, particularly when there is a large range of roles within an organization – or when an organization requires highly technical or specific skill sets.

Gathering data

Collecting data on the skills and knowledge of employees can be time-consuming and complex, particularly in larger organizations. It may involve surveys, assessments, or interviews, and requires careful planning and execution.

Maintaining accuracy

Skills matrices need to be regularly updated to reflect changes in the organization and to ensure that they remain accurate. This requires ongoing effort and investment of both time and resources.

Managing data securely

Collecting and storing data on employee skills and knowledge requires careful management to ensure data privacy and security. The right skills management solution, however, will take care of this for you. AG5, for example, has passed extremely rigorous cybersecurity screenings conducted by 86 of our major clients, employs fully certified IT security staff, and is ISO 27001-compliant for software-as-a-service.

Ensuring buy-in

Employees and managers need to understand the purpose of skills matrices and how they will be used. Ensuring buy-in from all stakeholders can be challenging, particularly if employees see the process as threatening or intrusive.

Integrating with other system

Skills matrices need to be integrated with other HR systems, such as performance management or training and development, to ensure that they are effective in driving organizational outcomes. In Chapter 8, we’ll take a closer look at how you can effectively integrate Excel skills matrices into performance management solutions.

Chapter 8 – Integrating skill matrices with skills management solutionsCopied

AG5’s skills management software integrates easy-to-use skills matrices with an intuitive performance management – giving managers and employees full ownership over their development.

Doing so, however, will require you to move away from Excel skills matrices – although the result will be an easier, more efficient method of managing employee skills, certifications, and qualifications, driving overall performance.

To successfully integrate an Excel skills matrix with a skills management solution, you should:

• Import Excel skills matrix data into AG5. Be sure to ensure that all data is accurate and up-to-date. This will enable AG5 to provide a comprehensive overview of employee skills and competencies.
• Set performance objectives. Use AG5’s skills matrices to set performance objectives for each employee, based on the skills and competencies required for their job role. This will help ensure that employee performance is aligned with those of the organization – and will also give employees control over their own development, helping them manage their skills via self-assessments and other helpful tools.
• Identify skills gaps and training needs. By combining intuitive dashboards with easy-to-use and manage skill matrices, you can easily track and manage employee training and development activities.
• Regularly monitor progress. As stated above, using skills matrices – and performance management software – requires an investment of both time and resources. This investment, however, will enable you to make informed decisions about employee development and succession planning, ensuring that you, your teams, and your organization are set up for future success.

Author Copied

Jago

As Head of Marketing, Jago utilizes data-driven strategies to drive AG5's success. When not at work, he's spending time with family and keeping an active lifestyle.