Spreadsheets and dashboards for EHS data
Turn incident, audit and action data into rates and charts a manager will act on. Build the workbook in a spreadsheet first and add a dashboard tool only when the same questions come up every month.
- Optional
- Intermediate
- about 12 hours
Written by the GreenHired editorial teamSources last checked 20 November 2026How we research roadmaps
Every pivot, chart and dashboard draws on two clean tables, one of events and one of actions, plus a one-page data dictionary, so a mistake there spreads everywhere. Define the terms first, clean the data next, calculate rates after that, and chart only what answers a decision. A dashboard comes last, and only if someone will open it every month.
A rate built from three cases
Site A logged 3 recordable injuries in a year and 150,000 hours worked. The site, its hours and its injury counts are made up for the example. With the BLS formula from the rates part:
- 3 injuries: 3 x 200,000 / 150,000 = 4.0
- 4 injuries, one more: 4 x 200,000 / 150,000 = 5.33, a rise of a third from a single event
- 2 injuries, one fewer: 2 x 200,000 / 150,000 = 2.67, which looks like progress and proves nothing
At that size, report the count, the rolling 12-month rate and the near misses reported side by side, and write under the chart that the rate rests on a handful of cases. Set it against an industry rate only when the case definitions and the hours base match.
Charts that change a decision, and the source behind them
For a monthly pack to a site manager, three charts are usually enough: overdue actions by owner, injuries by cause from your pivot table, and the rolling rate. Under each, write one sentence that names an action, such as “Overdue actions sit mostly with maintenance: agree new dates by Friday.” If you cannot write that sentence, drop the chart. A falling rate can mean fewer injuries or fewer reports, so show near misses reported beside it; safety performance metrics goes further into leading and lagging indicators, and reports that managers act on covers what to write around the charts.
Merged cells, free-text causes and duplicate rows break pivots quietly. The systems that capture the data in the first place are covered in EHS software tools. Repair the entry form and the data dictionary before you reach for Power Query or a dashboard tool.
What you will learn
- Turn counts of injuries and hours worked into a rate you can compare between months, sites and years.
- Build pivot tables that slice incidents by cause, department, shift and severity.
- Design an action tracker that shows owner, due date and overdue items at a glance.
- Choose three charts for a dashboard and write one sentence under each saying what to do.
- Benchmark against official statistics and say honestly what they cannot tell you.
The parts of this topic
Rates and trends in a spreadsheetabout 2 hours
A raw count of injuries tells you little; a rate per hours worked lets you compare a quiet month with a busy one and one site with another.
Decide what counts as a recordable case before you count anything. OSHA's recordkeeping page says many employers with more than 10 employees must record cases on Forms 300, 300A and 301 or equivalents. BLS gives the rate formula: injuries and illnesses x 200,000 / employee hours worked, where 200,000 is the hours of 100 people working 40 hours a week for 50 weeks. Build monthly and rolling 12-month rates: one injury in a quiet month swings the monthly figure wildly, so chart the rolling line beside it. Never compare sites or countries that define cases differently.
- Recordkeeping OSHA (United States) · Guide Who must record, the forms, and 8-hour and 24-hour notification
Pivot tables for incident analysisabout 2 hours
A pivot table summarises thousands of rows by cause, department or month in minutes, once the data sit in clean columns.
Microsoft's page says your data should be organised in columns with a single header row, then Insert, PivotTable, and drag fields into rows, columns and values. You can switch between sum and count, show values as percentages, and must refresh after the source changes. Pivot incident counts by cause against department, then by month, to see whether one cause keeps appearing in one area. Merged cells and blank rows in the source silently break the table, so fix the source first.
- Create a PivotTable to analyze worksheet data Microsoft Support · Guide Steps, recommended PivotTables, field list and refresh
Action trackers that get actions closedabout 2 hours
A simple list with owner, due date, status and evidence, plus a view of what is overdue, closes more actions than a long report.
HSE's investigation guide ends with an action plan and its implementation, and its risk assessment steps end with reviewing the controls. Give each action one line: source (audit, incident, inspection), owner, due date, status, date closed and a link to evidence. Add conditional formatting for overdue items and report the overdue count every week. Close on verification, not on announcement: an action to fit a new guard stays open until someone checks it was fitted and works.
- HSG245: Investigating accidents and incidents HSE (UK) · Guide Free PDF; action plan and implementation step
Dashboards with Power BI or similarabout 2 hours
A dashboard puts a few indicators on one page and updates when the data refresh; start with three charts, not thirty.
Microsoft describes Power BI as connecting to sources such as Excel, preparing and modelling data, building reports and dashboards with drag-and-drop tools, then sharing them in a cloud service, with a desktop app for building. Check licence terms before you plan around it. Pick three indicators a manager can act on and put a one-line instruction under each: overdue actions, near misses reported and the rolling injury rate would do. Ask the manager which decision the page should change, and delete anything that does not serve it.
- What is Power BI? Microsoft Learn · Guide Desktop versus service, and the step-by-step flow; check licence terms separately
Data quality and definitionsabout 1 hour
Bad data produce confident wrong charts; write definitions down and check entries before you analyse.
HSE's RIDDOR page has one section on which incidents are reportable and another on what records you need to keep. Write your own data dictionary: what counts as a near miss, a first-aid case, a lost-time injury, a hazard report. Each month, check for duplicates, missing dates and impossible values. Example: if a policy change in April starts counting first-aid cases, the trend jumps, and the line shows a new definition, not progress or decline. Mark the change on the chart or keep the old definition in its own column.
- RIDDOR: reporting and record keeping HSE (UK) · Law or regulation Reportable incidents and records to keep; follow the links for detail
Benchmarks and context from official dataabout 1 hour
Compare your rate with national or industry data, but remember that a benchmark reflects other organisations' definitions and size.
HSE publishes ill health, fatal and non-fatal injury and cost data under the Open Government Licence, and says total costs to Britain were around £24.6bn in 2024/25 (as read on 20 November 2026). The BLS publishes industry injury and illness tables, and OSHA's Injury Tracking Application links to publicly available establishment data. An industry rate can open a budget conversation, but your own records make the case. A small site may be too small for its rate to be stable, so show counts beside it.
- Injury Tracking Application (ITA) OSHA (United States) · Dataset Links to publicly available establishment data; check what is published
Simple automation with Power Queryabout 2 hours
Refresh your monthly reports automatically instead of copying and pasting, by importing and cleaning data with saved steps.
Microsoft's page explains that Power Query, called Get and Transform in Excel, imports data from files, databases and web sources, lets you remove columns, change types and filter rows without altering the source, saves each edit as a step, and reruns all steps on refresh. Automation repeats a messy process faster; it does not clean it, so fix the entry form first. Once that is done, twelve monthly incident exports become one clean table that refreshes in one click.
Practice task
Build a workbook from 24 months of fictional incident data containing a monthly and rolling 12-month rate, a pivot by cause and department, an action tracker with overdue highlighting and a one-page dashboard of three charts, each with a one-sentence recommendation.
Skills you build
Excel pivot tablesIncident rate calculationDashboard designData qualityBenchmarking
Resources for this topic
Every source here is free. Each link opens the provider's own site in a new tab.
- Injuries, Illnesses, and Fatalities (IIF) program US Bureau of Labor Statistics · Dataset Annual injury and fatality data, tables and an incidence rate calculator and comparison tool
- HSE statistics HSE (UK) · Dataset Ill health, injuries and costs for Great Britain; Open Government Licence v3.0
- Costs to Britain of work-related injury and ill health HSE (UK) · Dataset About £24.6bn in 2024/25 according to the page
- OSHA $afety Pays Individual Injury Estimator OSHA (United States) · Tool Estimates direct and indirect injury costs; US averages, not a company analysis
Live Environmental, health and safety (EHS) officer jobs
Roles whose title matches this career path.
Spot a problem with this topic?
Tell us what is wrong, missing or unclear here. Your note is attached to this topic.
Notes are sent from this page with JavaScript on. Without it, write to us on the contact page.