 What's going on everybody? Today we are starting our Excel tutorial series. Now there are so many things that you can do in Excel, so I don't know how long the series is going to be. It could be 15 or even 20 videos. But what I do know is that I'm going to be covering just about every single thing that I've used since I became a data analyst, and I want to show you how to do it. So it won't just be the more concrete things, you know, like pivot tables, charts, need lookups, things like that. It'll also be some of the more nuanced things like how to deal with missing data or how to deal with dirty data and how to clean that up within Excel. And so those are things that you may not be able to do, you know, if somebody wasn't showing you how to do it. And so that's what I'm going to try to help you because I know that that is something that you will need to do or learn how to do in Excel. Now, before we get into it, I want to give a huge shout out to the sponsor of this Excel series. And that is Udemy. I took so many Excel courses on Udemy when I was first starting out as a data analyst. And there was this one course that I kept going back to over and over again, because as I got into it in my job, I realized that there were so many things that were in that course that I really needed to know, but I didn't realize I needed to know it. And so I'm going to put the links to those courses in the description in case you want to take those. Again, huge shout out to Udemy. Without further ado, let's jump out of my screen and get started with our very first Excel tutorial. All right, so I'm going to go ahead and get rid of myself. We are going to be looking at something absolutely pivotal in your data analytics career. And that is pivot tables. And I think that's really appropriate. It is probably one of the most commonly used things I think that data analysts use to convey information in Excel. It's super easy to group things together to display information in a very easily understandable way, especially for people who are not data analysts, right? I use this a lot for other managers or for higher ups who don't want to get into SQL or aren't super tech savvy in Python or Tableau. They just want it in Excel. And so I use it all the time for that reason. And so we're going to be using this data set right here, bike store sales in Europe. I will include this link in the description. We're not going to look at the columns just yet. We're going to download it. I've already downloaded it a few times. But we are going to go to our downloads. We're going to open it up. And we're going to open up this sales right here. And give it a second. All right, perfect. And so here's what it looks like, at least on my screen. I'm going to spread it out just a little bit. And really quickly, let's take a very quick glance at this. So we have a date, a day, a month, a year. So some date information. Then we have some customer age information. So how old was the customer? Again, this is bike sales. So what did, you know, what did they buy? And they have some demographic information. So this is their age group. We have the gender, country, state, the product category, the subcategory, the actual product that was purchased. And then we have things like, you know, how much these things cost the quantity that was ordered. So we have order quantity, unit cost, unit price, then we have the profit, cost, and revenue. All things that we almost everything in here, we can in some way put into a pivot table. Now I'm not going to go through every single variation of that, but we are going to be looking at a lot of this revenue over here. Because I think it's pretty easy to show the value of a pivot table with, especially with, you know, currency or money. So what we're going to do to get started is we're going to go up to insert, and we're going to click on insert. And then we are going to click on pivot table. Now really quick, there is a recommended pivot tables. And if you click on that, what will come up is some recommendations that Excel gives based on the data that you have. And it can kind of give you some ideas of what you can do with pivot tables. It's going to generate it for you. We're not going to do that. We're going to build our own. But let's click on pivot table. And it's going to auto select basically everything. And that's fantastic. But what if it doesn't come like that? I just erased that. If it doesn't come like that, you can click right here. You can click, excuse me, you can click control, shift, and then the right arrow and then the down arrow. And that is going to select all of our data. And you have right here a new worksheet or an existing worksheet. We're going to create a new worksheet. Just tends to get too clogged up if we put it on the same worksheet that already has a lot of data in it. So right over here are pivot table fields. And these are all of our columns that we just looked at. And we're going to be able to select those and kind of drag and drop. Now, if you just took the Tableau tutorial series that I just finished doing last week, then this is going to be pretty familiar. You're going to start seeing a little bit of hopefully some patterns about how the data is kind of displayed. And so we have our filters down here. We have columns, rows, values, all of these things we will be using. I'll show you how to use today as well as some additional things. One thing that we want to start with for this demonstration is we're going to be looking at kind of the these bottom ones right here, profit, cost, and revenue. And we're going to be doing that per country, per country and state. And we'll kind of do some drill downs. And I'll show you how those work. So just to start out, we're going to take the country right here. And you'll see it populate right over here. In fact, let me zoom in maybe once. Yeah, that should be fine. I don't know if I want I might zoom in again in just a little bit. So we have our country and it's just like this. Very, very simple. Oops. Now I'm going to include the state. Now I'm going to drag this all the way and I'm going to put it under. You can put it above or you can put it below. I'm going to put it below. It definitely makes the most sense there. Now when you do that, it kind of populates it in an expanded way, but you can collapse this very easily. We're going to go right here. We're going to right click. We're going to go down to expand and collapse. And we're going to collapse the entire field. And so now here are all of our all of our countries as they were formed. But now each of them has this plus sign to the left. And if you click on it, now we can go and we see this state that we added to these rows. And what this is going to do is it kind of is like a roll up or it's like a grouping. And so if you have taken the sequel tutorial series and you've done things with group by, this is very similar to that. And if you've done the tab load tutorial series, it's kind of like a drill down. It's very, very similar. So you can drill into the information. So we can put some values in here. And what that's going to do is that's going to kind of create some context to what this, what we're grouping by. So just for visual purposes, let's add this revenue. So this is the revenue that bike, bike sales revenue, right? That's what we're looking at. So this is the sum of the revenue for these bike sales per country. Now, if we drop down right here, we can see that in Australia, New South Wales had 92, 9,203,495 dollars. Queensland had 5 million, you know, etc, etc. So now we can break it down. We can't, we don't just have to look at Australia, we can now drill down even further to the actual state is what they're calling it, the actual state within Australia. And so it's super, super useful and you can do that for every single one. And so we can look at Canada, we can look at France, and we can really drill down into the revenue for each of these countries as well as the states within them. Now over here, this is not the most pretty, it just says sum of revenue and then it has some numbers, not the most pretty thing I've ever seen. Really quick, we can go like, we can kind of highlight over these and go back to home, you can do it in a couple different ways, we can go to home and we'll type currency. Now it has these two dot zero zeros at the end, you can get rid of those really easily by going like that. Already, this looks quite a bit better just visually, especially if you're looking at it in, you know, dollars, you can change the currency to different currencies if you want to do that. Now we don't just have to do the sum of revenue, we can do a lot of different things. So let's go to the value field settings. So we can customize this name, so we can do revenue, oops, if I could spell revenue per country, that's fine that you know it's just a placeholder to try to show you, but we don't have to just do that. You know, we could do the count, the average, the max, the min, we can do just about anything we want, but let's keep it the sum right now. And if we want to, we can show this value as different things. So we percentage, percentage of column total, percentage of row total, let's do really quick just for demonstration purposes, the percentage of grand total. So when we do that, we can see that the United States, the per revenue per country, United States has 32% just between these countries and Australia has the next one. So it might be kind of hard to glance at this really quickly to know who has the highest, but what we can do is we can go right here, we can go to sort, and we can do largest to smallest. And there we have the United States on top. Now when you do it right here, it's not sorted largest to smallest, you'd have to go in again, like sort and do largest to smallest. And so now we can see that California has the, has the, you know, biggest percentage, they're pulling in 20% of that 32% of revenue. So I'm just going to click control Z a few times and get us back to where we just were. And what I want to do is I want to show you a few different things pretty quickly. So we want to pull in this profit and this cost. And so I'm going to pull in this cost next, and then I'm going to pull in this profit again. I'm going to change the currency on this. And I'm not going to change the names right now, but you absolutely can do that. Now the revenue is the, how much is actually being sold? So, you know, for the United States, it was 27 million. Now the cost is how much did it cost to manufacture or store or distribute all of these products? So that was 16 million. And the profit is actually how much money is being made at the end of the day after, you know, all their costs after all their employee costs after everything, they're still making the United States is still making $11 million. Now you might look at this and you might say, well, you know, I can kind of glance at it and say, know that this profit is correct based off these two numbers. But we can do a calculated field. If you remember what calculated fields are, that's something from Tableau, very basically the exact same thing. And so we can create an additional column right here that is a calculated field that can add and subtract these things to make sure that our numbers are adding up correctly. So let's do that really quickly. Let's go to Pivot Table Analyze. We're going to go over to Fields, Items and Sets and go to Calculated Field. Now we can name this anything. And I'm just going to, for demo purposes, I'm going to say, oops, Calculated Field Demo. I'm sure yours will be different. Now if you want to, you can go in here and this is the formula. It's almost like, you know, we haven't looked at formulas that this is our first tutorial. But you know, when we look at formulas, it's basically the same thing as writing it inside of a cell. But here it gives us kind of this open text to do how we do what we want with it. Now what we're going to do is we're going to do revenue. I'm going to insert that. I'm going to get rid of this. I'm going to do revenue. And so that's the very large number. And then we're going to subtract. And we're going to subtract our cost. I'm going to insert that. And let's do this and click OK. So this is our Calculated Field Demo column that we just created. And as you can see, it matches our sum of profit column exactly. And that's exactly what we want to see. We want to kind of check to make sure that this revenue and cost fields are generating the correct profit. And sometimes those are off. And so it's really good to kind of check those and have that additional column. You probably wouldn't have this if you were, you know, going to submit this to somebody. Just so you know, now that this is an actual column, you can't go here and do something like cut or and paste it over here. You know, that's not, I won't let you do that. What it is, is now an actual column. And so we can go and remove that. And we can add it back at any moment. So if we want to go back and add that, oops, add that down here, we can do that because we've created that column. It's now permanently there unless we go and delete all of that data. And so we can just click this check mark and it will get rid of it for us. All right. Now, the last thing that we have not used down here is the filters. Now, the filters is exactly what it sounds like. It's going to allow you to filter on certain things. But probably not things that you already have included in your pivot table. So if you add something like the country down here, it's going to kind of expand everything. And then if you then go and filter on it, it kind of breaks it down. That's really not what the filter is kind of used for or meant for. For example, right up here, we have customer gender. Okay. So let's take the customer gender and we'll put it in this filters. Now we can see all of the revenue, all of the cost, all of the profit, and we can do that based off of the gender. So we can filter by a gender, not really having to change anything about our pivot table. And so at a super quick glance, we can see that the males are the profit from the males is 16.487 million. And the profit from the females is 15.733 million. So at a super basic level, at a really quick glance, we can see that the men or the males are spending a little bit more than the females by about $700,000. Now let's go ahead and create one more pivot table. We're going to create a pivot table right over here. Let's go back to the sales right here. Again, control shift right down. It's going to select all of our data. And we're going to click OK. So one thing that we're going to look at is we're going to use some of this date information right here. So let's select our country just like we did before. And what we want to do is see, you know, what year were we performing our best? When were we doing our absolute best with oops, let me go back with our sales. So I'm going to select the year and put that in our columns. And so now we have 2011 through 2016. And we want to look at our revenue. Let's put our revenue right down here. And now we have all of our revenue knots. Again, make this into a currency just like that. And super quickly, now we can get a really quick glance at how Australia was doing each year. And we can see that there was a huge uptick in 2013 and a huge uptick in 2015. That didn't happen for every single country. It did go up for most countries very slightly for some. But we can see on a large scale from year to year what that's like. And so within just a few minutes, we're able to create some really useful pivot tables that anybody could look at and understand. And that's really the biggest use of these pivot tables is that you can kind of group these things together, show some information and data at kind of a broad larger scale, and make it to where anybody who's looking at it can understand it. That is why pivot tables are so useful. And so I hope that this video was helpful. I hope that I was able to walk through it and help you better understand how pivot tables work and how you can use them when you are working within Excel. Thank you guys so much for watching. I really appreciate it. If you like this video, be sure to like and subscribe below. And I'll see you in the next video.