How to Use EXCEL for DATA ANALYSIS

Today you're going to learn all the data analysis fundamentals in Excel. I will break this analysis down into four sections for you. Data transformation, descriptive, statistics, data analysis, and reporting. I love no fluff content that cuts straight to the point, so without further ado let's dive in. First of all let me walk you through the data set that we'll be working with. I'll auto adjust all of the columns, select all of the cells, and then double click on the columns and you can see all of the columns clearly. So we have order id, date, product, price, and quantity, payment method, manager, and city. It's a very simple orders data set and the first thing we will do is going to be pressing ctrl and t to turn this into a table. So you can see that I have the table design tab here now and I will change the name of this table to orders. The second thing we will do is go to the data tab, go to remove duplicates, and then hit okay and you can see that I've removed three duplicate values so I have 5 000 unique rows right now. The next thing we'll do is we're gonna tidy up the manager and the city columns. So in the manager column you can see that I have a bunch of white spaces before the first name, like here, in between the first name, and the last name, and then I probably have some after the last name as well, say for here example. You can see that I have so many white spaces right here. So excel fortunately has a function for this, it's called trim, and once you use this all of the white spaces disappear. Now another thing I would like to do is I would like to capitalize the first letter for the first name and the last name. So excel of course has a function for this as well, it's called proper. So once I use this function you can see that all of my manager names are in a much nicer format. And then let's tidy up the city column, you can see that it's a mix mash of uppercase and lowercase letters, you can use the proper function again, and then there we go, we have both of these columns tidied up. Now what I am going to do is click into cell I2 and then press shift and then right arrow and then hold down ctrl and shift and press the down arrow, hit ctrl c to copy, and then I will use the shortcut alt h v and v to paste as values. So now I can delete columns g and h, let's delete those, rename column g to manager, rename column h to city, and the next thing I will do is I will reformat the date column. So I like to do this to avoid any confusion between American and British or European date formats, and the way to do this is that I'm going to click into cell B2, hold down ctrl and shift and then the down arrow, and then go to number formatting and define my own number format, which in this case will be day day, and then month month month, and then year year year four digit year, you can see the sample right here. So once I hit ok, you can see the month is actually the abbreviation of the actual month name. So that would be our data transformation done. And before moving on to step two, where I'll show you how to create descriptive statistics, I want to share a resource that's genuinely made a difference in my own data analysis journey, especially for learning Excel. If you're just starting out, the Excel Fundamentals track on DataCamp is the best place I know where you can learn Excel fast. I've personally used DataCamp for years, long before they ever sponsored me, and it's still my go-to platform for picking up new data skills. What I love about it is the super interactive bite-sized sessions. There's no fluff, just practical hands-on learning that actually sticks. The Excel Fundamentals track contains five courses that will teach you everything from cleaning, analyzing, and visualizing your data, including lookups, pivot tables, and advanced Excel formulas, to advanced techniques like sensitivity and scenario analysis. Plus, you'll get to apply what you learn in real world projects like a full customer churn case study. If you want a fun engaging way to boost your Excel skills for data analysis, check out the Excel Fundamentals track on DataCamp using the link in the description below. Trust me, you'll be amazed how quickly you can level up. Next up, we're going to move on to descriptive statistics, and under the data tab at the top right, you should have a data analysis tool pack. If you don't have it, like I don't have it right now, you can just go to file, options, add-ins, Excel add-ins, hit go, select analysis tool pack, hit ok, and then you can see at the top right, now we have data analysis. So if I click on data analysis right here, you have all sorts of tools that you can use, but first of all, the thing I would like to analyze is the revenue. So I will create this revenue column, which is a simple calculation of price times quantity. So back to data analysis, let me select descriptive statistics, hit ok, input range, this will be the revenue column, so I will hold down ctrl and shift, press down, and then press enter, and then the output range I would like that to be, let me scroll up here, let's say in cell L2, hit enter, and I would like summary statistics, and then hit ok. And it was this easy to get descriptive statistics in Excel. Another cool thing that we could do would be to include a summary statistic chart, for example. Again, I will select all of the revenue figures, go to the insert tab, insert statistic chart, and under the statistic charts, choose box and whisker, and it's right here, you can see it on the bottom. Let me select this chart, press ctrl and x to cut the visual, and move it to the top right here, and there's a lot of things you can see on the box and whisker, but the key things I would like to highlight is this is your third quartile, right here, this line, and then this is your average value, you can see it's 4083, so 4083, this little x, this is your average value, and if I go to my descriptive statistics, I can see that my mean is indeed 4083, and then this line here is your median, so 3016 is your median, and then this line right here is your first quartile, so in between this, this box shows you the interquartile range. And what's cool about the box and whisker plot is that you can actually add categories onto it, so if I right click, and if I go to select data, you can see that I can add horizontal data as well. So if I go to edit, and I can select all of the managers, so I'm in cell G2 holding down ctrl and shift, pressing the down arrow, and then hitting okay, and then okay again, let me just go up to my chart, and then I can see all of the values, all of the revenue values split by the managers, and I can see the outliers at the top here, so I can see that Elliot for example doesn't have any outliers. That would be descriptive statistics in Excel in a nutshell. Next up, let's do some analysis. So I'll be answering three questions. What is the best-selling product? How does the revenue breakdown look over time, and how does the revenue breakdown by city look like? Back to the orders data set, I am going to insert a pivot table, so insert tab pivot table from table, and then I am going to use the orders table, and I would like to insert it on an existing worksheet. Let's see, I'll put it right here in cell D3. That sounds good, and here's my pivot table. So first question is, what is the best-selling product? The best-selling product is going to be based on the quantity. All I need is to drag the products onto the rows, so I have five unique products, and then the quantities on the values, and then I'm going to reformat the number format here to include a little comma for the thousand separators, no decimal places, hit okay, go to the rows, and sort them by descending in terms of sum of quantity. So we can see that hot dogs are the best-selling products. So I could just type in hot dogs right here, and I know hot dogs are the best-selling products. Now we can look at revenue breakdown over time. So to do this, we'll need the date field obviously, and the revenue fields. So let me grab the date field, drop it onto rows, and then you can see that Excel automatically groups it for me. I actually would only want to see this by years and month, so no quarter. I'll drag the date out as well, go to my date field, right click, and then I am going to expand the entire field, and what I like doing is going into design, report layout, show in tabular form, and then turn the totals and the grand totals off. Now I am going to grab the revenue, put it onto values, and then I am going to reformat this as well to include a little comma for to separate the thousands, hit okay, and everything looks much neater now. What would be even better is to insert a line chart. So let me just find line chart right here, and then you can see the revenue breakdown over time. Pretty clear, and I think this obviously shows the trends much better than just a table. So what we can also do is we can show the revenue breakdown by city. So if I go to let's say pivot table analyze, I am going to select the entire pivot table, hit ctrl c, and I am going to insert this right here. So ctrl c and ctrl v, so I just copy pasted my pivot table because I want to answer the revenue breakdown by the city this time. All I have to do is grab the years and the months from the rows, replace it with city, and I am going to order this in descending order by the sum of revenue. And instead of having a table, I will actually include let's say a column chart, and I think this looks so much neater. It's easier to see which city had the most revenue, which in this case is San Francisco. Now I guess another thing that we could answer that's not here is we could show the revenue breakdown over time by city. So let me select the entire pivot table again, press ctrl c, and then ctrl v to paste this in. I'll put cities on the columns, and this time I'll grab my date again, remove the exact date and the quarters. I am going to right click, expand the entire field, and then I am going to insert a line chart. And there you go, that's revenue breakdown over time by city. So pretty cool. I know it wasn't one of the three questions that I listed here at the top, but this is how you can answer that question specifically for example. Next up we're going to create a simple report. So reporting is step four in the video, and what you will be able to do by the end of this is you will be able to create a drop-down list where you can select the manager, and then the revenue values will automatically populate. So how to create a data validation drop-down? Well that is actually not that difficult. So I have a pivot table here, and I am going to change the product to manager. So these are my unique managers. I only have five of them. And then I go into the report worksheet, go to the data tab, and then I go to data validation. And I will say from a list. I just need to pass in the source, so it's going to be these cells. Hit okay, and now you can see that I can select any manager. So I'll select Lina for example, and I would like to see the revenue numbers for Lina. To be able to calculate the revenue for each manager based on the product type, we'll need a SUMIFS formula. So SUMIFS, the first thing I'll pass into this is the sum range. So back to the orders data set, go to the revenue column, and then I'll hold down control and shift, press the down arrow, and then comma. You can see that Excel gives me hints on what I should input into this function next. So criteria range one for me would be the manager column right here. Control shift, I'm going to hold it down, press the down arrow, comma, back to my report worksheet, select the actual manager that I would like to see the revenue for, and then I will press F4 to lock in the cell because I don't want this cell to move in the formula. Press comma again, back to the orders data set, criteria range two would be the product type. So hold down control and shift, press the down arrow, hit comma again, and then criteria two in this case would be burgers. But I want to drag my formula downwards, which means I'm going to lock in the column, but not the actual rows. So I'll press F4 once, F4 twice, and F4 three times, so you can see that I have $B5, which means I locked in B as a column, but not the rows. Close the brackets, and then as I drag down this formula, you can see that it will automatically populate, and you can press F2, by the way, to click into the cell, and then you can see which cells it's using in the formula. So as I go down and I press F2 again, you can see that my formula is dynamic and worked really well because I'm selecting, in this case, lena and sides. And if I go up here, I'm selecting lena and pizzas. So I can do a total on the bottom, which is obviously just going to be a sum of everything above it. Let me put this into bold, for example, and you can see the total here as well. Let me just give it a number format that looks a little bit nicer, remove the decimals, and there we go. So if you select different managers now, these values will automatically update. And one formatting thing you could do is you could select these values right here, so these five values, and then you could go to conditional formatting, data bars, and give it some gradient. So I'll give it, let's say, a yellow gradient fill, and then as the values change, you can see that the gradients will change as well with them. I really hope you found this video useful. If you did, don't forget to like and subscribe and check out everything on my website at mojan.info. If you're after a personalized data roadmap, personalized portfolio projects, or probably the best data portfolio in the world, then you should definitely have a look. Thanks so much for taking just a little time out of your day to watch this, and I shall see you in the next one.