5.2 Interact with a PivotTable - Video Tutorials & Practice Problems

<v Instructor>We've completed our basic pivot table</v> which we did back in the previous sub lesson, and now we want to look to interact more with the existing pivot table we have created. So what we're gonna do here is we're gonna put our mouse inside of the actual pivot table so that our pivot table's fields list pops up. And now we want to do here is look to put some additional information inside of the pivot table. So what we're gonna do here is we are going to grab our product categories, so we're gonna go ahead and find the product category, and we see that it is already in there. And what we want to do is go ahead and add in the sales territories above it. So I'm gonna go in and grab my sales territories and put it right above the rows. So as I'm getting ready to drop it in the rows area, I can see that just as I get in, if I'm right above the product category, a little green line shows up and I can see that I can actually drop my field in there. And now I get a breakdown that looks something like this right here. So we can add multiple fields to the filters, rows, columns, and values. And we're gonna see that over the next little bit here. Next, what we're gonna do is we're gonna go and grab our order type. So we're gonna go here and find the order type. And I can see that searching through this list is a little bit of a pain given that it is not in alphabetic order. So right now the list is actually sitting in the order that the data came in in the source. If I want, I can actually go up here into my pivot table tools, click on this and show the sorting in A to Z like so. So this way, now if I'm gonna go and find a column like my order type, I can actually go to the Os 'cause it's in alphabetic order and go to here and drag it into the filters like so. That just makes the navigation a little bit easier I find. Okay, so now that we've added the order type in, what we wanna do here is we are gonna go to the pivot table and under the dropdown box we are gonna choose the online order. So I'm gonna go here and filter this pivot table down to online orders. And now we're gonna see it react to that particular filter. And now let's go ahead and add a filter on our territory. So what I can do is go over here and click on this dropdown box right here. And now what I want to do is I'm just gonna choose Canada and Australia. So I'm just gonna click the Select All, go Australia, Canada, click Okay. And now my pivot table is showing me the sales territories of Australia and Canada for the online order types. So I can continue to interact with this in different ways. So I've gone through and I've completed that filtering, but what if I wanted to go ahead and get rid of all the filters, oops, that I've actually applied to my dataset? So let's go back over to my pivot table here. And with my mouse inside of the actual pivot table, I can go up to my pivot table Analyze tab, and under Actions I can choose Clear here and say Clear Filters. So if I wanna clear all the filters right now that are set for this particular pivot table, I can click on that. And now we get back to the unfiltered state for that particular pivot table. Okay, so what we are going to do next here is I'm going to remove the sales territory from the rows. So I'm just gonna go over here. And to do that I can click on it and say Remove Field like so. Or what I can do is click on it and drag it into the middle, and we'll see that it turns as a red X. Or I could have actually gone up here into the fields list and just simply unchecked the box there. So there's a number of different ways to get rid of the fields that you may choose to. So next, what we're gonna do here now is we are going to go in and we're gonna take our order quantity and we are going to drop it into the values. So I'm gonna go ahead and find my order quantity field like so, drop it into Values. And now we can see that I have multiple different aggregations that I can place in here, and they're showing up on the actual pivot chart. So I can see for 2019, I've got the Sum of Line Total here and the Sum of Order Quantity, all are both paired together in 2019 and then the same thing in 2020. So we do have the ability here, if I just make this a little bit larger here, we can see that when I drop the sum of order quantity into the values it actually put a values indicator up here in the columns. I can take that and move it up above the actual other values here. So we can see now my pivot table adjust to actually show the Sum of Line and then the two different years down below, Sum of Order Quantity and the two years down below. I can actually take this values and move it down over here to the product categories if I want to show it like that. So there's a few different places where I can go and actually move this to. But what I'm gonna do here is I'm just gonna put it back to its default location under the category. So I'm just gonna take this and move it over here and set my pivot table like so. And that brings an end to this sub lesson.