syelding / excel-data-analysis

Excel-Data-Analysis

About this Dataset. It includes customer data on the sales of bikes. The dataset includes customer data in regard to age, gender, marital status, region, and much more. My job was to find if there were trends in the purchasing of bikes.

Cleaning Data I located and removed duplicates. Second, I reformatted the data in two columns to make the data more clearly and universally understandable. In addition, I highlighted each column to check for grammatical errors. Last, for the column titled Age (which had ages ranging from 25-89) to simplify visualizations I created a new column called Age Bracket. In the Age Bracket column I utilized a “Nested If Statement” which means one 'If statement functions inside of another. This allowed me to create a base name for customer of certain ages.

Building Pivot Tables Pivot tables are a great way to quickly summarize values. So in this instance, I wanted to see the average income of buyers and their gender. This pivot table showed that people with more money purchased bikes and that men had higher salaries than women. From there I created a bar graph to display this. In the next pivot table, I selected the newly made Age Bracket column which listed individuals under the age of 31 as adoloscents, older than 31 middle age, and older than 54 as old. There was so a trend where middle age customers were buying the most bikes, so I created a line graph to display this trend. Last, I created a pivot table of customer commute distance by I selecting the commute distance of customers. From there I created a line graph to show the coorelation of commute distance and the purchasing of bikes.

Creating the Interactive Dashboard I copied and pasted all the graphs and created a header. From their I aligned them with my header by adjusting the width and height of the graphs. Next I created 3 slicers ( Marital Status, Region, and Homeownership). I connected the slicers using Excel's Report Connections function so that the slicers would connect to each graph.

Insights of Data Overall, the data here showed that mostly people aged 31 and older purchased bikes and that men salaries were higher which helped them purchase more bikes than women. In addition, the commute distance affected if customers were willing to purchase bikes.