Travel Time Percentage by Service Area
and Technician in Excel

How to Build Travel Time Percentage

1. Build a unique list of Service Area Zones

• Locate Service Area Zone in Column D of the dataset.
  
• In an empty area, create a unique list using:
  
  • Type =UNIQUE( then select the Service Area Zone column.
    
  • Use Ctrl + Shift + Down Arrow to select the full range quickly.
    
• Press Enter to return the unique zone list.
  
• Copy the results (Ctrl + C) and paste as values (Ctrl + Shift + V) so the list becomes static.
  
• This is important because you will sort the final results.

2. Convert Start Time and End Time into total minutes

• Identify the Start Time and End Time columns in your dataset.
  
• Compute the time difference:
  
  • Subtract Start Time from End Time.
    
• Convert that time result into minutes:
  
  • Multiply the difference by 1440 (because 24 hours × 60 minutes = 1440).
    
• Use Ctrl + Shift + 1 to view the raw value if the time formatting confuses the output.
  
• Confirm your result is in minutes (example shown: 122 minutes).

3. Calculate Travel Time Percentage by Service Area Zone with one formula

• You are not calculating row-by-row.
  
• You will:
  
  • Sum total Travel Minutes for a zone
    
  • Divide by total Paid Minutes for the same zone
    
• Build the numerator (Travel Minutes by zone):
  
  • Use SUMIFS on Travel Minutes (Column U)
    
  • Criteria range: Service Area Zone (Column D)
    
  • Criteria: the zone name (example: South)
    
• Build the denominator (Paid Minutes by zone):
  
  • Copy the same SUMIFS
    
  • Change the sum range from Travel Minutes (U) to Paid Minutes (AD)
    
• Divide numerator by denominator.
  
• Format the result as a percentage.

4. Create the chart and sort results correctly

• Click anywhere inside your Travel Time Percentage table.
  
• Insert a chart using Insert → Recommended Charts.
  
• Sort the zones:
  
  • If you want the smallest value at the top, sort Largest to Smallest (this feels backwards, but it produces the desired visual order in the chart/table).
    
• If sorting causes the formula references to shift incorrectly:
  
  • Fix the formula reference that got misaligned
    
  • Confirm values update correctly afterward

5. Adjust chart display and percentage formatting

• To show more variance visually, adjust chart bounds:
  
  • Set a lower bound like 0.18
    
  • Set an upper bound like 0.26
    
• Remove decimals on labels:
  
  • Double click labels
    
  • Go to number formatting
    
  • Set decimals to zero
    
• Update the chart title to match the KPI name (copy and paste the KPI title if preferred).
  
• Resize the title if needed.

6. Build a technician list and transpose it across columns

• Create a unique list of Technician Name the same way:
  
  • =UNIQUE( select technician name column, Ctrl + Shift + Down Arrow, Enter
    
• Copy and paste as values (Ctrl + C, Ctrl + Shift + V).
  
• Transpose the technician names so they run horizontally:
  
  • Copy the list
    
  • Right click where you want it
    
  • Choose transpose (Paste Special → Transpose)

7. Calculate Travel Time Percentage by Zone and Technician

• You are now adding a second criteria: Technician Name.
  
• Build the numerator:
  
  • SUMIFS Travel Minutes (Column U)
    
  • Criteria 1: Technician Name equals selected tech (example: Carlos Martinez)
    
  • Criteria 2: Service Area Zone equals selected zone (example: South)
    
• Build the denominator:
  
  • Copy the same structure
    
  • Change sum range from U (Travel Minutes) to AD (Paid Minutes)
    
• Divide numerator by denominator to get the percentage.

8. Fix dragging issues using absolute and relative references

• If you drag formulas across technicians, Excel will shift column references and break the calculation.
  
• Use F4 (or dollar signs) to lock references correctly:
  
  • Lock the full data columns (like U, AD, D) so they do not shift
    
  • Lock the technician reference so:
    
    • Dragging left to right keeps the same technician in the header cell
      
    • Dragging down changes the technician row reference correctly
      
  • Lock the zone reference so:
    
    • Dragging down keeps the same zone
      
    • Dragging across changes zones as expected
      

After locking references properly:

• Copy the formula and fill across the matrix to populate all technicians and zones.

9. Add an upper bound rule with conditional formatting

• Decide your threshold (example: 25%).
  
• Put that value in a cell (your “upper bound” cell).
  
• Highlight the Travel Time Percentage matrix.
  
• Go to Home → Conditional Formatting → Highlight Cells Rules → Less Than
  
• Select the upper bound cell as the comparison value.
  
• Adjust the threshold (30%, 20%, 15%) until the highlighting fits what you want to monitor.
  

Optional label formatting:

• Press Ctrl + 1
  
• Go to Custom
  

Add text like Upper bound: so the cell displays as a labeled threshold while staying numeric.