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”.
Watch Video – Create a Training Matrix in Excel
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.
How to Create a Training Matrix in Excel: 3 Easy Ways
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.
=UNIQUE(B5:B12)
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.
=TRANSPOSE(UNIQUE(C5:C12))
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.
=IFERROR(INDEX($D$5:$D$12,MATCH(1,INDEX(($B$5:$B$12=$B18)*($C$5:$C$12=C$17),),0)),"")
Formula Breakdown
- MATCH(1,INDEX(($B$5:$B$12=$B18)*($C$5:$C$12=C$17),),0)
- Output: 1.
- This portion returns the row number for our INDEX function. Inside this portion, there is another INDEX function, which will check how many cells have values from cells B18 and C17.
- Now, our formula reduces to -> IFERROR(INDEX($D$5:$D$12,1),””)
- Output: 44713.
- This value means the date “01 June 2022”. Our lookup range is D5:D12. Between that range, we’ll return the value of the first cell D5.
- Thus, we get our value.
- 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
- Multiply 3 Matrices in Excel
- How to Create Traceability Matrix in Excel
- Create a Risk Matrix in Excel
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.
=COUNTIF(C6:G6,"<18-May-2022")/COUNT(C6:G6)
Formula Breakdown
- We’ve two parts in our formula. With the COUNTIF function, we’re finding the number of cells that has dates less than “18 May 2022”. Before this date, the employees have their completed training.
- Then, we’re counting the number of non-blank values in our range.
- After that, we’re dividing these to find out the percentage of completion.
- 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!
Thanks a lot
You are welcome. I am glad that this article was useful to you.