UK Road Accident dashboard

Hello everyone, welcome to yet another walkthrough of my Excel interactive dashboard which gives insights about road accidents in the United Kingdom.

Road Accident Excel dashboard

Using Manipulated Data (not official data) found here my process for coming up with this dashboard involved the following processes:

1. Data cleaning
2. Data processing — which involves using formulas and building customized columns.
3. Data Analysis — involving the use of aggregation data
4. Data Visualization
5. Reporting/ Creating a dashboard

Requirements

The client/ stakeholders wish to have a Road Accident Dashboard for the years 2021 and 2022 so that they can have insights on the below requirements:

Primary KPIs

— to show Total Casualties after the accident.

— to show the total % of casualties with respect to accident severity and max casualties by vehicle type.

Secondary KPIs

• To show casualties with vehicle type.
• Monthly trend showing the comparison of casualties for the current and previous year.
• Maximum casualties by road type.
• Distribution of total casualties by road surface.
• Relationship between casualties by area/location and by day/night.

I also had to identify some of the stakeholders whom the data would be relevant for and they included:

• Ministry of Transport
• Road Transport department
• Police force
• Emergency Service Department
• Road safety corps
• Transport operators
• Public
• Media

My data was MetaData within a file extension of xlsx with 3.07 million rows as well as 21 fields.

Data Cleaning

Here I started by organizing the cell size, adjusting columns to ease the view of data, adding filters, check on any missing or misspelled values using the filter columns as well as replacing values.

Data Processing

Based on the requirement I was supposed to show a trendline between the current year and the previous year’s casualties.

The trendline shows in months which required me to add a monthly column.

Data Analysis and Data Visualization

Here is where I created charts and visuals using pivot tables.

I started by working on creating the Primary KPIs

Determining the % of fatalities

Next, I created the doughnut charts for the primary KPIs

Primary KPI charts

I went on to add the KPIs on my dashboard but first I had to create shapes for my KPIs as shown below:

I went ahead to create the 4th KPI for my dashboard which I named Casualties by Car since car accidents had the largest number of casualties.

I decided to group car and Taxi/private hire car into one using calculated items using the syntax

Select Car> Analyze tab> field item sets> calculated item> Name (cars)> formula (= Car + Taxi)> Add

I did the same for Bus/coach and Minibus as well as Goods vehicles and Vans and also for other vehicles, pedal cycles, and ridden horses which I renamed as others as demonstrated below.

My next step was to add Secondary KPIs which included adding images of vehicle types that I downloaded from the internet as well as adding more shapes for the other KPIs.

I then went ahead to prepare a monthly trend for the current year and the previous year by creating another pivot table where I had months in Rows, Year Filters, and the Number of Casualties to Values; after which I filtered the year to 2021, pasted the pivot and filtered for 2022.

Monthly Trend pivot table

Next I created a Combo chart by extracting the values outside the pivot table starting by month and selecting the entire table and inserting a 2D chart, a bar chart to represent casualties by road type and a Tree Map to show Casualties by Road Surface.

I then added a filter panel in form of slicers and connected it to the rest of the dashboard by Report Connection. The timeline was linked to all the other visuals except the monthly trend.

Filter panel for accident date and location

Finally, it was time to work on the side panel which redirects the stakeholders during their navigation on the dashboard.

I created icons for the same such as dashboard, data, Gmail in casethe stakeholders needed to share the dashboard with others, and URL link to direct the stakeholders to where the information about the accidents could be found on the internet.

For the data icon, I had to create another sheet which I named Data Analysis. This will show the various analysis done during the whole project.

I inserted a hyperlink to the data icon in order to direct the stakeholder to the Data analysis sheet.

Inserting hyperlinks to the panel icons

Data Analysis sheet

Finally we have the final dashboard which is very interactive

Final Dashboard

Google Drive: Sign-in

The abovelink is a 1-minute video showing how the dashboard works.