How to create a full Project in Excel with a Dashboard

I made this as a simple activity to validate Excel as a tool to create fast visualizations and quickly work with data.

I start by importing this Bike Sales dataset and by creating the different worksheets

• Working sheet (Most of the the work is done here)
• Pivot Table
• Dashboard

I copy the data set to an additional tab and then I move it to the Working Sheet. Then, I apply a filter tby

• On the Ribbon, In Data Tab: Apply Filter

I notice that the Marital Status Column needs cleaning so I fix it by:

• Data Tab: Remove Duplicates

By doing this I improve data significance: Changing “Marital Status” Columns from “M/S” values to “Married/Single” values.

• Use the Find & Replace tool (CTRL + H)

Afterwards I check for spelling errors using the filters

Age is usually better sorted using brackets. Used a nested IF() Formula to create the conditional loop to check for ages and return the correct age bracket:

The Age Brackets Column output will look like this:

After some cleaning I decided to create a Pivot Table

When creating Pivot Tables, remember to select Only the ranges that the table needs to contain (No blanks)

• I create an “Avg Income Per Purchase” graph by doing the following:
  • Moving fields to the correct areas
  • Gender to rows and Income to Values
  • Change Income to Average
  • Purchased Bike to Column
• With that, I can create a Chart
  • I start with Recommended charts for options
  • I Make the numbers looking nice by changing the format
  • Renamed the Axis Titles
  • Gave it an Chart Title
• The final result looks like the following: