 Hello everyone and welcome back to another tutorial. In this video we're going to be looking at, well, in a short video, but very in-depth, pivot tables. So how to get started with your very first pivot table and maybe some tips and tricks to pick up on the way if you're already familiar with using them. But before we do that, do make sure you subscribe to the channel and hit that bell notification button because that way you'll be notified of all videos that come out in the future. Right, so jumping in with pivot tables, the first thing we need is some data. So you'll see on the screen we've got some sales data that we're going to use to public or populate our pivot table. We'll put a link to where we download the data from because it's just randomly generated data in the description for this video and hopefully the link will be on the screen as well. So you can download for yourself and give this a try at home. So the first thing you do is insert our pivot table and the quickest and easiest way of doing this is we're going to go to our desired location, which for us is a separate sheet called summary. And we're just going to go to cell reference A1 and then once that's selected we'll go to insert and we're going to select the pivot table option. You'll see there's a recommended pivot tables option here for us that we can select. If you were to select that Excel would give you some recommendations on how it feels best to present your data. So I'll give you one, two or three or four options on templates to use. But for us, by all means, please feel free to use that and you can see for yourself. But for us, we're going to use the pivot table option on the left. Once we've done that, all we need to do is now select our range of data. So you can either select this button here so you get the minimal view so you can see more what you're doing. Or if not, you just need to be selected in here to select the data. Just go to your whether data is stored and for us it's in this sales data sheet. And all we're going to do is obviously go to the last row or the last piece of data and highlight everything from the first row to the last row. You can just select the whole columns. So it could be like this as you see on the screen now. So A to G, but the thing will not the thing. But the problem with this is by doing so, you'll get blank references. And that's in reference to obviously the blank cells you see beneath this table. There are benefits to doing both because obviously if you select the whole column, then any new data that's added into your table, you can automatically refresh and it will be pulled through to your pivot table. But obviously by using our defined or set ranges we're doing here, if any new data was added, we would need to update our range to populate our pivot table. But we'll be covering another video on how you can get around that with using a dynamic range for your pivot table ranges. But again, this is why you need to subscribe to the channel to make sure you don't miss that video when it does come out. So we've selected our range. Once doing that, we can just double check to make sure we're happy of where the pivot table is going to be populated. So this is where we could select a new worksheet location or just another worksheet where we want or another location where we have. But we can we're happy of where that is. So we need to do is go OK. And we can tell the pivot table has been added in there because we've got our default area range here in column C to row 18. And we've also got, and again, this only happens when you select within this range here. As you'll see by me clicking out and then back in again, we'll get our pivot table fields down the right hand side. You'll notice that our pivot table in row two or three here has been titled pivot table five and you'll see it again at the top here. By default, when you add your first pivot table, it'll be called pivot table one. But obviously for us, this is not the first pivot table we put into this workbook. So that's why we will see in pivot table five and again, why you'll probably see pivot table number one and not five. So to get started and put some data into our pivot table, you can see over the right hand side, we've got our pivot table fields. The fields are at the top and beneath that is the four different sections to help populate our pivot table. So the first thing we're going to do is scroll to the bottom to our total and we can now drag that down to our bottom right hand corner that is for values. So the first thing we achieve by doing that is we can now see our total or our total sales, which is 19,627. What we can do just to make that or tidy up and do some formatting with that number is if you right click on the sum of total and go down to field settings. And then you can see we can now do a bit of formatting. So we can go into number. I was going to go into the number or go into currency. We'll stick with pounds. I'll get rid of the decimal places, select OK and go OK game. And you can see it's a bit more, a bit better there. We've got the comma, we've got the pound symbol and it's a bit more in a readable format. So we just todded off that sum there. The next thing we can do or want to do our pivot tables to start bringing some other fields to help give some more meaningful information to our pivot table. So what we want to do, the first thing we want to do is we want to know, OK, what reps have been doing some sales for us. So we can bring into our reps into the rows section and you can see how automatically the rows or column A is populated with all our different reps names. And we can see the total sales that they have achieved. So we've got Andrew did 438, Jill 1700 and so on and so forth. So we've got that first piece of useful information so we can see how our total sales has been broken down by each individual. The next thing we're going to look at is columns. So rather than just doing it by one piece of information being named, let's do it by two. Let's look at total sales by name and also what items they have sold. So in order to do that, if we just go back into our field list, we can find our item field and we're going to drag that into columns. So if I just drag that into there, you can now see how this breaks out. So we can now see we've got the name down the left and across the headers at the top there. We've got each separate item so we can see what the total sales per item were. So we could look at binder and we can see that for binder, the total sales are at the bottom here of 19 and a half thousand or not 19. Sorry, nine and a half thousand and obviously how that breaks down. So we could see who did the highest sales and it appears to be Jardine and who did the lower sales, which is Howard. So again, another interesting and useful piece of information for us to use. The last part of our four sections we have is the filters. So for this one, we'll use region. So we'll drag region down into our filters and you can see we've got this drop down now populated into our pivot table. What we can do is go to our drop down and we can see each of our regions and by default, all three are selected. But if we only wanted to look at central, we can select central and you can see how that breaks down there. Or if you wanted to look at east, we can then select east and remove central or we can have a combination of east and west or all three if you require. So again, that's a filter. What again is very useful. So the next and final piece actually before getting onto that another part just to mention and use is through lack of having not very many options or fields in our table available. I'm just going to get rid of region for my filters and to get rid of region or any one of the other options we have here, all you need to do is just deselect it from the fields list up here. What we're going to do now is I'm going to use region and I'm going to drag it down into our rows section. So you can now see that we're able to bring multiple fields into our row section to do grouping. So we can now see in row five here, we can see for central, we can see the region central, we can see all the totals. So we know there's a total sales of 11,000 from central as a whole. We can then see obviously what the total for central in binder sales was 5,700 and then we can then see how that 5,700 breaks down for each individual who sold binders. So we're now getting some extra dimensions into this pivot table to really understand the data we are looking at. So to get to that last point I was going to mention and we're going to look at dates because we know we've got dates available to us in our table. So to do so I'm just going to remove region and rip and get back to this default view we've got here. So we've got some of our sales obviously for each item. Because we've got order date available to us, we can now bring order date down into our rows and you can see that it's automatically grouped our dates into years, quarters, and then the actual order date itself. So at the moment we can now see how the total sales are per each year. If I was to expand one of those, so let's say 2018, we can now see that we've got broken down by quarter one, quarter two, three, and four, and then if we were to go down one more, we can now see our actual date summarized by month. So this is again a really useful way of summarizing our data and actually an even better way of maybe using dates can be to break this down to separate. So let's take years into our filter and let's maybe take, let's get rid of quarters, let's get rid of quarters so we'll drag that up that fields option. So we can now see January through to December, so our total sales, but we can now if we wanted to we could select just 2018 or we can select just 2019. So a bit bit messy way to do it, but just another way to show you how you can now use these dynamic fields throughout your pivot table. So we hope that video was very useful to and it taught you how to get started where you're very first pivot table. If you do have any questions at all, please just leave us a comment below this video and we'll get back to you as soon as we can. Alternatively, there's a couple of links in the description to this video. So we've got our Facebook, Instagram, even our website. So if you prefer to get in contact with us there, then by all means again, send us a message there and we can obviously get back to you and answers to your questions as soon as possible. One more time. If you haven't already, please do subscribe to the channel and hit that bell notifications. You're notified of all of our future videos. And lastly, if you haven't already and if you did find the video useful, please or well, if you hope you did find the video useful. But nonetheless, if you could give the video a like, it'd be greatly appreciated by us. Not only does it help show us what content you like seeing and what you want to see more of. It also helps us with the YouTube algorithm so that we can reach more people and show them these this Excel skills. So thank you very much for watching and we shall see you in the next video. Thank you for watching.