Master Data Analysis on Excel in Just 10 Minutes

In this video, you're going to learn all the fundamentals of data analysis and we'll break this down into four main areas. First, we're going to be looking at transforming the data, so cleaning it up. Secondly, we're going to be looking at creating descriptive statistics to understand it better. Third, we're going to be looking at data analysis. And finally, we'll get into creating a report to visualize our findings. So let's get into it. And thank you to Coursera for sponsoring this video. More on them later. The first step here is to transform the data. And for this, here's the Excel file that we'll be working with, which you can download for free in the video description. So as you can see, this is going to be for a fast food chain, you can see the types of products here. And the first step is simply to clean this up. But for that, we're going to go to Ctrl T. So to change this into a table, so change the dataset into a table, then we'll hit on OK. From here, for the cleanup part, you can see that under the manager column, we seem to have some very odd spacing here. Let's go ahead and clean that up. Here, we're going to go to equals, trim, hit the Tab key. And this formula is going to remove these odd spaces. We'll hit Enter there. You can see that's going to auto populate for all of these. We'll change this name to manager. And then for all of these that are currently linked to this column over here, we're going to paste them as values. So we're going to go Ctrl Shift down, Ctrl C. And then we're going to paste these as a value. So Alt H V V is a shortcut for that. And now that means that we can go ahead and remove this column by hitting Ctrl minus. There you go. Then under quantity, you might notice that we've got these decimals, which don't make too much sense. You can't really have half a burger. And so for this, we're basically going to have to round up to a whole number. For that, we're going to use the round up formula. So equals, round up, hit the Tab key there. The number is this one, comma. And the number of digits for us is going to be zero because we want a full number. Close those brackets and hit Enter. So this is going to be our quantity now. Hit Enter there. And we basically want to Ctrl Shift down, Ctrl C, and then paste as values. So Alt H V V again. There you go. So now we can remove this column here. So we're going to select it and Ctrl minus. Then you'll notice that under city here, we have the city names, but we don't necessarily have the countries. That would be a nice thing to add. So for this, we can actually head over to the Data tab and go under Data Types. You'll find Geography here. So just click on that. Once you start to see that icon, it means that it's loaded up. What you want to do here is hit over to this icon to the side under Add Column. We basically want to add a country or a region associated with that city. And you can see that we have the corresponding country for each city. If you want to move this city column next to the country, you can actually go ahead and select it up top and we start to see these arrows when you hover over it. Just press the Shift key and then just drag it across like so. That should allow you to move everything. One final thing we haven't done here is test if there's any duplicates. So for this, we can simply go under Data again and we're just going to remove duplicates by going to this icon over here. So we'll click on that and it's going to be for that whole table and we'll simply hit on OK. And you can see here that we get a notification saying that five duplicate values were found and removed. So we'll hit on OK there. And speaking of data analysis, if this is something that you want to learn more about, you can check out Google's Data Analytics Professional Certificate. It's designed to teach you job-ready skills for any data analytics role, so you can learn all the skills required in less than six months. The program currently has well over 1 million students and a 4.8 star rating. Now, if you're wondering if you need any prior experience, no, there's no prior experience required and it's all fully self-paced. In the program, you learn key analytical skills including data cleaning, analysis, and visualization using some of the most common industry tools such as spreadsheets, SQL, or R programming. Upon completion, you'll receive a which you'll be able to share on your CV and LinkedIn. So if you're interested, sign up for a seven-day free trial now using the link in the description below. Alright, back to the video. Great, now that we've done step one, in step two it's all about the descriptive statistics. So really seeing things like what's the average price, what about the minimum, the maximum, and so on. So for this, let's hop over to the side and you could try to find for the price and for the quantity say the average going one by one. So type the average formula, then doing the same thing with the minimum, the maximum, so on. But there is a much faster way that's actually using a tool called data analysis. For that, we're gonna have to activate it. It should be over here to the side under the data ribbon. If you don't have it like me, we can go ahead and activate it. So we'll head over to file, go over to the bottom where it says options. From here, we're gonna go into add-ins and we want to click on the analysis tool pack here and hit on go. From here, we want to select analysis tool pack, like I said, and we'll hit ok. Now you can see that we have this data analysis option under the data ribbon. So that's the one that we want to select. From here, we want the descriptive statistics and we'll hit on ok there. And the input range is going to be all of our prices. So we'll go ctrl shift down there and then we want it to be a summary statistics. And where do we want this? Let's say we want an output range which is going to be up over here, so right next to the table. Let's say we put it here to the side. There, we're just gonna hit on ok. And now you can see that for the price, which is what we've selected, we've gotten everything from the mean, the median, mode, minimum, maximum, sum, and a bunch of other useful information. Let me repeat that same process for the quantity. Awesome! Now we've got the breakdown by price and by quantity as well, but one thing that this doesn't quite account for is the fact that there could be outliers in our price. So for that, we can go ahead down over here. We're gonna basically create a new chart which is gonna be a box and whisker. So let's first select all of the prices, so ctrl shift down and down over here, you're gonna find this icon. Click on that and we want a box and whisker. Basically this one over here and don't worry if you can't interpret it, we'll look at it just in a second. Let me move that all the way up. So once we have it in here, this is basically telling us a few different things. So first, these top and bottom lines are telling us the maximums, so the max and the min, if you will. And then in here, this box represents the first and the third quartile. Then you can see that we've got the x which is the average, then we've got a line which is going to the median. And finally, we've got these dots over here which represent all of our outliers in our price. Now if we want to dig a bit further into this, it would be nice to see the x-axis. Maybe we can put something like the manager's name to see where exactly this is coming from. Maybe it's one manager that might be reporting the data wrong. So we'll go to right click for that, go under select data, edit the horizontal category access label here. And what we're going to do is put the managers. So we're going to select all of these, ctrl shift down, and hit on ok, and hit on ok again. Now if we go all the way back up, you can see what that breakdown looks like by manager. And it seems to be that all of these outliers are accumulated with Joao. Maybe we should have a word with him. Now moving on to step three, which is data analysis. So over here you can see that we've got three different questions that we want to solve, which is what is our best-selling product, what is our total revenue, and what's our revenue breakdown by payment method. So if we go back to the table here, you'll notice that we don't actually have a column for revenue. So that's one that we're going to have to calculate simply by multiplying the price and the quantity. So we'll do that over here. Let me add an extra column there. And let's call this something like a revenue. And the formula for it is going to be equals the quantity multiplied by the price. And we'll just hit enter there. That should be calculated for us. And then we'll go up over here again. And for all of this analysis, we can actually do most of it with a pivot table. So we'll go ahead over to insert pivot table. The table or range we're interested in is all of this range. So once we select one of the cells, we can just go to ctrl a. That's going to select all of them. And we want this in a location in an existing worksheet, which we already selected. So we'll hit ok. Great. From here we can start doing all the breakdown. So what's our best-selling product? So let's do this by quantity. We're going to select the products as the rows. Just hover that over. And then we're going to select the quantity over here as the values. Great. Now we have all of that breakdown. If we want to sort it from highest to lowest, we can go to right click and go to sort. And we'll sort largest to smallest. And now you can see that beverages is our largest at 35,000. Then secondly, we've got what is our total revenue. For this one, we're simply going to have to select the revenue. So first we'll remove these. Let's take this out. And we'll take this out as well. And we just have to select the revenue and put it under values. That should give us the sum of total revenue, which seems to be 812,000. And finally, our revenue breakdown by payment method. So for that, we're going to get all of the payment methods and put those under the rose, like so. But to see the breakdown better, it probably makes more sense as a percentage. So what we can actually do is go to right click. And from here, go to summarize, show values as, sorry. And we're going to show them as a percentage of the grand total. Now you can see that full breakdown. And it seems like credit cards are the most dominant here. Now that you've seen how to do some of the data analysis, let me show you what a final report might look like. This one here is fairly simplified. So you can see that we've got this drop down, which is using data validation to select the manager. And everything dynamically changes. So if I select Joao, I'm able to see which country and city he's from using the x lookup. And then just below that, using the sumifs, I'm able to derive his particular revenue for fries and the other products, as well as his total revenue. And these data bars over here are done using the conditional formatting. If you're unsure how to do this type of formulas, you can check out this video over here, or this link over here to take our Excel course. Hit the like and that subscribe, and I'll catch you in the next one.