How to Analyze Overtime Rates Using Excel

How to Build the Technician Overtime Rate Table

1. Convert the dataset into an Excel Table

• Click anywhere inside your dataset.
  
• Press Ctrl + T.
  
• Check My table has headers, then click OK.
  
• Using a table makes formulas cleaner (structured references) and auto-fills formulas down the entire column.
  

2. Create a Month field from the Date column

• Insert a new column named Month in your table.
  
• In the first row of that column, enter:
  
  • =TEXT([@[Date]],”mmmm”)
    
• Press Enter and confirm the Month values auto-fill down the table.
  

3. Build the monthly summary layout

• Create a small summary table with these columns:
  
  • Month
    
  • Paid Non-Overtime Hours
    
  • Overtime Hours
    
  • Percent Overtime
    
• In the Month column, type January then drag down through December.
  
• Center the table if desired using Alt + H + A + C.
  
• Adjust column widths and use Wrap Text if needed to make headers readable.
  

4. Calculate Paid Non-Overtime Hours by month with SUMIFS

• For January, use SUMIFS to sum regular hours where Month equals January.
  
• Example structure (using your table’s column names):
  
  • =SUMIFS(Table[Regular Hours], Table[Month], $A2)
    
• Format the result as a number using Ctrl + Shift + 1.
  
• Copy the formula down through all 12 months.
  

5. Calculate Overtime Hours by month with SUMIFS

• Use the same approach, but sum the overtime hours column:
  
  • =SUMIFS(Table[Overtime Hours], Table[Month], $A2)
    
• Format with Ctrl + Shift + 1.
  
• Copy the formula down through all 12 months.
  

6. Calculate Percent Overtime by month

• Percent Overtime is overtime hours divided by total paid hours for the month.
  
• Use:
  
  • =OvertimeHours / (OvertimeHours + RegularHours)
    
• Format as percent using Ctrl + Shift + 5.
  
• Copy the formula down through all 12 months.
  

7. Create the combo chart (overtime hours + percent overtime)

• Hold Ctrl and select:
  
  • The Overtime Hours column
    
  • The Percent Overtime column
    
• Go to Insert → Recommended Charts and choose the combo chart.
  
• If it does not appear:
  
  • Insert → All Charts → Combo
    
  • Set Overtime Hours as a Column
    
  • Set Percent Overtime as a Line
    
  • Check Secondary Axis for the percent line
    
• Rename the chart title to something like:
  
  • Technician Overtime Rate by Month
    
• Optional: Add data labels
  
  • Use the chart “+” icon
    
  • If labels are noisy, add labels to only the line series and drag labels out of the way if they overlap.
    

8. Build the technician-by-month table

• Create a new section with:
  
  • Technician names down the left
    
  • Months (January–December) across the top
    
• Generate the technician list:
  
  • =UNIQUE(Table[Technician])
    
• Copy and paste values using Ctrl + C, then Ctrl + Shift + V to remove the formula.
  
• Fill months across the header row by typing January and dragging right.
  

9. Calculate technician overtime rate with one formula

• In the first technician-month cell, calculate:
  
  • Sum of Overtime Hours for that technician and month
    
  • Divided by Sum of Paid Hours for that same technician and month
    
• Structure (conceptually):
  
  • =SUMIFS(Table[Overtime Hours], Table[Month], MonthCell, Table[Technician], TechCell) / SUMIFS(Table[Paid Hours], Table[Month], MonthCell, Table[Technician], TechCell)
    
• Format as percent using Ctrl + Shift + 5.
  

10. Fix referencing so the formula can fill across and down

• When dragging, month should change as you move across, but technician should stay locked to the row.
  
• Apply absolute references:
  
  • Lock the technician column (add $ before the column letter)
    
  • Lock the month row (add $ before the row number)
    
• Important: Instead of dragging the formula across (which can cause Excel to shift table column references), copy and paste:
  
  • Copy the correct formula cell
    
  • Paste across the row and down the table using Ctrl + V
    

11. Add a benchmark-driven heat map with Conditional Formatting

• In a separate cell, type a benchmark such as 25%.
  
• Highlight the technician-month table quickly:
  
  • Click the first cell in the grid
    
  • Press Ctrl + Shift + Right Arrow
    
  • Keep holding and press Ctrl + Shift + Down Arrow
    
• Go to Home → Conditional Formatting → Highlight Cells Rules → Greater Than
  
• Click the benchmark cell as the threshold.
  
• Choose a red fill (or your preferred style).
  
• Now any technician-month overtime rate above the benchmark highlights automatically.
  

12. Show “Benchmark: 25%” as text while keeping the value numeric

• Type the benchmark value (for example 25%).
  
• Press Ctrl + 1 to open Format Cells.
  
• Choose Custom and start from a percentage format.
  
• Add a text label using quotes, for example:
  
  • “Benchmark: “0%
    
• This keeps the cell numeric (so conditional formatting works) while displaying the label.