Road Accident Analysis

The story behind the work

This independent Microsoft Excel project explored historical road-accident casualty records for 2021 and 2022. The aim was to move from a large, detailed spreadsheet to a dashboard where changes by month, vehicle type and road conditions could be understood quickly without mistaking a dataset summary for live national statistics.

What I set out to do

• Prepare and clean historical accident data for meaningful comparisons.
• Explore casualties by year, month, vehicle type, road type and surface condition.
• Build an Excel-native analytical workflow using Pivot Tables and charts.
• Make filtering interactive through slicers and a timeline control.

From raw data to a useful result

Cleaned and categorized data, used calculated columns, Pivot Tables, charts, slicers and timeline filters in Excel.

• Defined the analysis scope, reporting questions and the categories needed for the dashboard.
• Cleaned inconsistent records and duplicates, and created calculated columns for time and casualty groupings.
• Aggregated the historical data using Excel Pivot Tables and summary calculations.
• Built visual comparisons of casualty counts across vehicles, years, months, roads and surface types.
• Connected charts to slicers and a timeline for interactive exploration inside Microsoft Excel.

What I contributed

Independent Excel data cleaning, analytical summaries and interactive dashboard development.

• Completed the data preparation, calculations and dashboard independently in Microsoft Excel—not Power BI.
• Selected comparable casualty measures across multiple dimensions and built interactive controls around them.
• Added scope-aware interpretation so historic dataset figures are not presented as real-time safety indicators.

What the analysis revealed

• The published dashboard covered 417,883 casualties: 222,146 in 2021 and 195,737 in 2022.
• In the source analysis, cars accounted for 333,485 casualties (79.8%); the “other vehicles” group accounted for 3,424.
• November had the highest monthly casualty count in both years; February 2021 and January 2022 were identified as the lowest months.
• Single-carriageway roads carried the largest casualty count among the compared road types, while slip roads were lowest.
• The original dashboard found many casualties on dry road surfaces. Counts alone do not establish that a road condition causes accidents.