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

**Table of Contents**hide

## 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**.

`=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 (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**.

`=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.