How to Create a Training Matrix in Excel (3 Easy Methods)

In this article, we will show you 3 methods of how to create a training Matrix in Excel. We’ll make the Matrix from a dataset for the first 2 methods. To demonstrate this, we’ve picked a dataset with 3 columns: “Employee”, “Topic”, and “Date”.

Download Practice Workbook

What Is Training Matrix?

This is basically a table to keep track of the employee training programs. This helps the managers of a company. They can decide how many employees need and how much training. This Matrix aids in the development process of an employee. The main elements of a training Matrix are – the Name, Training Topic, Relevant Dates, and some Calculations. You can also add – Employee ID, Supervisor, and Working Department to the Matrix.

3 Ways to Create a Training Matrix in Excel

1. Using PivotTable Feature to Create a Training Matrix in Excel

For the first method, we’ll use the PivotTable to make a training Matrix in Excel. Here, we have a dataset of the employees’ training schedules. We’re gonna import that data to make a table. After inserting a PivotTable, we’ll format it using PivotTable Options.

Steps:

• Firstly, select the cell range B4:D12.
• Secondly, from the Insert tab >>> select PivotTable.
• Thirdly, select Existing Worksheet and cell B16 as the output location.
• Then, press OK.

After that, we’re gonna see the PivotTable Fields dialog box.

• After that, move these Fields–
  • Employee to Rows.
  • Topic to Columns.
  • Date to Values.

• Then, select “Count of Date”.
• After that, select “Value Field Settings…”.

Then, a dialog box will appear.

• Select Product from the “Summarize value field by” section.
• Then, click on Number Format.

• Select Date from the Category section and Type “14-Mar-22”.
• Then, press OK.

Now, we’ll get rid of the Grand Total from the PivotTable.

• Firstly, select the PivotTable.
• Secondly, from the PivotTable Analyze tab >>> select Options.

A dialog box will appear.

• After that, from the “Totals & Filters” tab deselect both options under Grand Totals.

• Then, under the “Layout & Format” tab >>> put three dashes ( “—”) for empty cells.
• Finally, press OK.

Thus, we’ll get our training Matrix from a dataset in Excel.

2. Create a Training Matrix in Excel Using Combined Formula

In this method, we’re gonna use the same dataset. However, we’ll use UNIQUE, TRANSPOSE, IFERROR, INDEX, and MATCH functions to create a training Matrix here.

Remember, the UNIQUE function is only available for Excel 2021 and Office 365 versions.

Steps:

• Firstly, type the following formula in cell B18.

This function returns the unique value from a range. There are 4 unique names in our defined range.

• Secondly, press ENTER.

This formula will AutoFill as it’s an array formula.

• Thirdly, type this formula in cell C17.

We’re again finding the unique values here. As we want the output to be in the horizontal direction, that’s why we’ve added the TRANSPOSE function here.

• Then, press ENTER.

We’ll see the unique values along the horizontal direction here.

Now, we’ll input the dates in the respective fields.

• Type the formula in cell C18.

• Then, press ENTER.

We’ve got the value as explained before.

• After that, AutoFill that formula downwards and then towards the right.

We’ll have output similar to this.

Finally, add some formatting. Thus, we’ll show another way of creating a training Matrix in Excel.

Similar Readings

• How to Calculate Covariance Matrix in Excel (with Easy Steps)
• Multiply 3 Matrices in Excel (2 Easy Methods)
• How to Create Traceability Matrix in Excel
• Create a Risk Matrix in Excel (With Easy Steps)

3. Use of Conditional Formatting to Create a Training Matrix

For the last method, we’re going to create a training Matrix from scratch. Then, we’ll add Conditional Formatting to it. Finally, we’ll use the COUNT and COUNTIF functions to add percentage completion in our Matrix.

Steps:

• Firstly, type the following things on the Excel Sheet –
  • Name of the Employee.
  • Topics for Training.
  • Relevant Dates.
  • Completion Rate column (we’ll add a formula here).

• Secondly, format the cells.

• Thirdly, add Legends for the Matrix.

Now, we’ll add Conditional Formatting to the Matrix.

• Firstly, select the cell range C6:G9.
• Secondly, from the Home tab >>> Conditional Formatting >>> select “New Rule…”.

A dialog box will appear.

• Thirdly, select “Format only cells that contain” under Rule type.
• Then, select “between” and put the date range from “1-Apr-22” to “18-May-22”.
• After that, press Format.

• Select “More Colors…” from the Fill tab.
• Then, from Custom >>> type “#FFC7CE” in Hex >>> press OK.

• Then, press Apply.

We’ve applied Conditional Formatting to the dates.

Similarly, we can add the Green color for future dates.

And, the Gray color for the blank cells.

This is what the final step should look like after applying all the formattings. Remember to use the formatting in this order, else, the Gray color may not be visible here.

Now, we’ll add a formula to calculate the training completion percentage.

• Firstly, type the following formula in cell H6.

• Secondly, press ENTER.

We’ll get almost 0.67 as our output, which is 67%. This value is for the number of training scheduled for an employee and how much of it is completed.

• Finally, AutoFill the formula and change the number formatting to show the percentage.

Practice Section

We’ve added practice datasets to our Excel file.

Conclusion

We’ve shown you 3 methods of how to create a training Matrix in Excel. If you face any problems, feel free to contact us. Thanks for reading, keep excelling!

Related Articles

• Make an Eisenhower Matrix Template in Excel (With Easy Steps)
• How to Create a Matrix Chart in Excel (2 Common Types)
• Make a Correlation Matrix in Excel (2 Handy Approaches)
• How to Do Matrix Multiplication in Excel (5 Examples)