Data Analysis Essentials in Excel

suppose you've received a large data set it's unformatted and messy and your manager is asking you some questions about it hey I have some doubts here do you have a second so to answer his questions we'll go over three essential data analysis skills which are data cleaning analysis and data visualization so let's get into it here's the Excel file which you can download for free in the video description we need to make a few changes to the data set like suppose we want to merge the first and the last name into one column we want to go ahead and change the formatting of the salespersons here so it's a bit more consistent and a bunch of other things now the fastest way to do that is using power query for that we're gonna head over to data and under the data tab you'll see this get and transform data area and just click on from table slash range it should select the whole data set for us and we'll just hit on OK there and from here it's going to load up the power query editor and this is really where we're gonna make all of the data cleaning changes it's a lot faster than doing it using just Excel so the first step when cleaning data is to remove any duplicates so under the order ID if there's more than one order ID with the same number then it's probably a duplicate so we can just right click on it and hit on remove duplicates whenever we make a change it's going to be recorded under applied steps here so you can always go back or you can even remove it if you wanted to then for the dates they're currently formatted as numbers you can see over here that it says whole number under date type so let's go ahead and click on it and change that to a regular date and you'll see that that changes automatically to the normal date format then we said we wanted to merge the first name and the last name so select both columns by hitting the control key we have both selected there and then we just need to go to right click and we'll go to merge columns from here we want a separator which is just gonna be the space sign for us and we want to go ahead and call this something like names and hit on OK and you'll see how we've been able to merge the first name and the last in just a few clicks if we keep scrolling through the data another thing that stands out here is that the units have decimals so if we're selling an item we should sell it in whole not a portion of it so let's go ahead and switch this up by going over to transform and from here you'll find the rounding on their number column and we want to go ahead and change that to just rounding it up and you can see there that we have the whole number one final thing that we want to change is all of these sales person column where they're not very well formatted so we can head over to format under the transform ribbon as well and first we want to go ahead and capitalize just each letter so the first letter of a word like so and we also want to trim these spaces so we can go back under format and just click on trim now that's looking a lot better now to go back to excel from here we just need to go over to the Home tab and then hit on close and load and this is going to apply all the changes that we've made so you can see here that it's created a new tab for us and this time it has everything cleaned up we have the queries and Connections in case we ever want to go back to it we can just right click and hit on edit but we don't right now so we'll just close out of that later in the video we'll look at creating some awesome visuals but before that let's get into the analysis section where our manager is asking us some questions about the data hey thanks for cleaning up the data if you don't mind can you summarize the revenue column so here he's probably trying to understand what the average revenue is or the minimum or maximum is and so on so it's basically this whole Revenue column here now we can do this with individual formulas like the sum the average and so on but there's a better way to do that that's by going to the data ribbon and then you'll find this data analysis option if you don't find that just go over it to right click and click on customize the ribbon then head under add-ins and you're going to want to select the analysis toolpack hit on go and select it from here obviously I already have it for now so I'm just gonna click inside of it once we get this pop-up we want to head to an area called descriptive statistics which is this one here and hit on OK this is going to summarize all of our Revenue data so for the input range we want to select all our Revenue numbers so just Ctrl shift down there and then for the output let's say we just want it on top to the to the right here so let's say I just put it on M2 and then I'm just going to put summary statistics that's going to give us all the information we want and hit on OK there and you can see that it's created this whole set of Statistics from hey what's our mean revenue what's our minimum our maximum the total revenue the count and a ton of other useful data awesome thanks for putting this together can you also show me the breakdown of Revenue by salesperson we can do this by creating a pivot table so just go ahead and select inside of data go over to insert and just click on pivot tables hit on OK there and it's going to create a new tab where we can analyze it then under pivot table Fields let me just drag this along so we can see it better we're gonna want to add the revenue so that's going to be over under values and we're also going to add the sales person that's going to be under the rows and now we have that full breakdown but as you can see it's not really an order here so we can right click on it go to sort and sort largest to smallest and it seems like Anna is being the best performer for us thanks for that now for our next marketing campaign we're trying to see if we should Target more of a male or female audience do you have a breakdown for that for this we can still use pivot tables so let's go ahead and select this first one Ctrl C to copy and just paste it down below with Ctrl V and now we're gonna make all the changes so this time instead of the sales person on the rows we're gonna remove it and let's add the gender in here so we have the sum of Revenue maybe we also want to see the units so let's go ahead and drag that in as well based on this you can see that we should probably Target females more as they're the bigger part of our audience we can also get the proportion so the percentage here simply by right clicking on it and then show values as a percentage of grand total so with that you can see that we have 53 percent almost that are female relative to around 47 that are male this is great thanks a lot for your help awesome we can pretty much get any information on our data using pivot tables but there's another AI tool that Excel introduced so let's take a look at it we'll head over to the table here under table one and this time instead of going to Pivot tables we're gonna go under the home ribbon all the way to the right where it says analyze data so let's click on that it's basically going to create a q a box and within it we can ask anything so before we did a pivot table to find the revenue by salesperson you can also ask it here so Revenue by a salesperson hit enter there and you can see that we get the exact same breakdown so that's an awesome AI tool that you might find Handy and if you want to learn even more about data analysis you can check out our Excel for business finance course using the link in the description below we won't just go over data analysis though we'll also cover formatting best practices shortcuts building awesome visuals dashboards creating large Dynamic Financial models and much more and if you get stuck along the way you can always ask us the course instructors any questions in the discussions forum and if you want more than just Excel we also have several other courses on power bi Financial Accounting corporate finance and more if that sounds interesting check out the link in the description below alright back to the video now that we've cleaned up and analyzed the data let's look at how to visualize it and one of the easier ways to do that is using conditional formatting so you can see over here that we have the the breakdown that we did earlier with the revenue by salesperson and suppose we want to visualize these numbers over here a bit better we can go ahead and select them and then just go over to conditional formatting under the home ribbon and then we'll select the data bars let's say we go for a red one over here and you can see how easily we can tell the proportions now and just how much Anna brought in in Revenue another great visual for data analysis is a scatter plot so over here we have the cleaned data set and suppose we want to see if there is a relationship between age and revenue maybe older people tend to spend more money so let's go ahead and select both just by clicking on the edge here and hitting Ctrl shift down arrow then we'll go back up and for the revenue we want to keep hitting that control key so we also have this selected and again Ctrl shift down from here we'll go over under insert and right in the center here under charts we're going to want to select the Scudder so this one right over here to drag this back up just hit Ctrl X and then scroll up to the top and let's say I put it over here to the side with Ctrl V so it does seem like there's a bit of a trend where the older they are the more money they're spending we can look at that closer with a trend line just by right clicking and going to add trendline it does look upward sloping we can even go ahead and add the display equation so that we can see the numbers more precisely so here's the equation and basically what this is telling us is that for every increase of one year in age Revenue goes up by around 340 dollars so overall it's fair to say that the older they are the more they're going to spend one final visual we look at is a box plot which is great for finding outliers in the data so over here let's suppose that we just take the revenue by hitting Ctrl shift down then we're gonna head over under insert again and this time under the very center here we're gonna click on that and go to the box and whisker I realize this is a funny looking chart so let's go over and explain what it's saying so the line right here which is quite small is the median the x is going to be the average for us and then these top and bottom parts are the extremes and these two here are the quartiles the first and the third quartile and if we had any outliers they would be showing us dots up over here or down over here just to show you quickly let's say I put another zero in here I'm just going to put a huge number you can see that it starts to show as an outlier there let me go back so you can see this again for more on data analysis check out this video over here going over five awesome data analysis tools or take our Excel chords over here and I'll catch you in the next one

Channel: Kenji Explains

Views: 122,171

Keywords: data analysis excel, data essentials excel, data skills in excel, analyze data in excel, data analysis, analyze data, data cleaning excel, clean up data in excel, data visualization, data visualization in excel, box plot excel, power query, power query excel, scatter plot excel, conditional formatting, merge columns power query, pivot tables for data analysis, analyze data with pivot tables, excel ai, data visuals, formatting with power query, power query for data cleaning

Id: GLneLGYZgnA

Length: 11min 51sec (711 seconds)

Published: Sun Sep 03 2023