Full Project in Excel | Excel Tutorials for Beginners

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.