Welcome to my Excel Dashboard project for Elite Bike Store (EBS)! This project was created as part of a data analytics task to help the fictional Elite Bike Store make data-driven decisions by uncovering insights from customer and sales data.
- Project Overview
- Objectives
- Tools Used
- Dataset Summary
- Data Cleaning and Transformation
- Key Metrics and KPIs
- Dashboard Features
- Insights
- Challenges Faced
- Power Query Integration
- Sample Dashboard Screenshot
- How to Use
- Conclusion
- Connect With Me
This Excel dashboard analyzes customer and sales data from a bike store to uncover trends in purchasing behavior and business performance. The dataset includes demographic, lifestyle, and transactional information. Power Query was used for data cleaning and transformation, while PivotTables and interactive visuals were used to create a dynamic dashboard. The project provides insights into customer segments, regional performance, and profitability, supporting data-driven decision-making for the business.
- Clean and transform the dataset using Power Query
- Create calculated fields for deeper insights (e.g., CLV, average purchase frequency)
- Build an interactive dashboard using PivotTables, Slicers, and PivotCharts
- Conduct profitability analysis based on customer attributes
- Use advanced Excel features and visualizations (e.g., heatmaps, spider charts)
- Microsoft Excel
- Power Query
- PivotTables and PivotCharts
- Slicers and Filters
- Excel formulas and functions
The dataset includes the following fields:
- ID: Unique customer identifier
- Marital Status: Single or Married
- Gender
- Income
- Children
- Education
- Occupation
- Home Owner
- Cars
- Commute Distance
- Region
- Age
- Purchased Bike: Indicates whether the customer purchased a bike
Performed using Power Query:
- Removed null or inconsistent entries
- Standardized formats (e.g., income and age fields)
- Created additional columns:
- Income Category
- Age Range
- Commute Distance (Numeric)
- Revenue
- Profit
- Cost per Bike
- Total Bikes Purchased
- Average Customer Income
- Revenue by Region
- Purchase Rate by Marital Status
- Segment-wise Profitability
- Commute Distance vs. Purchase Behavior
The final dashboard includes:
- Interactive filters: Region, Gender, Education, Marital Status, etc.
- PivotCharts and Tables: Displaying segment comparisons and trends
- Calculated Insights
- Advanced Visuals (optional layer):
- Heatmaps showing bike purchase density by attributes
- Spider chart comparing demographics
- Married customers with higher income and shorter commute distances were more likely to purchase bikes.
- The Pacific region showed the highest number of purchases.
- Professionals and homeowners formed the largest share of bike buyers.
- Education level impacted purchasing behavior: Bachelor’s degree holders were the dominant group.
- Cleaning categorical data inconsistencies (e.g., spelling variations)
- Structuring pivot data to support dynamic segment analysis
- Implementing complex calculated fields without bloating performance
Power Query was used to:
- Import and clean the raw dataset
- Transform column formats and calculate new fields
- Allow easy dataset refresh for future updates
- Download the Excel file from this repository
- Open it in Excel (2016 or later recommended)
- Use the slicers and filters to explore customer behavior
- Refresh the data through Power Query for updates
This Excel dashboard project helped visualize and uncover key insights into Elite Bike Store’s customer base. It demonstrates the power of Excel in dynamic reporting and business analytics using advanced features.
I’d love to hear your thoughts or suggestions!
- LinkedIn:(https://www.linkedin.com/in/mercy-jacob/)
- Email: jmercy306@gmail.com