How to make a Pivot Table

All right, it's the one everybody's been waiting for, it's Pivot Table time.

What is a pivot table? It is a way to take longer data like this, where there's lots going on, and break it down to usable chunks. I've created one, I just wanted to show you a bit of a demo.

So, on this other sheet down the bottom here, I've broken down the data to show me the ‘Products’ by the ‘Years’, and how much they were sold. I can break it down even further, and I want to say, the ‘Products’ that were sold by just 'Daniel Scott', the sales person, or maybe, 'Ben Samuels'. So it's a way of taking this data and feeding it into a sheet. And we have to kind of re-jig it to prove a point to extract data, to see it in a different way. So let's go off and build our pivot table.

To create a pivot table, open up the exercise files, there's one there called 'Pivot Table Data 1'. Open that up, and what we need to do is, have our cursor anywhere inside this data set. You might have to select it if you've got lots of different groups of data you're working with. In my case, it's just one big chunk, so I'm going to have it anywhere in here, and I'm going to go to insert, and this first one here called 'Pivot Table'.

Everything is fine, this one here, ‘New Worksheet' is just interesting. So we've got our workbook which is the Excel file. Within this workbook, we can have worksheets. I've got 'Sheet1' down here. And you can have multiple ones in here, it's just like pages in a document. So it's going to create a new one of these.  I’m going to click 'OK'.  And you'll see down here, here's 'Sheet1' still, with all my data, but I've got this new sheet too which is going to have my pivot table on it. Let's just name it, let's double click 'Sheet1' and call this one 'Raw Data'. And 'Sheet2' is going to be my 'Pivot Table', just so we know.

What we need to do is, we're going to use these pivot fields here. This is the most interesting, or important thing when you're building a pivot table. We're going to use these fields down the bottom here. The most important ones are rows and values, you'll use these the most. We'll use columns a bit more, and filters even less but rows and values. So rows are this. So the ups and downs, the Y-axis, the vertical. I'm going to put in, say, my 'Products' into here. So you click, hold and drag them into rows.

You can see here, it's listed them down here. So we're cleaning up our data from that nice, big raw data set, and kind of just pulling the bits we want. So I want to know to know the 'Products', and I want to know how much was 'Order'ed, the values. You can see here now, I've pulled from my raw data. I've pulled out the 'Products', and how much was spent on them. I can use a lot of these.

Let's say I want to get rid of the 'Products'. I can click on it, and there's one that says 'Remove Field'.Actually I want to find out actually-- from my 'Sales People', there he is there under 'Rows'. I can see, the rows down here, this is the totals that they've earned as sales people. I can 'bin' that one as well. I find rows and values often gives me most of the things that I want.

Let's say we're looking at our customers as well. These are the customers, and this is how much they've ordered. That's rows, down the side here, and the values. Columns is kind of when you want to break out even further. Say my question is, I want to find out ‘Products’, and I want to know in which ‘Quarter’ of the year they were sold. So, I'm going to 'bin' this 'Customer1'. And I'm going to drag in my 'Products'. And in the columns here--

So that's 'Products', but what I want to do is add some columns along here. 'Q1', 'Q2', 'Q3', 'Q4', so I can put in 'Quarter', and it breaks down my 'Apples', and when they were 'Sold'. So, in 'Q1', 'Blueberries' were sold, but there were no 'Apples' sold in 'Q1'. It might be nicer for this one, instead of 'Products'--

I’m going to remove this one, I'm going to say 'Sales People'. So there's the sales for 'Daniel Scott' in this quarter, and all the quarters, plus their 'Totals' at the end. By far, 'Daniel Scott' is the best sales person on the planet, good work, Dan. Poor Tayla! But that's all right. So that's a pivot table, and there's a lot of--

When you try to work it out you'll know that dragging it like this means this here, or that there. Does that look better? Sometimes you end up with the 'Product' along the top here. And I'm going to get rid of this one. Kind of, doesn't work. Kind of a strange looking pivot table. So you end up swapping these around, so don't worry. Often, their values are the same. It's the price, the quantity, the units sold. It's their numbers, it's their digits. And these columns and rows. Rows mainly, and then columns to kind of break out of it with more detail.

Now the last one here is filtering, and I find that I use this not very often. I'm going to show you two other ways of doing it. It just means I can filter it even further because I've got my X and Y-axis done. So what I want to do is, I want to kind of-- I want to break it down, actually I just want the results for Apples’. I don't want the rest of the data.

So instead of filtering it, what I can do is, you can see, here it says 'Row Label', this drop down here. And I can untick 'Select All', I just want to show 'Apples', and it breaks down that content a little more. And 'Apples' were only sold in 'Q3'. Or I can go into here, and say, actually not 'Apples', I meant, I want to check on how 'Boysenberries' are going. Boysenberries were sold in all four quarters. And it's their grand total, so we're kind of filtering down.

So you can use these little filters. It doesn’t matter if you're doing it up here. I'm going to turn all these   back on. And you can see, up here, I can say I just want to filter by 'Q1'. Let's say 'Q1' and 'Q2' because 'Q3' and 'Q4' haven't finished yet, so we don't want to add those 12 totals. So you can do these filters thatare within the labels here. I'm going to turn all these back 'on'. So when's a good use to use these filters? It's when you want to add an extra filter. That's actually not displayed here because we're filtering by our'Quarters', and by our 'Products'.

Let's say you want to filter by the 'Sales Person' now, but we can't because it's not there. So this is where filters are useful. So in here, I'm going to add 'Sales Person'. You see along the top here, there's this extra filter. Something weird plays for me up, I don't like it sort of at the top here. Drop down here, and I’m going to say, I want to see what Daniel Scott's doing.

It's kind of this third filtering I've got. I got my 'Quarters', I've got my 'Products', but I want to filter it a little bit more. And I want to break it down by 'Sales Person'. Now, I don't really like filters just because there's a nicer way of working with this same function here. So I'm going to drop this down, and go to 'Remove Field'. And we're going to use something called 'Slice', or 'Slicer'.

I clicked in my table here, I'm at 'Analyze', and there's this one here called 'Insert Slicer'. I'm going to pick the exact same thing I had before, 'Sales Person'. And it gives the exact same details, but on a cool little push button version rather than this ugly thing at the top that's insignificant.

So I can say, actually I want to go by Daniel. I want to figure out what Tayla's doing, and I want to see what Ben's doing. The same thing, it's a filter. They call that a slicer. And it's this cool little box out here, you can click multiple things. I want to find 'Ben', so there's this option here. I can move out 'Ben' and 'Daniel'. It gives me those options, and I can clear it out as well, clear this filter.

To get rid of it, I can click on the edge, hit 'Delete', and that gets rid of the slicer. You can have more than one slicer. You can puddle them up next to each other so you can make some cool combinations if you need to. So, I want to find out who earns the most as a client. So, what I'm going to do is I'm going to pick my 'Clients'. You don't see 'Clients', it's 'Customers'. Drag him into here, get rid of my 'Products'. And I want to find out their-- actually I'm just going to get rid of this one as well. So this gives me all my clients, and how much they've earned. And I can either just look through, and see where this client is, or I can use my more traditional sorting functions. So I can click in here, and I can say--

Actually I want to sort it by largest to smallest. These guys here, 'Fabrikam' have been the best client for me for that data set. But say I want to break it out a little further. I want to know, across the quarters, who the best is. So I click on 'Quarters', and I'm going to drag that into columns. So I can break it out a little bit.

Couple of things to know when you're working with pivot tables is-- I don't like these little labels, they're cool for re-sorting things, but once you're done with them they just kind of confuse the pivot table. So, under 'Analyze', there's an option here that says 'Field Headers', and I'll turn this off. The graph's still perfect, except you don't have these little things you can re-order by. And nothing, it just cleans up the table.

So, up to you whether you have those on or off. If you do lose this thing on the side here, this 'Pivot Table Fields' list, it's this option here, 'Field List', you can turn it on or off. Also, if you lose it, it's probably because you got your cursor clicked out here. Click over this, double check that's on.

The other thing, and probably the most important, and I like to save this till the end, is that, if you'veupdated your raw data, weirdly, your pivot table doesn't update automatically. So I go into here, and I decide that actually this was wrong. And I put that to '0', I've copied that across, lots of them are '0' now. Even more to make it more obvious. I hit 'Save', and I go back to my pivot table, it's not updated. So what I need to do, whenever I update my raw data I click in here, I go to 'Analyze', and I go to this one that says 'Refresh', and it will go and re-jig it. So that's a pretty big one for the pivot table. A big marker right next to your desk. Raw data did not update pivot table.

So that my friends is how pivot tables work. I'm going to set you a little exam, a little project. I would like you to create a pivot table that shows me who the best sales person of Boysenberries were. I want you to go through and create a quick little pivot table, just to show me. There's a couple of ways of doing it, and how to display it, so I'd like you to display it, and send me little screenshots. Stick it in the comments, or links, any which way, Dropbox, any which way you can. I'd love to see it.

So that's it for pivot tables. Let's get on to the next tutorial video.