How to Track TRIR in Excel and
Spot Safety Risks Over Time

How to Calculate Total Recordable Incident Rate (TRIR) in Excel

1. Set up the summary table

• Start by creating a clean reporting area for the monthly analysis.
• Add columns for:
  • Month
  • Hours Worked
  • Total Incidents
  • TRIR
  • 1 Year Rolling TRIR
  • Claim Cost
  • Target
• Keep the layout simple so the calculations and chart can be built from one place.

2. Create a unique list of months

• Use the UNIQUE function to pull a distinct list of recorded months from the dataset.
• This gives you one row per month for the summary table.
• If the month field is already formatted correctly in the source data, the month list will carry over cleanly.

3. Summarize total hours worked by month

• Use SUMIFS to total all hours worked for each month in the summary table.
• The function matches each month in the summary area to the month field in the source data.
• Copy the formula down so each month shows its full total hours worked.

4. Summarize total recordable incidents by month

• Use SUMIFS again to total recordable incidents for each month.
• This uses the recordable incident field as the value being summed and the month as the matching condition.
• Copy the formula down through the full monthly list.

5. Calculate the monthly TRIR score

• Build the monthly score using the monthly incident total and the monthly hours worked total.
• Copy the calculation down so every month has its own TRIR value.
• This creates the month-by-month trend before smoothing the results.

6. Add the 1-year rolling TRIR calculation

• Create a new column called something like 1 Year Rolling TRIR.
• Once you reach the twelfth month, calculate the rolling result using:
  • the total incidents across the last 12 months
  • the total hours worked across the last 12 months
• Use the SUM function to total both 12-month ranges.
• Then continue copying the rolling calculation downward so each new row reflects the most recent 12-month period.
• This helps smooth large month-to-month swings.

7. Add monthly claim cost

• Use SUMIFS to total claim cost by month.
• This is not required to calculate TRIR, but it adds extra visibility to the report.
• If the source data is stored in an Excel Table, you can reference the table columns directly, which makes the formula setup cleaner and easier to manage.

8. Add the benchmark or target line

• Create a Target column and enter the benchmark value in the first row.
• Then link each row below to the cell above so the same target repeats all the way down.
• This creates a consistent benchmark line that will update automatically in the chart.

9. Format the summary table

• Apply consistent number formatting to each column.
• Keep hours worked as whole numbers or standard numeric values.
• Format claim cost as currency.
• Format the benchmark and rolling values consistently so the chart reads clearly.
• Adjust column widths so the table is easier to review.

10. Build the combo chart

• Highlight the Month column, Hours Worked, 1 Year Rolling TRIR, and Target.
• Hold Ctrl while selecting non-adjacent columns if needed.
• Go to Insert and choose a Combo Chart.
• Set:
  • Hours Worked as a column series
  • 1 Year Rolling TRIR as a line series
  • Target as a line series
• Put the rolling TRIR and Target on the secondary axis so they are not compressed by the larger Hours Worked values.

11. Adjust the target scale if needed

• Make sure the target is entered as the correct numeric value rather than as a percent if that causes the chart scale to display incorrectly.
• Match the formatting between the rolling TRIR line and the target line so they can be compared clearly.
• This makes it easier to see when performance stays above or below the benchmark.

12. Clean up the chart formatting

• Rename the chart to reflect the KPI clearly.
• Adjust line thickness, colors, or dash style to make the benchmark easier to see.
• Change label formatting if needed to improve readability.
• You can also update the column and line colors to better match the rest of the dashboard.

13. Review the final output

• The columns show total hours worked by month.
• The rolling line shows the smoothed 12-month trend.
• The benchmark line shows whether the business is performing above or below target.
• Together, this creates a clear monthly TRIR view that is easier to interpret than raw incident counts alone.