Full Project in Excel with Interactive Dashboard | Excel Project | Excel Project from Start to End

The video introduces a step-by-step guide to building a dynamic and interactive Excel dashboard, which is presented as the final output. The dashboard is designed to analyze road accident data for 2021 and 2022, featuring filters for rural/urban areas and accident dates. The tutorial will cover data cleaning, processing, analysis, visualization, and report generation.

This section details the client's requirements for the road accident dashboard, including primary KPIs like total casualties by accident severity and vehicle type, and secondary KPIs such as casualties by vehicle type and monthly trends. It also outlines the key stakeholders who would benefit from this dashboard, including the Ministry of Transport, police force, emergency services, and the public.

The video provides metadata for the dataset, which includes 307,000 rows and 21 fields. The first major step is data cleaning, which involves adjusting column widths, adding filters, and checking for missing values, typo errors, and duplicates. A specific example of correcting a typo ('fetal' to 'fatal') in the 'Accident Severity' column is demonstrated.

This part focuses on data processing to create customized columns for months and years from the 'Accident Date' field using Excel's TEXT function. These new columns are essential for analyzing monthly trends as per client requirements. The process ensures data integrity and prepares the dataset for further analysis.

The tutorial quickly moves into data analysis and visualization by creating pivot tables to calculate key performance indicators (KPIs). It demonstrates how to create donut charts for 'Fatal,' 'Serious,' and 'Slight' accident severities, showing both total casualties and their percentage distribution. This involves extracting values from pivot tables, performing calculations, and formatting the charts for the dashboard.

A new sheet is created and named 'Dashboard.' The layout is designed by removing gridlines, setting a custom background color, and adding various shapes to serve as containers for KPIs and charts. The video shows how to adjust shape sizes, corner radii, and colors to match the dashboard's aesthetic.

The total casualties KPI is added to the dashboard using a text box linked to the extracted value from the pivot table. This ensures dynamism. Subsequently, KPIs for 'Fatal,' 'Serious,' and 'Slight' casualties, along with their respective donut charts and percentages, are meticulously placed and formatted on the dashboard, adhering to the chosen color scheme and fonts.

This section explains how to create the fourth primary KPI: maximum casualties by vehicle type. It introduces the advanced concept of using 'calculated items' within pivot tables to group multiple vehicle types (e.g., 'car' and 'taxi' into 'cars') to simplify the data. The process involves creating a new pivot table, defining custom groupings, and then presenting the top vehicle type (cars) with its own donut chart and percentage on the dashboard.

The tutorial demonstrates how to add secondary KPIs representing casualties by different vehicle types (cars, vans, buses, bikes, agricultural vehicles, and others). This includes inserting graphic icons connected to relevant vehicle types and linking text boxes to display the casualty figures dynamically. Icons are sourced from Excel's built-in icon library and external PNG images.

A new pivot table is created for monthly trend analysis, extracting casualties for both the current year (2022) and the previous year (2021). The data is then used to generate a combo chart comparing these trends. The chart is highly customized, with specific colors, markers, line widths, and fonts to ensure visual appeal and clarity on the dashboard.

This part details the creation of a bar chart showing casualties by road type. A new pivot table is used to extract road type and casualty data. The casualty numbers are formatted into 'thousands' for better readability. The bar chart is then designed with specific aesthetic choices, including gradient fill colors and data labels, and positioned on the dashboard.

The video demonstrates how to create a treemap to visualize casualties by road surface conditions. Similar to vehicle types, road surface conditions are grouped using 'calculated items' (e.g., 'wet surface' combines 'flood' and 'wet'). The treemap is formatted with no fill, no line borders, and data labels to effectively present the distribution of casualties on different road surfaces.

Two more donut charts are created to represent casualties by area location (urban/rural) and light conditions (day/dark). Again, 'calculated items' are used to consolidate different dark conditions into a single 'dark' category. These donut charts are styled with gradients and data labels, then integrated into the dashboard layout.

To enhance user interactivity, a timeline filter for 'Accident Date' and a slicer for 'Urban/Rural' conditions are added. The video clearly explains how to connect these filters to all relevant data sheets on the dashboard, ensuring that all charts and KPIs update dynamically. It also demonstrates how to create custom styles for these slicers and timelines to match the dashboard's theme.

The final segment involves adding hyperlinks to icons on the dashboard. These links allow users to navigate between the main dashboard, a 'Data Analysis' sheet (containing all underlying pivot tables for transparency and maintenance), an email option, and an external webpage (Wikipedia page for UK road accidents). This concludes the comprehensive dashboard design and development, showcasing a fully interactive and informative Excel project.