This Excel-based analytics project delivers a comprehensive road accident data analysis using interactive dashboards, Key Performance Indicators (KPIs), and trend visualizations.
The dashboard provides actionable insights into accident severity, road conditions, and causal factors, helping identify high-risk patterns and areas for safety improvement.
This project is ideal for learners and professionals interested in data analysis, Excel dashboards, and transport safety analytics.
📂 Download Full Excel Dashboard (Road Accident Data Project.xlsx)
- Analyze total accidents, casualties, and severity rates.
- Identify high-risk locations, accident types, and surface conditions.
- Examine seasonal and monthly accident trends.
- Compare urban vs. rural road accident distribution.
- Provide interactive visualizations for decision-making.
File: Road Accident Data Project.xlsx
Dataset Name: Road Accident Dataset
Key fields include:
- Date / Time – Accident occurrence details
- Location (Latitude / Longitude) – Spatial mapping points
- Road Type / Surface Condition – Environmental factors
- Weather Conditions – Clear, rainy, foggy, etc.
- Accident Severity – Fatal, serious, or slight
- Vehicles Involved / Casualties – Key outcome measures
- Total number of accidents recorded
- Breakdown of fatal, serious, and slight cases
- Identification of yearly and monthly trends
- Accident rates across different road types
- Impact of surface condition (dry, wet, icy, etc.)
- Comparative insights into urban vs rural environments
- Correlation between weather and accident frequency
- Time-based patterns: peak hours, days, and months
- Geographical hotspots for severe accidents
- Severity mapping using Excel charts and visuals
- KPI Dashboard: Accident counts, casualty rate, and severity KPIs
- Monthly Trend Chart: Visual comparison of monthly and yearly data
- Road Type Analysis: Evaluate accident distribution by road classification
- Surface Condition Insights: Analyze accident probability under different road states
- Dynamic Filters: Use slicers for custom data views
- Interactive Visuals: Donut charts, bar charts, and trend lines
- Pivot Tables & Pivot Charts for summarizing data
- Slicers & Timelines for interactivity
- Conditional Formatting for highlighting severity
- Dynamic Ranges & Named Tables for automation
- Formulas:
COUNTIFS(),AVERAGEIFS(), andVLOOKUP()for insights
- Microsoft Excel (Advanced) – Dashboard creation and analytics
- Data Cleaning & Transformation – Using Power Query and formula logic
- Visualization Tools – Charts, KPIs, and conditional formatting
Below are sample snapshots of the dashboards included in this project:
Here’s a snapshot of the full Road Accident Analysis Dashboard (Excel):
Through this project, you’ll learn to:
- Build interactive Excel dashboards from raw data.
- Apply Pivot Tables, Charts, and Slicers effectively.
- Perform data cleaning and transformation in Excel.
- Generate data-driven road safety insights using visuals.
Potential future developments for this project:
- Integrate Power BI or Tableau for extended analytics
- Automate Data Refresh with Power Query
- Add Geo-Maps for visualizing accident hotspots
- Include Trend Forecasting using Excel’s built-in functions
- Add Demographic Data for deeper insights
- Incorporate Machine Learning Models via Python integration
Kaushic Krishnamurthy G kaushickrishnamurthy@gmail.com Data Scientist | Excel Dashboard Developer | Business Intelligence Enthusiast
This project is open-source and available under the MIT License.
- Dataset inspired by publicly available accident records (for educational use).
- Excel community tutorials and data analysis case studies.