UK Road Traffic Accident Analysis

UK Road Traffic Accidents

Excel — an interactive dashboard and prescriptive analysis of 307,973 UK road traffic accident records, built to identify where road safety interventions should be targeted and why.

Tools & Skills

  • PivotTables & cross-filtering slicers
  • Dynamic KPI cards (linked text boxes, camera tool)
  • Descriptive statistics & data quality auditing
  • Dashboard design & colour system standardisation

Approach

A 307,973-row dataset covering 23 variables and 422 local authorities was audited for coverage, standardised for type and category consistency, then summarised through PivotTables into nine cross-filtering visuals. Four slicers — Year, Urban/Rural, Severity and City — update every chart and KPI simultaneously.

Casualty totals were reconciled against the severity breakdown rather than assumed, and a standardised colour system was applied so that severity always reads as warm tones and volume measures as cool tones.

Key Findings

  • Severity multiplies harm. Fatal accidents produce 1.80 casualties each against 1.34 for slight — a 34% higher human cost per event.
  • Rural roads carry disproportionate fatal risk. Rural areas account for 35.5% of accident volume but 59.4% of all fatal accidents, a 2.7× fatal rate against urban.
  • Unlit roads are a fixable, rural problem. 95% of the 16,528 national no-lighting accidents occur in rural areas.
  • Exposure is not risk. 73.8% of accidents occur in daylight simply because that is when most driving happens — reading this as a danger signal would misdirect spend.

Data Quality Finding

The coverage audit revealed 68 districts recording accidents in 2021 and exactly zero in 2022, almost all Scottish. Glasgow falls from 1,509 to 0; Edinburgh from 1,193 to 0. This is a reporting gap rather than a road safety achievement, and identifying it prevented a data artifact from being presented as evidence of successful policy. All accident-reduction rankings were excluded from the analysis pending source validation.

Outcome

Eight prescriptive recommendations tied to specific figures in the data, including a rural lighting capital programme, a Friday evening enforcement window covering the 17:00 peak, and a proposal to split the national KPI so that accident reduction and casualty reduction are measured as separate goals.