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”.

Intro: how to create a training matrix in excel


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.

Using PivotTable Feature to Create a Training Matrix in Excel

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…”.

Using PivotTable Feature to Create a Training Matrix in Excel

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.

Using PivotTable Feature to Create a Training Matrix in Excel

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.

Using PivotTable Feature to Create a Training Matrix in Excel

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.

Create a Training Matrix in Excel Using Combined Formula

  • 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.

Create a Training Matrix in Excel Using Combined Formula

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.

Create a Training Matrix in Excel Using Combined Formula

We’ll have output similar to this.

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

Create a Training Matrix in Excel Using Combined Formula


Similar Readings


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).

Use of Conditional Formatting to Create a Training Matrix

  • Secondly, format the cells.

  • Thirdly, add Legends for the Matrix.

Use of Conditional Formatting to Create a Training 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.

Use of Conditional Formatting to Create a Training Matrix

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

  • Then, press Apply.

Use of Conditional Formatting to Create a Training Matrix

We’ve applied Conditional Formatting to the dates.

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

Use of Conditional Formatting to Create a Training Matrix

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.

Use of Conditional Formatting to Create a Training Matrix

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.

Use of Conditional Formatting to Create a Training Matrix


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

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

2 Comments
  1. Thanks a lot

Leave a reply

ExcelDemy
Logo