Stage 10: Systems, data and writing · topic 41 of 50

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

Part of the EHS officer roadmap: how to become one and what to learn

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

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.

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.

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.

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.

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.

Back to the whole roadmap

Live jobs

Live Environmental, health and safety (EHS) officer jobs

Roles whose title matches this career path.

All Environmental Health & Safety jobs

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.