Create a simple Excel Project for Beginners! (English version)
so I have been following this guy for a while, I learned excel from there and he is Alex Freberg or people usually recognize it as Alex_TheAnalyst.
so this project is based on data about bicycle sales, the dataset I got from Alex's youtube link is in the reference section
in this project we are presented with several data sets that display 14 columns of data sets, namely :
1. ID: Describes the purchase ID
2. Marital Status: Describes the marital status
3. Gender: Describes the gender of the buyer
4. Income: Describes the income of the buyer
5. Children: Describes the number of children the buyer has
6. Education: Describes the buyer’s education status
7. Occupation: Describes the occupation of the buyer
8. Home Owner: Describes whether the buyer owns a house or not
9. Cars: Describes how many cars the buyer has
10. Commute Distance: Describes the range of distances that the buyer often travels
11. Region: Describes the country where the buyer lives
12. Age: Describes the age of the buyer
13. Age Bricket: Explains which age range the buyer falls into
14. Purchased Bike: Explains whether the buyer bought a bike or not
if we look at the original data, there will be some differences:
1. there will be some data duplication
Data -> Remove Duplicates -> ok
2. where Marital Status and Gender are difficult to distinguish because the data is abbreviated so it can be confusing, and for age, there will be a lot of Age numbers so we needed Age brackets as the age range is used to help us read the age.
Gender: M = Male, F = Female
became like this.
Which is easier to read, top or bottom?
3. in the income column we change the type from general to currency and replace it with USD and decrease the decimal for easy reading
4. in the commute distance column we change 10+ miles to >10 miles so that when pivoting it can be sorted
finally, we get clean data that we can pivot the table to visualize.
next, we will create a dashboard according to what we want to look for.
should we want to:
1. to know the average income of people who buy/do not buy the bike
First, go back to the datasheet we created earlier, click on the insert tab, select pivot table, and select all parts of our data.
after that we input
Income to Values (default will be totaled / sum, then change it to average)
Gender to rows
Purchased bikes to columns
after that, we make the visualization
click on the table and select the insert tab for Recommended Charts or on the PivotChart Analyze tab and select PivotChart
And the visualization above makes it easier to read the data.
2. to know who buys based on distance
Same as the first part, we go back to the insert tab, choose Pivot Table and select the data we want to make into a pivot table, then input it:
- Commune Distance to rows
- Purchases bike to values (change the data from sum to count)
- Purchases bike to columns
after that make the visualization like the previous instruction
3. know who buys bikes based on age
Same as the first part, we go back to the insert tab, choose Pivot Table and select the data we want to make into a pivot table, then input it:
- Age Bracket to rows
- Purchases bike to values (change the data from sum to count)
- Purchases bike to columns
after that make the visualization like the previous instruction
After we get the visualization we want, it’s time to create a new sheet for the dashboard.
Why do we create a new sheet specifically for the dashboard?
Because most of our clients will only see the results of our data without questioning how we made all of that.
Notice any difference?
That’s right, there is a column on the left that contains data, but it’s not just any data.
It is the data we take from the insert slicer on the PivotChart Analyze tab.
How do we do that?
First, we click on the visualization that we want to slice, then click and choose what we want as the sorter. then click ok and the slicer comes out.
in the event that we want the slicer to be the sorter of all our visualizations, click on our slicer column later in the tab a new slicer will appear. after that click report connections and select all the pivot tables that we created earlier.
voila then the sorter can be used for all visualizations
enough until here, maybe when there is a new project I will try to work on and share it here
Thanks for reading!
