Full Project in Excel | Excel Tutorials for Beginners

Summary

In this Excel tutorial, viewers are guided through creating a comprehensive project, from data cleaning to dashboard development. The host demonstrates how to use a dataset to build interactive visualizations and filters, providing a step-by-step walkthrough that includes removing duplicates, adjusting data formats, and utilizing pivot tables for analysis. The session culminates in designing a user-friendly dashboard with slicers for demographic filtering, showcasing Excel's capabilities for data analysis and presentation.

Takeaways

• 📈 The tutorial series aims to guide viewers through creating a complete project in Excel, from data cleaning to building an interactive dashboard.
• 🔗 The dataset used in the tutorial is available for download from the instructor's GitHub, allowing viewers to follow along with the exact same data.
• 👀 The initial step in the project is to preview the final dashboard to understand the end goal before diving into the data.
• 🧼 Data cleaning is emphasized, starting with removing duplicates to ensure the accuracy of the dataset.
• 🔢 The script covers the importance of data formatting, such as converting abbreviations into full words for clarity and adjusting number formats for consistency.
• 📊 The creation of pivot tables is a key part of the process, allowing for the organization and summarization of data for visualization.
• 📈 Visualizations like charts and graphs are created from the pivot tables to represent data insights, with a focus on making them clear and interactive.
• 📑 The script discusses the creation of a 'working sheet' to keep raw data separate from the data being actively manipulated and analyzed.
• 📝 The tutorial includes creating age brackets to categorize data, making it more manageable and understandable for dashboard users.
• 🔍 The use of slicers is highlighted for adding interactivity to the dashboard, allowing users to filter data based on specific criteria like marital status or education level.
• 💼 The final dashboard is presented as a valuable tool for portfolio enhancement, showcasing the user's ability to utilize Excel for complex data projects.

Q & A

What is the main focus of the Excel tutorial series video?

The main focus of the video is to guide viewers through creating a complete project in Excel, including data cleaning and building an interactive dashboard.

Where can the dataset used in the video be found?

The dataset used in the video can be found on the instructor's GitHub, with a link provided in the video description.

What types of visualizations are created in the video?

The video creates various visualizations including charts to represent average income based on gender and bike purchase status, customer commute distances, and age brackets related to bike purchases.

How does the video handle data cleaning for the 'gender' and 'marital status' fields?

The video demonstrates using 'Find and Replace' to change abbreviations 'm' for male, 'f' for female, 'm' for married, and 's' for single, to their full names for clarity in the dashboard.

What issue was found with the 'commute distance' data during the video?

The 'commute distance' data was initially in a range format which could become messy in visualizations. The video suggests keeping it for now but indicates a potential future need to change it for better visualization.

How are 'age brackets' created in the video?

The video uses an 'IF' statement to create 'age brackets', categorizing individuals as 'adolescent' if under 31, 'middle age' if between 31 and 54, and 'old' if 55 and above.

What is the significance of the 'purchased bike' column in the dataset?

The 'purchased bike' column is significant as it indicates whether a person bought a bike or not, which is a key metric for the analysis in the video.

How does the video address the issue of duplicates in the dataset?

The video uses the 'Remove Duplicates' feature in Excel to identify and remove any duplicate rows in the dataset, ensuring the data's accuracy.

What is the purpose of creating pivot tables in the video?

Pivot tables are created to organize and summarize the data effectively, which is essential for building the dashboard and its visualizations.

How does the video enhance the dashboard's usability?

The video enhances the dashboard's usability by adding slicers for filtering data based on marital status, region, and education, allowing users to interact with the data and view specific insights.

What is the final recommendation given by the instructor regarding the project?

The instructor recommends that viewers take the project further by adding their own unique elements, and suggests using the completed project as a portfolio piece to showcase Excel skills.

Outlines

00:00

📊 Introduction to Excel Project

The video script introduces an Excel tutorial series where the host will guide viewers through creating a comprehensive project in Excel. The project involves data handling, cleaning, and the creation of an interactive dashboard. The host promises a step-by-step walkthrough and suggests that the project could serve as a portfolio piece or be expanded upon for more complexity. The dataset to be used is mentioned, with a link to be provided in the description for download.

05:01

🔍 Data Overview and Initial Setup

The host provides an overview of the dataset, which includes demographic and behavioral information about individuals related to bike purchases. The script describes the initial setup, including creating a working sheet separate from the raw data for ease of manipulation without altering the original dataset. The dataset fields are briefly explained, emphasizing the importance of the 'purchased a bike' column for analysis in the video.

10:02

🧹 Data Cleaning Process

The script details the process of data cleaning, starting with checking for and removing duplicates to ensure data integrity. It then moves on to standardizing data formats, such as expanding abbreviations for marital status and gender for clarity in the dashboard. The host contemplates changes to the income format but decides to leave it as currency for the time being. Other fields are reviewed for consistency and accuracy, with a focus on usability for dashboard users.

15:03

📈 Creating Age Brackets and Pivot Tables

The host discusses the creation of age brackets to simplify the analysis of age demographics, using conditional formatting to categorize individuals into 'adolescent,' 'middle age,' and 'old' based on their age. Following this, the script describes the creation of pivot tables as a foundational step for building visualizations in the Excel dashboard. The process of selecting data for the pivot table and setting up the initial structure is outlined.

20:07

📊 Analyzing Income and Bike Purchases

The script focuses on using pivot tables to analyze the relationship between income and bike purchases, segmented by gender. It details the creation of a visualization that shows the average income of individuals who did or did not purchase a bike. The host also discusses the process of selecting the right chart type and customizing the visualization for clarity and aesthetics.

25:09

🚴‍♂️ Examining Commute Distance and Purchases

This section of the script examines the correlation between commute distance and bike purchases. The host creates a pivot table to count the number of individuals who bought or did not buy a bike within different commute distance ranges. The script acknowledges a potential issue with the ordering of data and discusses the process of revising the pivot table to better represent the data visually.

30:09

📊 Visualizing Age Brackets and Purchase Behavior

The host discusses creating a visualization to analyze the relationship between age brackets and bike purchase behavior. The script describes the process of using a line chart to represent the count of bike purchases across different age groups. The host experiments with different chart styles and emphasizes the importance of clear and concise visualization for better understanding of the data.

35:11

🛠️ Building and Refining the Dashboard

The script outlines the process of assembling the various elements into a cohesive dashboard. The host discusses the importance of aesthetics, such as removing gridlines and aligning charts for a clean presentation. The process of copying and arranging visualizations on the dashboard is detailed, along with the consideration of color coordination and design consistency.

40:14

🔧 Adding Interactivity with Slicers

The host introduces the concept of adding interactivity to the dashboard using slicers, which allow viewers to filter the data based on specific criteria such as marital status, region, and education level. The script explains how to insert and configure slicers and how to apply them to all visualizations on the dashboard for a comprehensive analysis tool.

🎓 Conclusion and Encouragement to Innovate

In the conclusion, the host reflects on the project's educational value and encourages viewers to explore beyond the tutorial and add their unique touches to the dashboard. The script emphasizes the importance of learning and creativity, and the host expresses gratitude for the viewers' engagement, wrapping up the video with a sign-off.

Keywords

💡Excel

Excel is a widely used spreadsheet program developed by Microsoft. It is integral to the video's theme as the tutorial series focuses on creating a complete project in Excel, from data handling to dashboard creation. The script mentions Excel multiple times, highlighting its functions such as creating pivot tables and visualizations.

💡Dashboard

A dashboard in the context of this video refers to a user interface that presents information in an easy-to-read manner, often used for monitoring and analyzing data. The video's main project involves creating an Excel dashboard, which is exemplified by the script's detailed steps on how to build and customize it.

💡Data Cleaning

Data cleaning is the process of detecting and correcting (or removing) errors and inconsistencies in a dataset. The script emphasizes the importance of this step before starting the dashboard project, mentioning the removal of duplicates and ensuring data accuracy.

💡Pivot Table

A pivot table is a data summarization tool found in spreadsheet software such as Excel. It allows users to dynamically arrange and analyze data. The script describes creating pivot tables as a key step in organizing the data for the dashboard, which helps in summarizing and understanding patterns.

💡Visualization

Visualization in this video pertains to the graphical representation of data, which helps in understanding complex information quickly. The script discusses creating various types of visualizations using Excel to represent the cleaned data in an interactive and digestible format.

💡Filter

In the context of Excel and the video, a filter is a feature that allows users to view data based on specific criteria. The script mentions creating interactive filters for the dashboard to enable users to view data subsets based on their interests or needs.

💡Duplicate Data

Duplicate data refers to the presence of identical data points within a dataset. The script specifically addresses the removal of duplicate data as a crucial step in the data cleaning process to ensure the accuracy and reliability of the dataset used for the dashboard.

💡Demographic Information

Demographic information includes data about a person's age, gender, income, and other statistical factors. The script discusses using demographic information from a dataset to analyze and visualize the purchasing behavior of bike buyers in the Excel dashboard.

💡Age Brackets

Age brackets are categories into which ages are grouped for easier analysis and visualization. The script describes creating age brackets to categorize the dataset, which is then used in the dashboard to analyze the purchasing patterns of different age groups.

💡Slicer

A slicer in Excel is an interactive tool that allows users to filter data in a pivot table or chart. The script explains how to insert and use slicers to add interactivity to the dashboard, enabling users to view data based on selected filters such as marital status or education.

Highlights

Introduction to creating a complete project in Excel, including data cleaning and dashboard creation.

Potential use of the project for portfolio enhancement or further exploration.

Overview of the dataset used for the project, with a link provided to download the same dataset from GitHub.

Demonstration of the final dashboard's interactive features and visualizations.

Explanation of creating a working sheet in Excel to separate raw data from the work-in-progress.

Initial data assessment, including checking for duplicates and understanding the dataset's demographic information.

Technique to remove duplicates in Excel using the 'Remove Duplicates' feature.

Clarification of gender and marital status abbreviations in the dataset for better dashboard usability.

Discussion on the format of income data and its implications for calculations and visualizations.

Strategy for handling non-numeric data like 'homeowner' and 'cars owned' by converting them into simpler terms.

Introduction of creating age brackets to simplify visualization and analysis of age-related data.

Use of IF statements in Excel to categorize ages into 'adolescent', 'middle age', and 'old'.

Mistake identification and correction regarding the 'marital status' label in the dataset.

Process of creating pivot tables from the cleaned data as a foundation for dashboard visualizations.

Analysis of average income based on gender and bike purchase status using a pivot table.

Visualization creation to compare average incomes and bike purchase decisions.

Discussion on the importance of data presentation, such as removing gridlines and adjusting number formats for clarity.

Design considerations for an effective dashboard, including layout, color coordination, and adding a header.

Incorporate interactive elements like slicers to filter data by marital status, region, and education level.

Real-time adjustment of dashboard elements to improve visual appeal and data interpretability.

Encouragement for users to explore beyond the tutorial and customize the dashboard for their portfolio.

Transcripts

what's going on everybody welcome back

to the excel tutorial series today we're

going to create an entire project in

excel

[Music]

now if you've never done a complete

project in excel where you take the data

you clean it and then you create an

actual dashboard where people can click

on things and filter things this is

gonna be a really great learning

opportunity as well as potentially you

know a simple project that you can use

for your portfolio or you can spice

things up and go a little farther than

what we're gonna be doing in today's

video i will walk you through every

single step of the way and hopefully we

learn something together and without

further ado let's jump right into it

let's jump on my screen and get started

with the project all right so this is

the data set that we're going to be

working with i will leave a link in the

description to my github where you can

go and download it so you can be working

with the exact same data set that i am

using now before we actually get into

this data and start looking at it i'm

going to show you what the final

dashboard is going to look like

we're going to create a few different

types of visualizations nothing too

crazy

and then we'll create some filters as

well so we can kind of you know create

some interactive filters with our data

so let's go right on over to our data

set

now i'm going to

hide this because we are not going to

use that but what i am going to do

before we do anything is i'm going to

create a

dashboard

and i'm going to create a pivot table

oops

and i'm going to create a working sheet

so

all these things have different uses and

i'll explain that as we go along so

this is our data set i'm going to

copy this over to our working sheet when

i go into you know in excel and i'm

working on something i don't like to you

know use just the one that i was using

in case i mess something up and it saves

over it or some issue i like to create a

working sheet and keep the raw data

right over here

it just makes my life easier i don't

have to save it and then you know open

up a different excel to compare them

so we have our bike buyers this is our

working sheets this is our raw data this

is the one we're actually working on

today

so let's um let's start looking at it

really quick and just kind of glance and

see what data we're working with

and then we'll start cleaning it up

making it more useful for what we are

going to be using it for

and then we'll start building out the

dashboard

so

right here we have an id

that should be a unique id to each

person

this is their marital status so married

or single this is their gender male

female have their income children their

education their occupation do they own a

home how many cars they own

how long their commute is the region

where they live their age and if they

purchased a bike and this column right

here is extremely important this is

going to tell us whether they did or did

not buy a bike so we got their

information they're looking for a bike

but they either decided not to buy a

bike or they did buy a bike and we're

going to be using that one a lot in the

in this video and so

um you know this is basically

the data set that we're working with

some of the demographics and information

behind the person

so what we want to do when we are

cleaning the data before we do anything

i like to see if there are any

duplicates in here

what we're going to do is come right up

here

we can go to

bom

where is it right here we got remove

duplicate so we're going to click on

that it selects every single one and we

just want to see if there's any useless

duplicated data that we do not need uh

and the data is a header so we can click

ok

all right so we had a ton of duplicates

in there for whatever reason so yeah we

do have duplicates in there so i'm glad

we did that otherwise we would have uh

you know

not good data and we don't want that

let's start right over here um the id of

course we're not going to change the

marital styles and gender are

m's s's f's and m's

this isn't inherently a bad thing to

have it like this but you know we have

to think about it from the perspective

of someone who's going to be using this

dashboard do they know what m and s is

do they know what m uh and f is and if

they don't it's better to just spell it

out for the most part so let's just do

that so we're going to click on the

column b we're going to hit control h

that's going to bring up our find and

replace

now there's an m in both of these

columns and there's different things one

is married and one means male so what

we're going to do is we're going to

search by columns

and we'll have match case i don't think

that's going to change anything but that

just means an exact match

and we're going to do m

equals and we're going to replace it

with married and we'll replace all

awesome and then we'll do s is single

this one is super easy we're going to do

the exact same thing right here so

column c

hit ctrl h

we'll do

still has by column so we'll do

m is

male

and we'll replace all of those

and f is

female

and replace all those that's great

uh you know the next column right here

is income and in except in this previous

video i talked about how i don't

typically like it in this format and

that's true um if you're doing

calculations on it or any other thing it

can mess it up sometimes having the

dollar sign or it being a currency

we're not really going to mess with it

too much right now

what we can do is just kind of

make sure all of it's currency

we'll just go like that to make it a

little simpler but we're not going to

change it to like a numeric

um

we will use this in the visualization

we'll see how it looks and if we need to

we'll come back and change it if not

we'll keep it how it is

so that's all we're going to do to that

one

the children those look good we have

education

partial college partial high school this

looks fine to me if there's any spelling

errors or anything like that of course

we need to clean that up it doesn't look

like there is

occupation

skilled manual manual okay those should

be separate with a homeowner

should just be yes or no

all right we have cars one two three

four good night who owns four cars um

and then we have the commute distance uh

and you know there's nothing

terrible about this it's giving you

ranges um which can be a good thing

i i say let's keep it for now but i have

a feeling when we get further and we

start using it in the visualization we

may want to change this so let's just

hold off for now

but if needed we will come back to this

and we'll change this

and then we have our region

and that looks totally fine and we have

our age now

when you're using ages typically you

have some type of like age bracket or

age range and you do that because there

are so many ages in here right it's 25

all the way down to 89 and if you're

using that on some type of visualization

it could just get really messy and so

you'll create kind of you know just

brackets around these so that you can

kind of condense it and make it a little

bit easier to understand

so

let's do that and just create a new

column and then we can use that for our

dashboard so let's go right up here

we're just going to create a new column

we'll call this age

brackets

and

what we can do is we can use an if

statement

to kind of

say if it's older than or less than and

and kind of give them these ranges

that's one way to do it and that's the

way we're going to do it right now so

let's go up here and what we want to do

is we want to say is going to we're

going to say equals i'm going to do if

and we're going to close that

parentheses now what we're going to say

is if this

i'll go right back up here if this is

less than so we're going to do this

31

and we're going to say comma

so if they are less than 31 what do we

want to call them what do we want their

their

you know

name to be

we'll call them

adolescent oops that's not how you spell

adolescent adolescents

and then if they're not what we're gonna

do is we're gonna say it's invalid

okay and let's just see if this one

works first

all right it's not working at all um

okay so basically what we did was um

incorrect

we did it backward uh we want to do i

said uh l2 is greater than 31 no we want

to do

like this

so let's do that now

all right and it should pull up where if

they're under the age of 31 so if

they're 30 or below is basically what

it's saying so if they're 31 they'll be

invalid but if they're 30 or below it's

adolescent so it is working properly um

and let's see what it see what it says

perfect

so this one is working and now what we

want to do is we actually want to build

on this

and make it

kind of like a nested if statement if

you've ever heard of that or done that

before

so this is our first if statement and

this is going to be

this is invalid this is our value if

false statement this whole statement is

going to become

our value if false for a different if

statement

so

let me

write it out and hopefully that'll make

sense but we're going to say if we do

open parentheses and we'll do it like

this and let's just get rid of this for

a second

all right

what did i do and let me do

oops

give me a second

okay

we have our if let me just write that

out again

we have our f there we go so now what

we're going to do is we're going to

write

basically the next part of it so we're

going to say if that l2

is and we're going to do this time we're

going to do greater than or equal to 31.

so now it's going to include that 31. so

right here we did anything less than 31

so it's 30 and below this one is going

to be 31 and above so we're going to say

these people are

middle

age

and if not then it's going to go to this

if statement and then we need to close

that i believe so now let's try this

all right

fantastic now if

everybody should be in one of these

areas right everyone should either be an

adolescent or middle age because

basically all we're saying is is if

they're older than 31 or 30 or below

that's all these two statements do

so we have um you know our next group

now we can add and go even further into

this

and now we can use this entire thing as

the um

what was it called

the value if false section so that's

what we're going to do we're going to do

one more so we have three different

categories so we're going to say if and

do uh an open parenthesis and we're

gonna say if oh actually let's do it um

let's not do it to this one

let's do it to this top one just easier

uh so we're gonna say if open

parentheses we're going to say l2

and this time we're going to say anybody

over the age of 50

or we can do 55 let's do 55.

so do 55 and we're going to call them

old

and we'll do comma and this is the value

if false statement and we need to close

the parenthesis so let's try this

anybody over the age of 55 should have

old

you know maybe we'll do 54 so anybody

who is 55 is considered old i think

that's fair

i think that's fair guys oops i should

have done

i should have done that to this one let

me get out of this

and we'll do 54.

my dad is 55. that's why i'm doing it

like this this is for you dad

because he should be in this old

category to be fair so now we have

adolescent adolescent middle age and old

these are three categories so we can now

have these buckets these different

groups of ages and it's much more usable

than these individual ages um and so we

will be using this in our in our

dashboard for sure

now our next one is the purchased bike

and we're not going to do anything with

that so

you know that is that is that one

and you know there wasn't a ton to clean

up here we removed some duplicates um i

don't know why it says that what did i

do married

married what does this mean even mean

i did i write that did i mess this up

guys

oh

[Music]

when i did the m

and the s

replacement in there and it replaced it

with married

and single it's supposed to say marital

status

oops

thanks for catching that guys thanks for

catching that i hope that's how you

spell marital uh we'll see so

we are going to keep it just like this

now

what we are going to now

now what we are going to do is build

pivot tables with this data so we had

our raw data we have our working sheet

and now we want to create pivot tables

and pivot tables is how you actually

help build your dashboard to help build

your visualizations so we're going to go

right here we're going to hit

whoops let me get rid of that

we're gonna go right here we're gonna

insert and we're gonna say pivot table

and it's gonna ask us what range

so we're gonna go back to the working

sheet and we'll just click here and hit

control a

this is going to select all of our data

for us so it's really easy and we're

going to hit okay

and so now we have all of our

pivot down i don't need to pull it out

that far that was way too far and now we

have all of our pivot table information

over here and so that should make it

really easy to you know actually build

out so what we're going to do is start

selecting what columns and what data we

actually want to work with so the first

one that we are going to build out is a

dashboard that is basically looking at

the average income of somebody who

either bought or did not buy a bike

so we need in this one we're going to

need their income that's definitely

going to be a value right here but we

want to break it out by

male and female so let's look at their

gender i'm going to pull that down into

the rows so

this is basically a sum and now let's

look at

let's make this an average so i just

went to the

um

i clicked right here i went to the value

field settings and we're just gonna do

an average

all right and then we are gonna make

these

um

and as you can see there's four decimal

points um we'll keep it as is right now

but we may need to go back and change

something and then we're going to look

at if they purchased a bike or not

and we're going to put that right here

so we can see that

right here for the people who did not

buy a bike the females their their

average dollar was 53 000 the average

salary for the average salary for males

was 56 000 for yes the ones who did buy

a bike the average salary was 55 for a

female and 60 for males so the people

who had a little bit more money are

buying bikes and we can also see that

the men are making more money in this

data set just overall in general um so

let's make the visualization really

quick but

you know i don't know i'm not a huge fan

of these decimal points and maybe we can

just change that in the visualization

we'll see

oops

that's not what i meant to do

um let's do that so what we are going to

do is we're going to click into here and

click insert

and we're going to these recommended

charts and it's going to bring up

basically

every single type that we would want um

and we can just click in here and see

which one looks good

uh oh yeah i love those 3d ones those

are my favorite you guys know that uh

let's let's use this one right here

pretty simple um whoops let's pull this

right over here

and as is it looks pretty good um you

know it shows male female we have the

average or the incomes right here

whether they did or did not purchase it

and so at a glance it's pretty easy to

see

let's see if there's anything um

you know

if you want to change up style wise go

for it i'm just going to keep it as is

but let's see if there's anything we

need to add right do we want to add

these axis titles

for the most part i i tend to do that um

it makes it pretty easy to see so we can

go in here and we can just click it like

this and we'll say income

and we'll say

oops

and we'll do

gender

so that's what that is

and let's go back in here

do we want to add a chart title we

definitely want to add a chart title uh

for most of these we'll add a chart

title for sure so we'll say average

income

per purchase

um i don't know if that's 100 right but

we'll we'll use it if we need to change

it to be you know by gender or something

we can but

for now let's see do we want to add data

labels

definitely not

a data table

we can do this it may make it a little

easier to read i will say that again

these numbers are just these decimal

points are really throwing me off let's

go see if we can change it in here

let's go to

see if we can just make these numbers

okay

and

um we can keep it like that or we can

even

do something like this add commas

yeah i'm going to keep it just like this

i i think this just looks the best um

again i'm i'm getting adding commas here

i'm changing the decimal place right

here it just makes it look a little

nicer a little cleaner

so

let's keep this

exactly how it is um

we can always change things if we want

to

if we want to come back to it so

we created our pivot table and then we

created our visualization basically

exactly what we're going to do for all

of these because again all of these need

all of these need pivot tables in order

to create the visualization so let's

get out of here

we're going to scroll down and we're

going to create our next pivot table

and once we get done with all of the

pivot tables that we need or all the

visualizations that we need then we will

um

we will start so we're going to do

control a

we're going to do okay and basically do

the exact same thing that we did

this time we're going gonna look at the

distance so for this one i wanted to see

you know i try to you know i created

this already i've already done this

entire project through but i haven't

really talked about why or what we're

gonna look at

for this one you know we're looking at

is their income does it change whether

they bought or didn't buy one

so if they said yes you know is there a

reason are they making more money is you

know our price points are the customers

did they make more money so you should

cater to them or not

uh that's a good question another thing

is you know we sell bikes or this person

sells bikes so

commuting distance definitely makes a

difference you know does the person who

is buying a bike live one mile away from

where they work or 20 miles away this

will help us determine this next

visualization will help us determine you

know

who is doing that or who's buying it so

what we're going to do

is we are going to look at the that one

that we were looking at earlier the

commute distance so we're going to bring

that right over here so we have these

you know one mile 10 mile 1.2 etc

now we're going to

again we're going to look at if they

purchased a bike that's really important

and let's make that the column as well

so now what we have is a count

of these nodes and yeses whether they

did or did not buy a bike

um one of the issues i already see and

we'll i'm going to visualize it and then

i'll show you this 10 miles you know

it's right next to the 0.1 so it's not

an order

and that could be

that could be an issue so we may have to

revise that somehow to put it at the

very bottom because we can either do

ascending

or

descending uh either one i don't think

is gonna work so we may have to work

through that in just a second um i don't

know if i did that in my

i plan for that um

yeah so it has this big dip um

yeah so let's let's create it um that's

okay we're gonna figure this one out

together because i honestly um

i didn't plan for this one so okay we

have 0.1 miles that's exactly where it

needs to be the one the two the five

that's exactly where it needs to be this

10 miles is not and let's see if i

change that 10 10 plus miles to 10 miles

plus

let's see if that'll put it down here

because i don't know if it's looking at

i don't know if it's reading it weird um

but let's go to this working sheet

and let's go right here and we're going

to do control h

and we'll do oops not this one

um 10 miles plus

let's get that in there and we're going

to do 10

uh miles

plus i i don't know if that's actually

gonna work um

we will see so let's go back to the

pivot table

let's re go to the data

let's refresh

uh no it didn't it didn't change it um

okay so

let's think about this maybe if we

change it to like

a letter it might change down here so

start it with uh miles that could work

um let's try it

okay it's already selected

let's do

the 10 plus miles

okay so let's do

um

more than

10 miles

and we'll replace all

let's get rid of this

[Music]

let's go to the pivot and refresh

all right okay so

it's not perfect but it works um and for

what we're doing i think we'll keep it

how it is so we have our second one

uh and

you know there are different ways you

can kind of change this one um you know

on the last one we did a ton of

different stuff

we can do

just do

commute

distance

and

we can say

what do we want to say in this one what

is this

oh this is the count um do we have it

could we have to keep this one

um

no there we go i'm just gonna do um

just one and say

commute distance

and let's add a title

chart title we can make this one um

let's say

distance

per customer

uh that's not 100 true because it's

nowhere yes um that's that's the

important part of this it's

distance

average distance uh let's see

we'll just say customer commute

all right and we'll keep it just like

that

all right perfect

i don't think um

let me see i don't think there's

anything else we need to add on that one

all right now let's go right down here

we're gonna create our very last one uh

we only have three so you know

sometimes you'll have a ton sometimes

you'll have like one on each sheet and

you'll create multiple sheets but um

do control a um now we have our thing

now

this one we're going to be looking at

these age brackets that we were looking

at that we created um

something that i do

honestly a lot is is kind of bracket

things and into groups like this and you

know

for this i'm just kind of made them up

but

um you know it's good to

know how to do this

because i i promise you this one happens

a lot or i use this one a ton and then

we just want to look at who purchased a

bike

uh so the same thing as we did before so

like purchase a bike kind of the

purchase um you know pretty easy so you

have the count of either no or yes for

these age ranges

and let's go to

the insert we'll go to recommendation

um i personally like a good line for

this one

um so let's

already this is already interesting

maybe do something like this

that's nice

see this one versus this it just adds a

dot oh looks nice we'll keep that one um

so

just really quick at a glance really

interesting people under the age of 30

are not buying that many bikes um age 30

to 54

uh 31 to 54 buying a ton of bikes uh

they're

they buy more bikes or look at bikes

more than anybody really interesting um

but yeah we'll make the dashboard a

little bit

um let's make these chart titles we'll

do

oops the horizontal

let's call this

age bracket

and then we'll add a chart title

again you can add some extra stuff if

you want to

but you don't need to none of this other

stuff we really need i'm just kind of

looking at the stuff we do need or do

want so what do we want to call this one

let's call it customer

age

brackets

and

it's not perfect but we'll keep it as is

for comparison let me see if i can copy

um

or

use this

um real quick instead of the age

brackets i'm gonna get rid of this and

use

the age

and then let's use

let's insert recommendation

we'll use a line

and we'll use this

so

this compared to this

just think of it like

if a customer or consumer or not a

customer if somebody you're working with

is trying to use this dashboard to

understand this dashboard

this is gonna be just it's gonna

i don't know it might melt their brain

it just makes no sense it makes sense

it's just all over the place it's really

hard to make sense of this it really is

i mean you can kind of see a pattern

going up around like the mid 30s and

then it trends downward but it's hard to

see um it really is so doing these um

these brackets really helps and you can

even add you know adolescent um

you know

0 to 30 underneath it in fact we may

want to do that um

why not why not let's do that whoops

um so why don't why don't we do that why

don't we go back i'm just gonna i'm

doing this on the fly why don't we go

back

uh what am i doing whoops

and this is all calculated but let's do

adolescent

0 to

30.

let's do middle aged

31 through

54. and then old 55 plus let's see if

this breaks anything i hope it doesn't

um

and we'll go back to our pivot table

let's refresh the data

uh okay it did mess with stuff

okay never mind because it was a

terrible idea don't do that

um

perfect

uh let's get rid of that

that was a terrible idea

don't do that i'm glad we tested it out

though i like i like to see if it was

gonna work no it messed with the um

the order of things um i i intentionally

named them adolescent middle age and all

because it's it it

makes sense for the visualization um

but you know if i

change something and it messes with it

i'm not going to mess with it it was

just an idea on the fly guys come on

all right so let's start building out

our dashboard now

when we're building our dashboard what i

personally like to do is to have this

pivot table sheet

and then i will copy them over and later

we'll hide these other sheets

and i'll explain that a little bit but i

like to have this this one for us so

we're gonna copy this so i just click on

it hit ctrl c

we're gonna paste it right over here

uh let's just make them small for now

that's oh gosh

no let's not do that oh these look

terrible okay anyways um

let's

copy this one over

oops

okay what did i just do

[Music]

oh i didn't copy this one whoops

it's not copying

okay

we're gonna go copy

hit paste

fantastic

oops

guys look away this is this is tough to

watch it's tough for me to watch i'm the

one doing it and it's tough for me to

watch all right let's go to this last

one

i'm not sure i'm going to try it again

all right it worked this time

so now we have

our three visualizations

this is perfect but now we actually want

to create a dashboard now how do you do

that how do you make it look nice

um and then we're gonna add some you

know filters and stuff like that how do

we make it look nice

um

what happened here what changed what do

we do

oh my goodness gracious all right let's

copy this

let's paste this

let's get rid of this i don't even know

how that happened i've never seen that

before that was wild uh excel is trying

to destroy my whole video

i mean i'm doing this for you excel good

night okay

no problem at all what we're going to do

and how you make this at least look nice

first off we can get rid of these grid

lines pretty easily and i recommend when

you do that when you make a dashboard

just makes it look cleaner it makes it

look like an actual dashboard um let's

go to view

and grid lines so we can get rid of

these grid lines it just makes it look

nicer

we're going to make

you know we can choose any color here

i'm just going to get choose a color

i like this

and let's

we're basically creating like a header

right if you're using like tableau or

something we're going to merge and

center so it takes every single cell

that we have highlighted creates into

one let's call this

bike

sales

i have i think called a bike sales

dashboard let's just call it that

you know see what happens

let's get

that let's make it white

and make it much larger than it is

okay okay um

sure

let's do that

it doesn't look bad

um

what is it doing

there we go uh let's play that center

perfect um it's not perfect but we're

going to use it all right so

now we kind of want to organize these

and

you know everybody has their different

way of doing it

i'm just going to start

building it out myself and just see how

it looks

and then we'll go from there i like this

one there

we can put

this one i this one's kind of a longer

one so i'll probably put it at the

bottom let's see how it looks

um but we'll put this one right here

try to line it up geez let's let's zoom

in a little bit let's try to line this

up see what it looks like

let's extend it to the end

that doesn't look too bad it needs to

move up just a hair

and i'll show you how to kind of align

these in a second but

um

that looks not bad

and we'll kind of try to align these as

well let me zoom out

and extend this the length

of this just to make it look nice um

you know

now

what you can do and you know this is

something that's pretty

simple is you can

get both of these and we're gonna go to

shape format and we can just align these

it's really nice to align especially if

like the top

and maybe like the left to right

but like we're going to line these to

the top and they just kind of align

themselves on the very top now these

look much better

this one is a larger dashboard or a

larger visualization so i'm gonna keep

it how it is

um

and i'm gonna keep this one how it is so

it is gonna be a little bit smaller as

you can tell and then we'll have this

one

um and i'm going to

do that um i

this is going to bother me if i don't

align these so let me do this

i'm going to shape format align to

the right

and

it's not exactly what i wanted to happen

because

oh geez what am i doing that's not

exactly what i wanted to happen i

actually wanted this one to align uh

this one to align with this one it did

the opposite um so let me just scoot

this back all right visually it looks

fine but that's how you do it if you

want to do it um i i

if you have multiple of them like this

it you can make it look bad so we have

our dashboards this is already looking

really good i like how this looks

colors are coordinated it we have a kind

of a theme throughout um and it looks

nice i actually i actually kind of want

to change this one um

to

um

let's see

maybe if i did like that it looked nicer

than all of them yeah this does look

nicer

um it doesn't change much either

guys should i do it

all right we're going for it we're

changing the design on the fly

should i do it for all of them

let's see

ah it doesn't fit doesn't fit um all

right guys just ignore what i'm doing uh

don't do any of this i'm just messing

around at this point so

this is really great to have it really

is and what we want to do is there are

other elements there are other things

that people would like to field a filter

by and be able to look at but it's not

in this visualization

um to be more specific one field that's

could be really interesting is married

versus single are single people buying

more or are um married people buying

more you know it'd be nice to filter on

it so we're going to click on

any of these actually and we're gonna go

up to pivot chart analyze and we'll

click

insert slicer now we can choose which

ones we want to be able to filter on all

the same time or one at a time i'm just

gonna do the first one by itself and

then i'll show you how to do other ones

um

but this one is the merrell status so

this is the married single the one we

were just looking at and we can drag

this right over here

and bring it in a little bit

all right and we don't need all that

space so we're gonna

boop all the way up now

while we're doing this um it only

because we selected this uh this

visualization it only is working on that

one right now we of course wanted to

apply to all of them is not hard to do

all we're going to do is we're going to

click on we're going to make sure we're

clicking on this we're going to go up to

slicer we're going to hit report

connections

um and if you remember we have this

this pivot table that we're working with

and this is where all of our pivots are

coming from so we're going to actually

apply it to all of them

this is our sheet

and this is the name of the pivot table

now again we created that fourth one

we're not using it but we're going to

apply it to all of them

so now

when we click on it

it's going to apply to all of them so at

a quick glance let's see what single

people are doing

[Music]

interesting

interesting um you know

when i'm looking at the just these

numbers right here

married people these individuals are

making a lot more like eight

um

sometimes eight to like ten thousand

more on average than their single

counterpart

you know again that's a rough estimate

but it's interesting so now what we can

do is we're going to create more of

these so we're going to go to uh pivot

chart analyzer we're going to go to

slicer now we already did marital status

but what if we want to look at things

like

uh

region

and maybe something like their education

so

let's bring up both of those and look

now two of them come up so let's add the

region right here

and we'll bring that in just a little

bit see if we can match it

nailed it all right now we're going to

put that up we'll bring this one down

just like this

bring it over see if i can match it

again come on

[Music]

almost nailed it i don't know if i

nailed it but it's close all right kind

of bring this up a little bit

bring this up

and we have to do the exact same thing

that we did with this one because right

now again it only applies to that one

chart so what we want to do is we're

gonna go to slicer report connections

add it to all of them

okay

do the same thing with education

for connections

we are looking good

and now uh let's get rid of all of them

this is going to be everybody

so now we can slice and dice and choose

what we want we want to look at people

who have a bachelor's degree who live in

europe and are single

and this is the information that we have

on those people so now we can

narrow it down by certain demographics

even further

and look at this key information so we

may not you know look at counts and

averages of these things but we're able

to filter on them and that's really

great to know so bachelor's degrees on

average are making 60s 70 000 let's look

at

let's look at graduate degrees

okay a little more

um

but you know again i'm just looking at

random stuff but you can mess around

with this take a look at some stuff

this to me

i want to make this color darker i feel

like it'd look nicer darker

there we go oh yeah that's way better

this to me is it's a good dashboard

right you have key information that

you're looking at nice visualizations

it's color coordinated you have these

slicers on the side um

to me this is a fantastic

just simple dashboard

and there are so many other things that

you can do with this data and you can

make it unique and you can add your own

spin on it and i highly recommend that

you do that push yourself go past what

we just did today and add your own stuff

and use this and then you can add this

to your portfolio website and show this

off and show people that you know how to

use excel which is a fantastic thing to

know how to use and show off so with

that being said i hope that this project

was helpful i hope they learned

something along the way i know i did

i was learning things as we were going

and i hope that you didn't mind that i

took some detours along the way um for

your amusement as well as my learning uh

so with that being said thank you so

much for joining me i really appreciate

it i hope you have a good day and

goodbye

[Music]