📊 Excited to share my latest Excel project on Road Accident Data and Casualties! 🚗💥
In this interactive Sales Dashboard, I've created a comprehensive view of road accidents for the years 2021 and 2022. The objective was to provide valuable insights to clients and shed light on accident trends. Here's a glimpse of my approach:
🔍 Data Gathering & Cleaning: I imported the CSV file and performed various activities such as applying filters, removing duplicates, fixing errors, and transforming the data into an easily analyzable format.
🔄 Data Processing: To create trendlines for CY and PY casualties, I added Year and Month columns using the TEXT() function on the Date column.
📊 Data Analysis and Visualization ➡ Pivot Table with 📈 Insights Drawn:
⏳ Created Total Casualties Data using Pivot Table in Dashboard. ⏳ Created a Doughnut chart to analyze the Total casualties & its percentage with respect to accident severity. ⏳ Consolidated similar type of vehicle type using Pivot Table analyze option. ⏳ Created a Doughnut chart to analyze the maximum casualties by Vehicle type, which was a Car. ⏳ Inserted different vehicle type icons through Illustration section and Created a data of Total casualties with respect to Vehicle Type in Dashboard. ⏳ Created a Combo 2-D line chart to understand the Monthly trend representing comparison of casualties for CY and PY. ⏳ Created a Bar Chart to know the maximum casualties by Road Type. ⏳ Consolidated similar type of Road surface condition and Created a Treemap chart to understand the Distribution of total casualties through types of Road Surface. ⏳ Consolidated similar Light condition and Created doughnut chart to understand the relation b/w casualties by Area/Location & by Day/Night.
🔍 Enhanced Functionality: I added filter panels and established connections to all the required pivot tables. Hyperlinks and connections were also added to necessary icons for seamless navigation and created a Data analysis sheet to summarize all the pivot table Data for a new user or developer or client to ease their work and gathered information.