Okay, so let's have a look at how we build a simple pivot table. So the first thing is we need to have our cursor somewhere in the data that we want to pivot. So when we're gonna produce our report, the data here is the one we're going to use. So we've got a transaction number, salesperson's name, a data transaction, the product they've sold, the number of units and the sales amount and the location. So we're now have to start asking a few questions of our database. The question we're gonna ask first, the example we're gonna use is I wanna know how much each salesperson has sold. So the quantity of the total sales amount that they've sold across all products, all time. Okay, let's gonna start with that as our example. So as I say, we get our cursor somewhere in the database and then we're gonna go to the insert menu. So hit Alt, and then N and then you should have tables as an option. And then V for pivot. Now we're gonna accept all of the defaults. So the table range, that first top option there, should all automatically be selected because we started with our cursor in the data. You'll notice we do have the option to use an external data source, but we're not gonna worry about that for this simple pivot at the moment. Where do we want to put the pivot table report? Well, we'd like to put that on a new worksheet. Try and keep pivot tables separate from the data. So don't try and put them next door, or any of those kind of things, because what you'll find is when we start formatting the pivot table it may start mucking around with things like column widths or row heights or things that we don't really want to muck around inside the data. So I recommend we keep our pivot tables on a separate sheet. So once we've got those two things automatically selected, we just click on okay, and that will take us to this screen. Now this screen is where we build our pivot. Over on the left hand side, we've got an empty area where we've got the pivot table will appear as we start to build it. On the right hand side, we've got a list of all the fields from our database and at the bottom of there on the right hand side, we've got four boxes to drop things into. So we've got a section called filters. We've got one called columns, one called values, and one called rows. Now we're gonna start off with a nice simple pivot table. We are just gonna drop in one thing to rows and one thing to values. Just have a look and see what happens. So what we're gonna do is we are gonna drag name into rows, because we said we wanted to look at the salespeople. So that's contained in our name item. And you'll see that when we drop it into the rows area, over on the left hand side, our pivot table starts to be built. So we've got a list of every single salesperson just once from our database. So it's extracted that information already. The next thing we want is we'd like to, we said we wanted the sales amounts, so we're gonna add that in. So we're gonna drag that and we're gonna put that into values. And there we go. So there is our simple pivot table. So we said we wanted to know how much has each person sold and there it is. So, couple of important things to realize about pivot tables. The way we format pivot tables is different to the way we would forgo about formatting just your regular rows and columns in our data. What I don't suggest you do is select things in our pivot table and start applying your formats like you normally would. What I suggest we do is stick to the pivot table method of formatting. And those are a couple of little tricks you need to know about where these things are hidden. The first thing is numeric information. So when we've got numbers in our database here, so numbers in our pivot table, if I want to change the number format for these things, then the way to do that is in our value section of our pivot table area down here. If we click on this area, you'll see that you get value field settings. And the value field settings item here allows us to have this number format button. So I can come in here, click on value field settings, change the number format to whatever I'd like it to be, click on okay, click on okay again. And that will have changed the number format. Okay? So do it that way rather than perhaps just using the more traditional ways of changing your number format.

Okay, so that's number formatting. How about the formatting of the pivot table itself? So things like titles, colors, those kind of things? Well, to do all of those edits, you just need your cursor somewhere in the pivot table. And then you'll notice we've got two ribbon areas that only appear when my cursor is in the pivot. So if I click away from the pivot table, you don't see those options. When I click back inside, then we do see those two options appear, analyze and design. So obviously if we wanna change the look and feel of our pivot table, then we're gonna go to the design ribbon. And this area here, this pivot table styles, is how we're gonna go about changing the design. So what we're gonna do is we're gonna click on the dropdown list here, and we're go and choose one of the pre-existing designs that exist inside Excel. Now, if you don't have a pre-built template that's used by your organization already available in here, then it might be a good idea to suggest that it's something you want to implement at least for yourself, better still, for your team, better still for your organization. So usually the way this works is you pick the one that's closest to the the one you want. Say for example, let's say this medium gray one is the one that's closest to my branding that I want to have or my look and feel I want to have for my pivot tables. To then go and modify it and add your own design embellishments, then you would right click on it and choose duplicate.

And then this would allow us to create my own pivot table style.

I could then go about changing any of the formatting elements, click on okay, and then I would have that available next time I come into Excel. I would be able to go and choose that by going to design, clicking on the thing. And you can see there's my new custom pivot table design. So I never need to go about changing pivot table design all the time. What we do is we just do it once, set up a style, and then every time we want to use it, just come in here and go and choose it. Okay, couple of other things I want to show you. Pivot table options. Just need to show you a couple of important things if we go to the analyze menu. And then under the pivot table dropdown, we look at options. Useful things to know about, grand totals. You'll notice that we automatically get a grand total. If I don't want to have one that's perfectly okay. You can turn off grand totals for either rows or columns or both. And as well as that, you might want to change the layout and format. You might want to say this one is the one that I quite often turn off, auto fit column widths on update. So if your data changes, usually I don't really want the pivot table changing its column size. I'd really like it to say fixed the way I set it. So, normally I'll turn that one off as well. So, there's a couple of things to have a look at in there. By all means, have a look at the other options in here, but there's a lot of different ways that you can change the display of a pivot table. Okay, so that's enough for our simple pivot tables. We've created a simple pivot table. We've asked a question of our data and then displayed that in a result.