Understanding Training Trends
as Technicians Gain Experience

Calculating Monthly Training Hours per Service Technician

1. Put the dataset into an Excel Table

• Click anywhere inside your dataset.
  
• Convert the range to a table using Ctrl + T.
  
• In the dialog box, confirm My table has headers if applicable.
  
• Tables make formulas cleaner and auto-fill new rows automatically.
  

2. Create the Month column from the Training Date

• Add a new column called Month.
  
• In the first row of that column, use the TEXT function to extract the month name from the training date:
  
  • Type =TEXT([@[Training Date]],”mmmm”)
    
• Press Enter and let the table auto-fill the formula down the entire column.
  

3. Create a unique list of technicians

• In a new area, add a header like Technician.
  
• Use the UNIQUE function referencing the Technician Name field in the table:
  
  • Type =UNIQUE(TableName[Technician Name])
    
• Copy the spilled list and paste values using Ctrl + C, then Ctrl + Shift + V.
  

4. Create the month headers across the top

• In the row above your matrix, type January in the first month cell.
  
• Drag across until you reach December.
  
• Center the headers and technician names if you want a cleaner layout.
  

5. Build the Training Hours by Technician by Month matrix with SUMIFS

• In the first cell of the matrix (January for the first technician), use SUMIFS:
  
  • Sum range: Training Hours
    
  • Criteria 1: Month equals the month header (January)
    
  • Criteria 2: Technician Name equals the technician in the left column
    
• Your formula structure will follow this logic:
  
  • =SUMIFS(TableName[Training Hours], TableName[Month], MonthHeaderCell, TableName[Technician Name], TechnicianCell)
    

6. Lock references so you can fill across and down correctly

• Before copying, adjust your references:
  
  • Lock the technician column so it stays in column B as you copy left to right.
    
  • Lock the month row so it stays on the header row as you copy top to bottom.
    
• Then copy the formula across all months and down all technicians.
  
• If dragging creates issues because of how structured table references shift, copy the first cell and paste across and down instead of dragging.
  

7. Add totals quickly

• To total each technician’s annual training hours, click the total cell at the end of the row.
  
• Press Alt + = to auto-sum the row.
  
• Copy that down for all technicians.
  

8. Create the heat map with Conditional Formatting

• Highlight the full matrix of monthly training hours.
  
• Go to Home → Conditional Formatting → Color Scales.
  
• Choose the style you want:
  
  • Green high (high values are green)
    
  • Red high (high values are red)
    
• This gives you an instant visual of where training hours concentrate.
  

9. Build the technician detail table for the scatter plot

• Copy the technician list to a new section.
  
• Add headers for:
  
  • Role
    
  • Tenure
    
  • Training Hours
    
• Use VLOOKUP to pull Role and Tenure from the dataset:
  
  • Lookup value: Technician Name
    
  • Table array: start from the Technician Name column in the dataset and include Role and Tenure columns
    
  • Column index: 2 for Role, 3 for Tenure (based on your selected table range)
    
  • Match type: 0 for exact match
    
• Fill the formulas down through the technician list.
  

10. Calculate total training hours per technician

• Instead of another lookup, use SUMIFS to total training hours by technician:
  
  • =SUMIFS(TableName[Training Hours], TableName[Technician Name], TechnicianCell)
    
• Fill down for all technicians.
  
• Confirm totals match what you see in the heat map totals.
  

11. Create the scatter plot and add a trendline

• Highlight the Tenure column and the Training Hours column.
  
• Go to Insert → Recommended Charts and choose Scatter.
  
• Update the chart title to match your KPI.
  
• Format markers and styles however you want.
  

12. Add the trendline

• Click a data point on the scatter plot.
  
• Add a Trendline.
  
• This gives you the directional relationship between tenure (x-axis) and training hours (y-axis).