Excel Dashboard Design and Development

Overview

• Today's tutorial focuses on the design and development of a dynamic Excel dashboard.
• The final output is a dashboard that addresses a business problem related to road accidents in 2021 and 2022.
• Dashboard features include:
  • Filters (slicers) for urban and rural data.
  • Timeline filter for accident dates.

Project Steps

1. Data Gathering

• Data source: Kaggle (demo data for practice).
• Dataset details:
  • 307,000 rows and 21 fields.

2. Requirement Gathering

• Client requests: Create a road accident dashboard for insights on casualties and vehicle types.
• Primary KPIs requested by the client:
  • Total casualties.
  • Percentage of total casualties by accident severity.
  • Maximum casualties by vehicle type.
• Secondary KPIs:
  • Casualties visualized by vehicle type.
  • Monthly trend comparison of casualties for current and previous years.
  • Casualties by road type and surface.
  • Relationship between casualties by area location and time of day.

3. Data Cleaning

• Adjust column widths and add filters to identify errors.
• Unique identifier: Accident Index (no duplicates or blanks).
• Correct typos (e.g., replacing 'fetal' with 'fatal').

4. Data Processing

• Create additional columns for Month and Year from the Date field using functions.
• Aggregate data using Pivot Tables; visualize with charts.

5. Data Analysis and Visualization

• Build custom charts and format them for visual appeal.
• Create a dynamic, interactive dashboard using preceding steps.

Dashboard Components

Primary KPIs

• Total Casualties: Displayed prominently on the dashboard.
• Casualties by Severity: Displayed in donut charts.

Secondary KPIs

• Casualties visualized by vehicle type with charts.
• Monthly trends displayed, allowing for comparison across years.
• Casualties categorized by road type and surface conditions.

Interactivity

• Slicers allow users to filter data dynamically.
• Timeline allows filtering by years.

Stakeholders Involved

• Ministry of Transport
• Road Transport Department
• Police and Emergency Services
• Public and Media

Final Output

• An interactive dashboard composed of:
  • Dynamic charts.
  • KPIs that reflect current data trends.
  • Filters for detailed insights based on user input.

Conclusion

• This comprehensive project outlines the steps to design an Excel dashboard, emphasizing data cleaning, analysis, and visualization.
• Users should now have a functional dashboard ready for insights into road accidents.