An interactive Road Accident Dashboard built in Microsoft Excel, providing data-driven insights into UK road casualties.
- 📊 Measure total casualties by severity — Fatal, Serious & Slight.
- 🚗 Identify vehicle types contributing most to road casualties.
- 📅 Compare monthly casualty trends between 2021 and 2022.
- 🛣️ Assess road type & surface conditions impact on accidents.
- ✅ Deliver insights to support road safety policy decisions.
- Total Casualties after accidents
- Casualties & % by Accident Severity (Fatal / Serious / Slight)
- Maximum casualties by Vehicle Type
- Casualties by Vehicle Type (Cars, Vans, Bikes, Buses, etc.)
- 📈 Monthly Trend — CY vs PY Casualties
- 🛣️ Casualties by Road Type
- 🌧️ Casualties by Road Surface (Dry / Wet / Snow/Ice)
- 📍 Casualties by Location (Rural vs Urban)
- 🌙 Casualties by Light Condition (Daylight vs Dark)
File: Dataset/Road_Accident_Data.xlsx
Sheets: Dashboard · Monthly Trend · Road Type · Road Surface · DayNight Casualties · Data · KPI · Data Analysis Sheet
| Key Field | Description |
|---|---|
| Accident Date | Date of accident |
| Number_of_Casualties | Casualties per incident |
| Accident_Severity | Fatal / Serious / Slight |
| Vehicle_Type | Cars, Buses, Bikes, Van, etc. |
| Road_Type | Single/Dual carriageway, Roundabout, etc. |
| Road_Surface | Dry, Wet, Snow/Ice |
| Urban_or_Rural_Area | Location type |
| Light_Conditions | Daylight / Dark |
| Metric | Value |
|---|---|
| Total Casualties | 417,883 |
| Fatal Casualties | 7,135 (1.7%) |
| Serious Casualties | 59,312 (14.2%) |
| Slight Casualties | 351,436 (84.1%) |
| Car Casualties | 333,485 (79.8%) |
| 2021 Total | 222,146 |
| 2022 Total | 195,737 |
- Microsoft Excel — Dashboard, Pivot Tables, Charts
- Power Query — Data Cleaning & Transformation
- Pivot Charts — Interactive Visualizations
- Built a fully interactive multi-sheet Excel dashboard with slicers and timeline filters
- Applied Pivot Tables for dynamic KPI calculation across severity, vehicle type, and road conditions
- Designed donut charts, bar charts, and line trend charts within Excel
- Performed YoY comparison analysis (2021 vs 2022) using structured Pivot data
- Practiced professional dashboard UI design with a dark theme in Excel
The dashboard reveals a 12% decrease in casualties from 2021 to 2022 (222K → 195K), with cars accounting for ~80% of all casualties. Single carriageways and dry road surfaces remain the highest-risk conditions. These insights empower stakeholders to prioritise road safety interventions effectively.
- Clone this repository
- Open
Road_Accident_Analysis.xlsxin Microsoft Excel - Navigate between sheets using the bottom tab bar
- Use the Filter Panel (Accident Date & Urban/Rural) to slice data
Feel free to raise an issue or connect via GitHub for questions or suggestions.