EXCEL DASHBOARD PROJECT
Bike Sales Excel Dashboard
A comprehensive Excel project featuring data cleaning, advanced pivot tables, and an interactive dashboard to analyze customer demographics and purchasing trends.
Project Overview
This project transforms raw sales data into actionable insights. Starting with the "Bike Buyers 1000" dataset, the workflow involves rigorous data cleaning to standardize categorical values, creating a dedicated working sheet for manipulation, and building complex pivot tables.
The final output is a consolidated, user-friendly dashboard that allows stakeholders to explore bike sales trends based on income, commute distance, and age using interactive Slicers.
Workflow Summary
-
Import Load raw CSV into Excel Workbook.
-
Clean Remove duplicates, standardise text, group ages.
-
Analyze Create Pivot Tables for key metrics.
-
Visualize Build interactive dashboard with slicers.
Phase 1: Data Cleaning
1. Standardizing Categorical Data
Raw data contained single-letter abbreviations (e.g., 'M', 'S', 'F') which are ambiguous in final reports. These were transformed into full text descriptors using Find & Replace logic.
| Field | Raw Value | Transformed Value |
|---|---|---|
| Marital Status | M, S | Married, Single |
| Gender | F, M | Female, Male |
2. Creating Age Brackets
To make age analysis more meaningful, individual ages were grouped into quantitative "Age Brackets" using Nested IF functions.
=IF(L2>54, "Old", IF(L2>=31, "Middle Age", "Adolescent"))Phase 2: Pivot Table Analysis
Income vs Purchase
Analyzing if higher income correlates with bike purchases.
Commute vs Purchase
Impact of commute distance on customer conversion.
55% of buyers commute 0-1 Miles
Key Finding: Customers with shorter commutes (0-1 Miles) are significantly more likely to purchase a bike compared to long-distance commuters.
Interactive Dashboard
Final Output with Slicers for Age, Income, and Region.
Outcomes & Final Thoughts
This project demonstrates the end-to-end capabilities of Excel for small-to-medium scale data analysis. By converting raw rows into an interactive visual narrative, I was able to uncover that Middle-aged customers with short commutes are the ideal target demographic for new marketing campaigns.