 Hello everyone and welcome back to another tutorial in this video We're going to be looking at pivot tables and more specifically how we can Use the sum average and count functions within the pivot table to give us the desired summaries We need without having to do any complicated Calculations or just having to add anything extra to our data So we've got a basic data set we're going to be using and we've used this one previously Simply it was just downloaded from a website. That's particularly good at providing some sample data So again, obviously on that note. This is sample data. There is no personal information being used in this It's all randomly generated. So as with all of our data, if there is any Coincidence where that data does look genuine. I can assure you it's not. It's just randomly generated and it just be just by chance So what I'll do is put a link to the website in the description to this video So if you want to download that data yourself, you can do So what we're going to do is first we've put our pivot table in So if you're not sure on how to insert a pivot table, you can check one of our previous videos around pivot tables That will show you everything you need from start to finish What we are going to do is we're just going to simply use the region section or the region data point So I say so we've got central east and west and then we're going to just bring in the total and total for us is just what the sales total is and You can see by default. It's just going to give us that sum of total So if you wanted to have your total sales by Obviously by region All you need to do is drag it in and you can see clearly just by down the right hand side here You can see it summarizing by the sum of total But let's say you wanted to do a count so you can see the total number of sales that you had in central Using just this total field or maybe you want to look at what the average sale was just by using again this field So what we can do is we can just bring well We can either use the same field or we can bring that field in multiple times to derive each of these Circumstances and that's exactly what I'll do. So I'm just going to just drag total back down here again and once more So you can see we've got three iterations of the sum of total and at the moment. They're showing at the same for each So let's say in this first one. We actually want that to be the count the number of sales So it's going to literally count the sales total for each by region So all you do is if you go into our information button and You can see that at the moment it pops up and we can see summarized by And it might depend on the version you're using. I'm actually even using Microsoft x Microsoft excel on a Mac today But you'll get exactly the same view Well, the principle will be exactly the same although the view might be slightly different And you can see at the moment you can see some has been selected underneath this section of summarized by So all we're going to do here to get our count is simply go into count Select okay, and you can now see in the bottom right hand corner It's been updated to show count of total and you can see us back in column B here It's now been changed to count of total and you can now see the total number of sales by region So central had 24 east had 13 and west had 6 Go move on to the next one All we need to do is if you go back into our information button But this time for the second instance we have the sum of total and this time we'll go into average So we can go okay, and you can see as quickly and easy as that it changes everything to average so we can now see what the average sale value was and Well, okay, it's like I was ain't gonna say it's not very different at all But yeah between central and west you can see there's not a great difference in the average sale But they are both quite a bit higher than the west obviously by about Trying to do some quick math here about 40 just less than 40 pounds So what we can do with this one as well is we'll just quickly change our formatting So we can do that simply by going to our information button once more and this time we're going to the number section And we'll just select currency and we'll get rid of the two decimal places and go okay Okay, again, and I'll just do that one more time for our sales total just so it tidies up a bit And once again, if you're not familiar with how to do this and I may be rushing through this quite quickly at the moment All you need to do is go and check out our previous videos that go into a lot more depth on how to do the formatting pivot tables And you can see I just make so look a bit better in our table for obviously reading when we're talking about pounds and currency Rather than actual numbers So we've now managed to get what we achieve we wanted our count So you can see the total number of sales per region we can get the average sale So we can see that that's fine, and we can then get us some of Some of our sales and so the overall total sales sold and this is particularly useful bringing these additional columns in or so I say fields because it allows us to actually understand the data a bit more because we can actually see that obviously here in West It's the basically the reason it's obviously a lot lower than central is simply because the sales volume There's a lot more sales in central than they were in West Especially when we know that the average sales was about the same So everyone is selling about the same average sale per transaction It's just that central is doing obviously a heck of a lot more sales than West is and Obviously, this is not the limit you can take this a bit further. So if you wanted to bring total in one more time You can then have these options are doing max and min So we can go into the information one more time and you can actually see these are all the options that you have You've probably seen it on the screen already, but just a touch on it You can see there's many other options that you have when using The the options available in pivot tables when you want to summarize and the other one you can do is just go into max or min I'll select max for this instance and you can now see the max sale that was Achieved in the data and this basically means this is the largest sales transaction that happened obviously It's probably made up of maybe multiple different items, but ultimately this is the largest transaction within this Overall total so you can see overall there was 24 sales, but the largest of those sales was accounted for over 10% obviously more like 15% of the overall total and once again, we'll just change the formatting of that Go back into number just so we're back into currency so it all looks familiar and Wasn't the purpose of this video But again, you've probably now got a bit more insight and you can pull in expert in going through and changing the Formatting for the values within your pivot tables. So although a quite a quick video We hope this was a great like to the point example of what you can do in pivot tables Especially if you're trying to work with your counts averages and sums This is a question we regularly get of obviously what can we do to make this easier and especially You might not want to be using a pivot table for your summary You might literally just want to use it to obtain a value Like the averages for each region if you're going to a meeting you some number that you can quickly reference So again, it's another use for them. So you don't have to use pivot tables just for when you're presenting They're also particularly useful just to have in your back pocket if you quickly need to analyze your data and obtain some values So if you haven't already, please do subscribe to the channel We've got a lot more videos that are coming out going into more depth with other formulas and functions And also we'll continue to look at pivot tables and allow you to understand the benefits and the additional tricks that are available with them If you haven't again already, sorry, I've said that twice now Do give the video a like it not only allows us to see which what content you like and would like to see more of It's obviously also very beneficial for the channel as well So thank you very much for watching and we shall see you in the next video Before you go, don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlists so you can see these categorized together So make sure you check those out and get all those useful information and obviously as always Don't forget to subscribe and hit that bell notification button