Bike Sales Dashboard Using Excel

Introduction

This project focuses on Excel and using pivot tables to create visualizations and an interactable dashboard to analyze a bike sales dataset. The dataset being used shows customer data for a bike company, with information such as the customer's marital status, gender, income, education, commute distance, region, and age, and most importantly, if they purchased a bike. The goal of this project is to use Excel to visualize the dataset and create a dashboard to gain insights into sales history for bike customers, and to see if any customer demographics contribute to the purchase of a bike, such as their age or distance for their commute to work. If you download the Excel project, you will see 4 spreadsheets that make up the project. The first sheet is the raw data, the second is the cleaned data, the third are the pivot tables and visualizations, and the final sheet is the dashboard. Below I have shared several screenshots of the project, as well as an interactable version where you can explore the project yourself. Enjoy and thanks for checking it out!

Cleaning the Data

This is the raw data for this project. You can see the dataset includes different information on customers for a bike shop, most importantly it notes if they purchased a bike or not. With this data, I am going to try and create some visualizations that will look at different factors and their possible connection to whether a customer will purchase a bike or not. This will hopefully provide valuable insights, and drive marketing and other business decisions for the owner of the bike shop.

To start the cleaning process, I checked for and removed any duplicate data in the dataset. There were a total of 26 duplicate rows, all of which I removed. I also checked for any null values in the dataset, which is had none of.

This is the cleaned data, after removing duplicates and checking for null values, I went through and clarified some of the data. At a glance, it was a little confusing to see what the M, F, and S meant in the second and third columns of the original data, so I replaced those values to make it easier to read the data. Next, I knew I wanted to work with the Age column for my analysis, but I knew an Age Bracket would make a lot more sense than exact ages themselves, so I added another column and assigned one of three categories, depending on if the customer was Adolescent, Middle Aged, or Old.

Creating Visualizations for the Data

Here are two visualizations I created for the dataset using pivot tables. I chose to create both of them because they seemed like two of the most impactful pieces of information that would contribute to someone purchasing a bike. One shows the average income of buyers and separates it by gender, and the other shows the relationship between the count of bikes sold and the commute distance for the customer who purchased them.

Creating the Dashboard

Here is a screenshot of the final dashboard. I combined the finished visualizations on a new sheet and created the dashboard, which shows the average income of buyers, the count of bikes purchased based off commute distance, and the purchase count of bikes based off the age brackets I created. You can use the buttons on the left side of the dashboard to filter the data and have all three visualizations updated based on your filter. For example, you can filter out a region or gender and show the visualizations based off that information. The goal of this project was to create a dashboard, which would be the primary piece the user would interact with, and be able to get different visualizations by filtering and showing the data they were interested in. This dashboard successfully accomplished this goal, while keeping the look and feel of the dashboard simple, clean, and easy to use.

Below, you will find a version of the project you can interact with. This version uses Google Slides instead of Excel, so please keep in mind that the pivot tables and dashboards won't work. Instead, you can download the file and open it with Excel to see the filters you can interact with, like in the screenshot I added above.

Excel Project.xlsx

Thank you for checking out my Excel project! The original dataset was taken from Github and can be found here - https://github.com/AlexTheAnalyst/Excel-Tutorial/blob/main/Excel%20Project%20Dataset.xlsx

Next project

Page updated

Google Sites

Report abuse