Road Accidents Dashboard Using Excel

---

Introduction

Through this project, I aim to create a dashboard using Excel, based on predefined KPIs mentioned by the client. I completed this assignment as part of an online workshop conducted by Data-with_Swapnajith. The project involves accepting raw data, which is then processed, cleaned, analyzed, and visualized with granularity. It includes dynamic features like slicers and drop-downs, making the report understandable even to stakeholders with zero technical knowledge.

Data And Software

The data we have used here is fictional and has no privacy concerns. It was generated using: Mockaroo - Random Data Generator and API Mocking Tool | JSON / CSV / SQL / Excel within a time frame from 2021 to 2022.

There are 3.07 million rows(observations) & 21 columns(Features) in this data. and it is in xlsx format.

Data

I have used Excel Version: 2046(Build 17726.20160 )

Client Requirements

Primary KPIs:

• Total Number of Casualties: This measures the overall number of people injured or killed in accidents.

• Casualty Breakdown by Severity and Vehicle Type: This analyzes the distribution of casualties across different severity levels (e.g., minor, serious, fatal) and vehicle types (e.g., car, truck, motorcycle) to identify high-risk groups.

Secondary KPIs:

• Casualties by Vehicle Type: This provides a more detailed breakdown of casualties specific to each vehicle type.

• Year-over-Year Casualty Trend: This tracks changes in the total number of casualties over time, allowing for comparisons between the current year and the previous year.

• Maximum Casualties by Road: This identifies specific roads with the highest number of casualties, which can be prioritized for safety improvements.

• Distribution of Casualties by Road Type: This analyzes how casualties are distributed across different road types (e.g., highways, rural roads, urban streets) to understand potential risk factors associated with each type.

• Distribution of Casualties by Road Surface: This examines the distribution of casualties based on road surface conditions (e.g., wet, dry, damaged) to identify potential correlations between surface conditions and accidents.

• Casualties by Area/Location and Time of Day: This explores the relationship between the location and time of day (day/night) when accidents occur and the number of casualties. This can help identify high-risk times and areas for targeted interventions.

Data Cleaning & Pre-processing

Here's a rewrite of the instructions with some improvements:

1. Import Data:

• Import your data into Excel. This can be done through various methods like opening a CSV file, pasting from another source, or using the "Get External Data" feature.

2. Set Data Types:

• Go to the "Home" tab and locate the "Data Types" section.

• Click the arrow in the bottom right corner of the section. It expands a menu with data type options.

• Select each column header and choose the appropriate data type from the menu:

• Accident_Index, Junction_Control, Junction_Detail, Accident_Severity, Light_Conditions, Local_Authority_(District), Carriageway_Hazards, Police_Force: Text

• Time: Time (24h)

• Accident Date: Date (choose or customize the format to "dd-mm-yyyy")

• Speed_limit, Number_of_Vehicles, Number_of_Casualties: Whole Number

• Latitude, Longitude: Decimal Number

3. Remove Duplicates :

• If you want to remove duplicate rows based on the "Accident_ID" column:
  

• Select the entire data set.

• Go to the "Data" tab.

• Click on "Remove Duplicates."

• Ensure "Accident_ID" is checked in the "Select duplicates to remove" section.

• Click "Remove Duplicates." (This step is optional if you don't need to remove duplicates.)

4. Filter and Clean Data:

• Go to the "Home" tab.

• Click the filter drop-down arrow on each column header.

• Use the filter options to check for:

• Typographical errors: Look for inconsistencies in spelling or capitalization.

• Null/Blank values: Identify and decide how to handle these. You can choose to:

• Leave them blank (if the number is minimal compared to the total data).

• Fill them with appropriate values (if there's a clear pattern).

• Delete the rows containing them (if they significantly impact the analysis).

• After reviewing each column, you can choose to keep or remove rows based on your filtering criteria.
  

• In the case of "Road_Surface_Conditions," "Road_Type," and "Weather_Conditions," you've decided to eliminate rows with blank values because they represent a small portion of the data. This is a valid approach depending on the context of your analysis. However, consider if there might be a more suitable way to handle these missing values, such as imputing them with average values from other entries.

• [ Accident_Severity] column It seems there was a typo error where "fetal" and "fatal" were found as unique values. To correct this, we selected the column, pressed Ctrl+F, and used the find and replace function to change "fetal" to "fatal." Then, we clicked "Replace All." 49 errors where treated.

Adding Month and Year for Visualization:

1. Insert New Columns:

• Click on the cell to the right of your "Accident Date" column.

• Right-click and choose "Insert" to create two new blank columns.

1. Extract Month:

• In the first new column header, type "Accidental_Month" (or your preferred name).

• In the first cell below the header (e.g., cell C2), enter the formula =MONTH(B2). This extracts the month number from the date in cell B2.

• Double-click the small square at the bottom right corner of cell C2. This fills the formula down the entire "Accidental_Month" column, extracting the month for each accident date.

1. Extract Year:

• In the second new column header, type "Accidental_Year" (or your preferred name).

• In the first cell below the header (e.g., cell D2), enter the formula =YEAR(B2). This extracts the year from the date in cell B2.

• Double-click the small square at the bottom right corner of cell D2. This fills the formula down the entire "Accidental_Year" column, extracting the year for each accident date.

Data Analysis Based on KPIs

1. Create a PivotTable for Accident Severity Distribution:

• Select the Data: Highlight your entire data set (including headers).

• Create the PivotTable: Go to the "Insert" tab and click "PivotTable." Choose "New Worksheet" or an existing sheet for the PivotTable.

2. Analyze Accident Severity:

• Drag "Number_of_Casualties" to Values: This will initially show the total number of casualties.

• Drag "Accident_Severity" to Rows: This will group casualties by severity (Fatal, Serious, etc.).

3. Create Donut Charts for Each Severity:

• For Each Severity:

• In a separate area of your worksheet, create labels for each severity level (e.g., "Fatal Severity").

• Use the GETPIVOTDATA function:

• In the cell next to the label, enter the formula:=GETPIVOTDATA("Number_of_Casualties",$A$9,"Accident_Severity","<Severity Name>") (replace <Severity Name> with "Fatal," "Serious," etc.).

• This formula retrieves the total number of casualties for the specific severity from the PivotTable.

• Calculate Percentage:

• In the next cell, calculate the percentage of casualties for that severity:=([Previous Cell Value] / Total Number of Casualties) * 100 (replace "Total Number of Casualties" with the actual cell containing the total).

• Repeat for all Severity Levels.

4. Create Donut Charts:

• Select the range of cells containing the severity labels, total casualties for each severity, and their corresponding percentages.

• Go to the "Insert" tab and choose "Recommended Charts" or "Other Charts" -> "Doughnut."

• This will create a donut chart for each severity level, visually representing the distribution of casualties.

5. Create a Time Series for Accident Dates :

• Go to the PivotTable Analyze tab (appears when you click anywhere within the PivotTable).

• Click "Insert Timeline."

• In the "Insert Slicer" window, select "Accident_Date" and choose the desired layout.

• This creates a timeline slicer allowing you to filter the PivotTable by date range.

6. Total Casualties by Vehicle Type with Grouping

In your PivotTable:

• Ensure "Accident_Severity" remains in the Rows section.

• Drag "Vehicle_Type" to the Columns section.

Create Vehicle Type Groups :

• Right-click on any cell within the "Vehicle_Type" field in the PivotTable.

• Select "Field Settings."

• Click on the "Show Items & Sets" tab.

• Here, you can create custom groups for vehicle types. For example:

• Click "New" to create a new set.

• Name the set (e.g., "Cars").

• In the "Add Items to Set" section, check the boxes next to "Car" and "Taxi/Private hire car."

• Repeat this process to create other sets like "Motorcycles" (combining all motorcycle types), "Buses" (combining minibus, bus, and coach), "Goods Vehicles" (combining all goods vehicle types), and "Others" (combining cycle, horses, and others).
  

7. Number of Casualties by Time:

• Create a new PivotTable or use an existing one.

• Drag "Number_of_Casualties" to the Values section.

• Drag "Accident_Date" to the Rows section. (This will create a table showing casualties for each date).
  

• You can further group dates by:

• Right-click on "Accident_Date" in the Rows section.

• Select "Group" -> "By Months" (or "By Quarters," "By Years," depending on your desired time granularity).

• To visualize trends over time, you can create a chart from this PivotTable. Select the data and choose a line chart or other suitable chart type.

8. Number of Casualties vs. Road Type:

• Create a new PivotTable.

• Drag "Number_of_Casualties" to the Values section.

• Drag "Road_Type" to the Rows section.

This will show the total number of casualties for each road type (e.g., highway, rural road).

9. Number of Casualties by Road Condition:

• Create a new PivotTable.

• Drag "Number_of_Casualties" to the Values section.

• Drag "Road_Surface_Conditions" to the Rows section.

This will show the total number of casualties for each road surface condition (e.g., wet, dry, damaged).

10. Number of Casualties by Urban/Rural Area:

• Create a new Pivot Table.

• Drag "Number_of_Casualties" to the Values section.

• Drag "Urban_or_Rural_Area" to the Rows section.

This will show the total number of casualties in urban and rural areas.

11. Number of Casualties by Area:

• Create a new PivotTable.

• Drag "Number_of_Casualties" to the Values section.

• Drag "Local_Authority_(District)" to the Rows section. (This will show casualties for each district).
  

• If your "Area" has more granular details (e.g., specific city names within districts), you can use that field instead for a more detailed breakdown.

Visualization

Here's how to create donut charts showing fatal, slight, and severe casualties compared to the total using PivotTables and charts in Excel:

1. Create a PivotTable:

• Highlight your entire data set (including headers).

• Go to the "Insert" tab and click "PivotTable." Choose "New Worksheet" or an existing sheet for the PivotTable.

2. Analyze Accident Severity:

• Drag "Number_of_Casualties" to the Values section. This will initially show the total number of casualties.

3. Separate Severity Data (Optional):

This step is optional if you already have separate "Fatal," "Serious," and "Slight" columns in your data. If not, you can create calculated fields within the PivotTable:

* Right-click anywhere within the PivotTable.
* Select "Change Source Data."
* Click on "Calculated Fields."
* Click "New."
* Name the field (e.g., "Fatal Casualties").
* In the "Formula" box, enter the formula to filter casualties based on severity:
    ```excel
    =IF([Accident_Severity] = "Fatal", [Number_of_Casualties], 0)
    ```
* Click "OK" to create the field.
* Repeat this process to create separate calculated fields for "Serious Casualties" and "Slight Casualties" using appropriate formulas in the IF statement.

4. Create Donut Charts for Each Severity:

• For Each Severity:

• Create a label for the severity level in a separate area of your worksheet (e.g., "Fatal Casualties").

• Use the GETPIVOTDATA function:

• In the cell next to the label, enter the formula (adjust based on your field names): Excel =GETPIVOTDATA("Fatal Casualties",$A$9)

• This formula retrieves the total number of casualties for that specific severity from the PivotTable (or calculated field).

• Calculate Total Casualties (if not already available):

• Find a cell containing the total number of casualties (might be a total in the PivotTable or a separate calculation).

• Calculate Percentage:

• In the next cell, calculate the percentage of casualties for that severity: Excel =([Severity Cell Value] / Total Casualties) * 100

• Repeat for all Severity Levels:

5. Create Donut Charts:

• Select the range of cells containing:

• Severity label (e.g., "Fatal Casualties").

• Severity total count from PivotTable or formula.

• Percentage of total casualties for that severity.

• Go to the "Insert" tab and choose "Recommended Charts" or "Other Charts" -> "Doughnut."

• This will create a donut chart for each severity level, visually representing the proportion of fatal, slight, and severe casualties compared to the total.
  

• Ensure your PivotTable layout reflects your needs. You might need to adjust which fields are in the Rows and Values sections.

• If you created calculated fields, modify the GETPIVOTDATA formula accordingly with the specific field names in your PivotTable.
  

6. Multi-Line Chart for Casualties by Month (2022 vs. 2021):

• Assuming your PivotTable has "Accident_Year" in Columns and "Accident_Month" in Rows:

• Select the data range for casualties in both years (e.g., all cells showing monthly casualty counts for 2022 and 2021).

• Change Chart Type:

• Go to the "Chart Design" tab (appears when you select the chart).

• Click "Change Chart Type" in the "Chart Type" group.

• Select "Line" -> "Stacked Line" (or "Line" -> "Line" if you prefer separate lines for each year).

• Filter by Year :

• You can add slicers to your chart to filter by year and easily compare monthly trends.

• Go to the "PivotTable Analyze" tab.

• Click "Insert Slicer" and choose "Accident_Year."

• This lets you isolate casualties for 2022 or 2021 on the chart.

7. Bar Chart for Casualties by Road Type:

• Assuming your PivotTable has "Road_Type" in Rows and "Number_of_Casualties" in Values:

• Select the data range showing road types and corresponding casualty counts.

• Change Chart Type:

• Go to the "Chart Design" tab.

• Click "Change Chart Type" in the "Chart Type" group.

• Select "Column" -> "Clustered Column" (or another column chart type you prefer).

8. Treemap for Road Surface and Casualties:

• Assuming your PivotTable has "Road_Surface_Conditions" in Rows (or a hierarchy) and "Number_of_Casualties" in Values:

• Select the data range showing the hierarchy of road surface conditions and their casualty counts.

• Change Chart Type:

• Go to the "Insert" tab.

• In the "Charts" group, click the dropdown arrow under "Recommended Charts" or navigate to "Other Charts."

• Select "Treemap."

9. Pie Chart for Rural vs. Urban Accidents:

• Assuming your PivotTable has "Urban_or_Rural_Area" in Rows and "Number_of_Casualties" in Values:

• Select the data range showing "Urban" and "Rural" casualties.

• Change Chart Type:

• Go to the "Chart Design" tab.

• Click "Change Chart Type" in the "Chart Type" group.

• Select "Pie."

10. 3D Pie Chart for Accident Area:

• Assuming your PivotTable has "Local_Authority_(District)" (or your area field) in Rows and "Number_of_Casualties" in Values:

• Select the data range showing casualties by area (district).

• Change Chart Type:

• Go to the "Chart Design" tab.

• Click "Change Chart Type" in the "Chart Type" group.

• Select "Pie" -> "3D Pie."

Dashboard

• Insert a New Sheet: Dedicate a new sheet for your dashboard layout.

• Heading:

• In cell A1 (or your preferred location), enter the title of your dashboard (e.g., "Road Accident Analysis Dashboard").

• Format the title with a larger font size and bold text for prominence.

• Total Casualty Count:

• In another cell (e.g., B1), use the COUNTA function to count the total number of casualties in your data set (excluding headers). For example, =COUNTA(B2:B<large number>) (adjust the range based on your data).

• Format the cell to display the count as a large, prominent number.

• Slicers:

• Go to the "Insert" tab.

• Click "Slicer."

• Select the field you want to filter by (e.g., "Accident_Date").

• Repeat for other desired filtering fields (Urban/Rural Area, Road Type, Area, Vehicle Type, etc.).

• Position the slicers strategically on your dashboard for easy access.

• Pivot Charts:

• Create separate PivotTables for each visualization you want to display. Refer to previous explanations (steps 1-5) for creating PivotTables showcasing various aspects like:

• Multi-line chart (2022 vs. 2021 monthly casualties)

• Bar chart for casualties by road type

• Treemap for road surface and casualties

• Pie chart for accidents in rural vs. urban areas

• Donut charts for fatal, slight, and severe casualties compared to the total

• 3D pie chart on accident area (district)

• Arrange the PivotCharts on your dashboard, considering the suggestions mentioned earlier (top row, left and right columns).

• Icons for Vehicle Types:

• Find suitable icons representing different vehicle types using online resources or your own designs.

• Insert the icons in the section displaying the number of casualties using each vehicle type.

• You can create a table structure with vehicle type names, casualty counts, and corresponding icons for better organization.

• Linking Slicers and Charts:

• Right-click on any PivotChart.

• Select "PivotTable Analyze."

• Click "Change Report Filter."

• Check the box next to each slicer you want to link to this chart.

• Repeat for all PivotCharts, ensuring all slicers affect all charts consistently.

• Containers (Optional):

• Use the "Drawing Tools" tab to create rectangles with rounded corners.

• Position them around related elements (e.g., slicers for date and area) to visually group them.

• Formatting and Design:

• Use a consistent color scheme throughout the dashboard (consider color-coding related charts).

• Apply the same font style for headings, labels, and data text.

• Adjust chart and text box sizes for optimal readability.

• Use white space effectively to avoid overwhelming visuals.

• Additional Considerations:

• Ensure all charts have data labels (values) displayed clearly.

• Consider including legends for charts with multiple data series.

• If your dashboard becomes cluttered, explore alternative layouts or prioritizing the most crucial visualizations.
  

• Explore Excel's chart formatting options to customize the appearance of your charts.

• Consider using conditional formatting to highlight critical data points.

• Save your dashboard as a template for future use with updated data.