Blog · Board and management reporting · Manufacturing
Build a safety dashboard in Excel from aggregate site reports. This template compares recorded incidents, exposure hours and overdue corrective actions, with formulas and checks for each denominator.
A safety dashboard in Excel compares recorded cases with exposure hours and shows which corrective actions remain overdue. Build it from approved aggregate site reports, then check the inputs before adding charts. Counts, denominators and reporting cutoffs belong beside every rate.
Use separate sheets called Inputs, Dashboard and Checks. The Inputs sheet holds values copied from approved site summaries; the Dashboard sheet holds formulas; the Checks sheet records differences against source totals. Keep individual names, medical details and investigation narratives out of this management workbook.
The broader safety dashboards guide explains the reporting boundary. This template concentrates on cells and reusable checks. Confirm that cases and hours cover the same workforce, locations and dates before entering them.
Use these columns on Inputs, starting with headers in row 1:
| Column | Field | Entry or formula in row 2 |
|---|---|---|
| A | Site | Ohio |
| B | Approved recordable cases | 3 |
| C | Hours actually worked | 240,000 |
| D | Open actions at cutoff | 12 |
| E | Overdue open actions | 3 |
| F | Cases per 200,000 hours | =IF(C2>0,B2/C2*200000,NA()) |
| G | Overdue share of open actions | =IF(D2>0,E2/D2,NA()) |
| H | Period ending | June 30, 2026, stored as a date |
For this example, the incident window is April 1 through June 30, 2026, and the action snapshot is June 30, 2026. Each site has one row. Store a reference to the approved report beside the table so later revisions can be identified.
The standard base is 200,000 hours: 100 employees multiplied by 40 hours and 50 weeks. Oregon OSHA's recording guide describes the total case incidence calculation using recordable cases and hours actually worked. Use classifications already approved by the responsible team.
Recorded case rate = Approved recordable cases / Hours actually worked × 200,000
Overdue action share = Overdue open actions / All open actions
Here is the complete input and output table for a US manufacturer's quarter. Rates use two decimal places; action percentages use whole percentages. Keep full precision inside formulas.
| Site | Recordable cases | Hours worked | Open actions | Overdue | Case rate | Overdue share |
|---|---|---|---|---|---|---|
| Ohio | 3 | 240,000 | 12 | 3 | 2.50 | 25% |
| Georgia | 2 | 160,000 | 8 | 4 | 2.50 | 50% |
| Nevada | 1 | 100,000 | 5 | 1 | 2.00 | 20% |
| Total | 6 | 500,000 | 25 | 8 | 2.40 | 32% |
Ohio's calculation is 3 / 240,000 × 200,000 = 2.50. The company calculation is 6 / 500,000 × 200,000 = 2.40. Compute the total directly from Inputs rows 2 through 4:
=SUM(Inputs!B2:B4)/SUM(Inputs!C2:C4)*200000
=SUM(Inputs!E2:E4)/SUM(Inputs!D2:D4)
The second formula returns 8 / 25 = 32%. Georgia has the same recorded case rate as Ohio but a higher overdue share. That supports a question about its action backlog; it does not explain why its incidents occurred.
Put the selected period ending in Dashboard cell P1 and site names in column A. If Inputs later contains several quarters, aggregate only the selected period. For a site's case count in Dashboard B2, use:
=SUMIFS(Inputs!$B$2:$B$1000,Inputs!$A$2:$A$1000,$A2,Inputs!$H$2:$H$1000,$P$1)
Change the first range to column C for hours, D for open actions and E for overdue actions. Microsoft documents SUMIFS as adding values that meet multiple criteria. The site and period ranges must align with the values being summed.
Calculate the rate after aggregating those inputs. Do not sum source rates. Also exclude pasted subtotal rows, which would count the same site twice. Use an explicit list of expected sites so a missing report cannot disappear from the dashboard unnoticed.
Show a site comparison table, a case-rate trend with exposure underneath and an overdue-action table. Keep chart scales consistent between periods. The quality dashboard in Excel uses production units for its denominator, so keep its rate definitions separate even when both reports reach the same plant meeting.
The exposure-weighted rate must equal the combined rate:
=SUMPRODUCT(Inputs!C2:C4,Inputs!F2:F4)/SUM(Inputs!C2:C4)
It returns 2.40. The ordinary average is 2.33 after rounding, because it gives Nevada the same weight as Ohio. That is a different calculation.
Tie the six cases to approved site summaries, the 500,000 hours to the matching hours reports, and the 25 open actions to the cutoff snapshot. Check that overdue actions never exceed open actions. Microsoft's IF documentation supports returning a different result when a logical condition fails; use a visible exception rather than a reassuring zero when exposure is missing.
Give each KPI an owner who can explain a source revision. Keep the original cutoff and the revised report reference when a correction changes a previously issued dashboard.
Covirage is not affiliated with Microsoft.
Bring a file of aggregate site cases, exposure hours and action counts with their periods and cutoff dates. Covirage maps the columns, its tools compute and check the supplied figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own report.
See Covirage for industrial manufacturers for the wider operating context.
Use an inputs sheet for approved aggregate site reports, a dashboard sheet for calculated comparisons and a checks sheet for reconciliations. Keep the reporting period and action cutoff visible. Retain source report references so a reviewer can trace a number without importing personal incident details.
Calculate the combined rate from total cases divided by total hours, using the same rate base. An ordinary average gives every site equal weight even when exposure differs. Keep site rates alongside the total so a large site does not hide smaller locations.
For this template, an action is overdue when it remains open at the reporting cutoff and its due date is earlier than that cutoff date. Use the same closure rule across sites, and show missing due dates separately rather than treating them as on time.
No. The responsible safety team supplies approved classifications and aggregate counts. The workbook applies arithmetic to those inputs and reports action status. It cannot establish that classifications are correct, that every incident was reported or that a site meets regulatory requirements.