How to Calculate Tech Utilization Rate in Excel

Build the Technician Utilization Heat Map and Annual Averages

1. Add a Month Column from the Date

• Insert a new column for Month (the video replaces “Weekday” with Month).
  
• In the Month column, use the TEXT function on the date cell:
  
  • =TEXT([date_cell],”mmmm”)
    
• Fill the formula down the dataset.
  
• If you ever want weekday instead, use:
  
  • =TEXT([date_cell],”dddd”)

2. Create the Month List Down the Rows

• In your summary table, type Month in the header cell (example: B2).
  
• Type January in the cell below (example: B3).
  
• Drag down until December (example: B14).

3. Create the Technician List Across the Columns

• Generate a unique list of technician names using:
  
  • =UNIQUE([TechNameRange])
    
  • In the video, the technician name column is used to create this list.
    
• Highlight the spilled UNIQUE results.
  
• Copy and paste values to remove the formula:
  
  • Ctrl + C, then Ctrl + Shift + V
    
• Sort the list A to Z.
  
• Copy the sorted list.
  
• Paste it across the top of your utilization grid using Transpose:
  
  • Paste Special → Transpose
    

Result: Months run down the rows, technician names run across the columns.

4. Build the Monthly Utilization Formula with SUMIFS

Goal: For each technician and month, calculate:

• Total Billable Hours for that technician in that month
  divided by
  
• Total Available Hours for that technician in that month
  
• Click the first cell inside the utilization grid (example: C3 for January and the first technician).
• Start the numerator with SUMIFS summing Billable Hours:
  
  • =SUMIFS([BillableHoursRange], [MonthRange], [MonthCell], [TechNameRange], [TechHeaderCell])
    
• Divide by the denominator SUMIFS summing Available Hours:
  
  • =SUMIFS([AvailableHoursRange], [MonthRange], [MonthCell], [TechNameRange], [TechHeaderCell])
    
• Format as percentage:
  
  • Ctrl + Shift + 5
    

Important detail from the video:

• Make sure the denominator is referencing Available Hours (correct column), not the wrong column, then fix it by changing the column reference.

5. Lock References So You Can Drag the Formula Across and Down

When you copy the formula across technicians and down months, you must lock references correctly.

• In the formula, use F4 to lock the full column ranges inside SUMIFS (Billable, Month, Tech, Available).
  
• Lock the Month reference so:
  
  • Moving left/right does not change the month
    
  • Moving up/down does change the month
    
• Lock the Technician header reference so:
  
  • Moving left/right does change the technician
    
  • Moving up/down does not change the technician
    
• Copy the formula across the row (all technicians), then down the column (all months).
  

Result: The entire monthly utilization matrix fills in correctly.

6. Create an Annual Utilization Row by Technician

This row calculates annual utilization per technician using totals (not averaging the monthly percentages).

• Add a row beneath the monthly grid for Annual Average Utilization.
  
• Copy your monthly utilization formula into the annual row.
  
• Remove the month criteria from both SUMIFS sections:
  
  • Remove the MonthRange and MonthCell pair
    
  • Remove the extra comma left behind
    
• Keep only the technician criteria so it sums the entire year for that technician:
  
  • Total billable hours across the year ÷ total available hours across the year
    
• Format as percent and copy across all technicians.
  

Key point shown in the video:

• Do not average the monthly utilization percentages.
  
• Use total billable ÷ total available for the year.

7. Add Conditional Formatting Heat Maps

• Highlight the monthly utilization grid.
  
• Home → Conditional Formatting → Color Scales.
  
• Choose a color scale you like (the video notes high and low can both be good or bad depending on your ops goals).
  
• Repeat conditional formatting for the annual utilization row if desired.

8. Create the Annual Utilization Chart by Technician

• Highlight the technician names row and the annual utilization row.
  
• Insert → Recommended Charts.
  
• Choose the chart style that displays utilization by technician.
  
• Update the chart title to something like:
  
  • “Annual Technician Utilization Rate”
    
• Optional formatting shown:
  
  • Adjust axis minimum to something like 0.5 to show more variance
    
  • Remove decimals in labels via the Number formatting options

9. Repeat the Same Build by Tech Level

The video recreates the same structure, but groups technicians by Tech Level instead of technician name.

• Copy the table structure to a new area.
  
• Replace the column headers with Tech Levels (example: Senior, Junior, etc.).
  
• Build the same utilization formula using SUMIFS:
  
  • Numerator: sum of Billable Hours by Tech Level and Month
    
  • Denominator: sum of Available Hours by Tech Level and Month
    
• Lock references with F4 so it copies correctly across and down.
  
• Add the annual utilization row by removing the month criteria (same method as before).
  
• Apply conditional formatting to the grid.
  
• Insert a chart for:
  
  • “Annual Utilization by Tech Level”
    
• Add data labels to the chart if needed.