5.6 Filter and sort data in a PivotTable - Video Tutorials & Practice Problems

<v Instructor>Users do not often want to see</v> an entire dataset and are usually interested in smaller, more focused slices of data. You can narrow the data in the pivot table based on criteria that has been set by applying filters. In this sub lesson, we will go through filtering data in the pivot table, which includes the label filters, value filters and the manual selection filter. Let's start with the label filters which are triggered by text-based criteria. So what we're gonna do first here is within the pivot table, we're gonna click the drop-down arrow next to sales territory. So let's go over here to the sales territory click this drop-down box right here and we will see a dialog box that we can interact with. So what we're gonna do in here is just go down to the label filters cause that's we're gonna take a look at first and we can see that on the context menu here that there are a number of different ways that we can do our label filters. We can do equals, does not equal, begins with. So picking things will begin with a certain character, text that does not begin with a certain character, texts that ends with a certain character, that does not end with a certain character and the list goes on. So you can just go through the rest of these particular options. So what we're going to do first is we're going to go choose greater than so keeping in mind, we were selected on the sales territory. We're gonna say greater than and we get prompted with a dialogue box that we can interact with here. And what we're going to do is we're gonna go in here and type the letter D. So I'm just gonna go in and type D and go okay. So what this is gonna do is get us all the labels that are greater than D. So we're gonna notice that Australia and Canada dropped off from our dataset. And then to go ahead and clear the filter after we've done that we're just gonna click on the filter boxer and say clear filter from sales territory. So in these examples here, we're gonna go through one by one and clear the filters as we go ahead and apply them. Keep in mind earlier on in a previous sub lessons we went through and showed you how in the pivot table, when you're selected inside the pivot table the ability to go in here and clear all the filters that are applied to the pivot table at any given time. Right now, we're just going through one by one. So there's really no point going back up through here and we'll just toggle them down here. So just keep that in mind as you're working with your pivot tables. So I guess it's time to actually clear the filters as opposed to just talking about it. So let's go ahead and clear that. You'll notice that Australia, Canada and I guess Central came back as well. So that's the one that I missed there in the original one that we spoke of. So where are we going to go next here is we're going go to the sales territory again and we're gonna click on this here. And what we're gonna do is go to our label filters and we are going to say begins with, so let's go over here and choose begins with and if we want we can say let's say the letter N. So I think that's what we wanna choose here so choose N so we can see that we have the Northeast sales territory and the Northwest sales territory up here. Okay so let's go ahead and clear that one. So we clear the filter out. And the last one I wanna show you is we're going to the label filters once again and this time what we're gonna do is we're gonna show you some of the search options here. So we're gonna go to equals. And what we're always talk about here is the use of the question mark to represent any single character or the use of the asterisk to represent any series of characters. So what we wanna do here in this particular example is we wanna find any of the countries that have the letter E as the second character. So what we're gonna do here is to do that we're going to put the question mark to represent a single character. Then we're gonna put the letter E and then if we want to have any character with anything after E we're just gonna put the star there. What that's gonna tell us to do is any character can show up in the first position E has to be in the second and then anything can show up after that. So let's just go ahead and choose that. And we end up with Central and Germany cause these are the only two sales territories with the letter E in the second position. So what I'm gonna do here again is go back from my label filters. Let's go over here to equals and let's go ahead and change this. So now let's go and assume that we want to go and filter our data so that we have the ability to have E anywhere in the actual character. So are there strings? So what I can do there is I can go star E star like this and now we will see all the sales territories that have the letter E anywhere there. So lemme just pop that back up here under the label filters, we just went to the equals and put star E star and this would be equivalent to going over to this over here. So I'm just gonna quickly clear this. And if I go back down to the label filters and says contains, I can go over here and put the letter E in here. And if it contains the E we're gonna have to put the same results because that contains is actually letting me go and choose anything that contains the letter E. Okay so next what we're gonna do here is we're gonna take a look at the value filter. So I'm just going to go and clear my filter from this here. And what we're gonna do, the value of filters are they're gonna be triggered by value-based criteria. And it's important with these filters here to understand which level you're applying the filter at. So what I'm gonna do here first is I'm going to go to my sales territories. And what I'm gonna do is apply the filter at the sales territory level first. And to do that I am going to go to the drop-down box right here and instead of choosing label, I'm gonna go down to value. And what I'm gonna do here is I want to find all of the grand totals for sales territories that are between eight and 10,000. So before I go ahead and do that that is looking for any of the grand totals here at this level that are between eight and $10,000. So don't look at the values in here cause that's at the product category level. We are gonna be concerned with the filter being applied here. So let's go ahead and do that. So go back over to this, go to the values and say between and we're gonna say the total order quantity. Cause that's the item we want to take a look at. Cause it's the only thing we over in the values at the moment. And I'm gonna say between 8,000. So let's go over here. Oops, go ahead and type in 8,000 and 10,000 like so and now we'll see that these are the two sales territories that fell between that particular range. So what we'll do now is we're gonna go in here and we're gonna clear that filter. Now we're going to apply the filters at the product category level. So let's go ahead and clear that out like so. And now what we're gonna do is go into the product categories here and keeping in mind this time we have filters that can be applied here. So what I'm gonna do is I'm gonna choose the product category. Go down to values filter say between and in this case I wanna see all the total order quantities that are between 1000 and 2000. So just go 1000 and 2000 like so. And you can visually take a look at some of the values here. We've got a couple that we can see on screen that are gonna show up. So this 1598, nothing in this, nothing in Canada will show up. We've got a few values in here. So we've got three within the Central region. So let's just go ahead and click okay. So we've got the value that showed up in Australia the three, which would just point to our Central region. And we can go through the rest of the ones down below here. So just be aware of which level you're providing the value filters at. So once again, we will go and clear this filter out like so. And the last filter that we want to show you is a top function to go in either find the top of a range or the bottom range. So there's a couple of different configurations we can put on that. So let's go over that right now. Okay so let's take a look at the top filters. So what I'm gonna do here is I'm gonna go to sales territories click the drop-down box here, go to the value filter and down at the bottom here it's called the top 10. So that's just the name of your top 10. You have the ability to go in and configure this in a moment to either the top or bottom and it doesn't even have to be the number of 10. So let's just a way to do the top or bottom of a set of data. Okay so let's go ahead and keep this first example nice and simple. So what we're gonna do here is we're gonna take a look at the top three. So I'm gonna change that from 10, which is default. The top three items by total order quantity. So what that's gonna do is here, go in and take a look at the grand totals, which we have right here and go and find the top three grand totals. So I'll go ahead and click okay. And we're gonna see that we get these particular values right here. They're not in sorted order here by grand total. And we'll show you how to do sorting in a few moments here in this particular sub lesson. But for now, this goes and picks the top three items. So next what we'll do is let's go ahead and go back to the value filters. And I'm gonna go back down to top 10. And this time what we're gonna do here is we're going to change some items to percent. So first, what I need to do here is, actually let's cancel this. So I wanna go and show you what this is doing here. Cause these next two are a little tricky to get your head wrapped around. But after you see a couple of examples they're pretty straightforward. So it's gonna clear the filter there. We go down to the grand bottom here. And the grand total for this particular dataset here is 97,942. So if I asked for the top 10% of the data what that's gonna do is look for the groupings that are at least $9,736 ish for that particular grouping. So let's go ahead and do that. So I'm gonna go up to the sales territory, go to my values filter, go to top 10. I'm gonna go and ask for the top 10% like so go ahead and click okay. And we're gonna see that this is the top item that covers at least 10% of the actual sales. Okay so that covers 10% of the data, but what if we wanted to actually take a look at 20% of the data? So what we're gonna do in that particular instance is I'm gonna go back over to my filter here, click the drop-down box, go to the value filter top 10. And this time I'm gonna say I want the top 20%. Now, 20%, just for some quick math that I had already done in a sheet of paper is $19,472. Now we can already see that this one right here, which was the top is only $19,180. So this isn't gonna cover the top 20%. So if I go like this and click okay we're gonna get the next item or next sales territory in the grouping here. So now these two cover at least 20% of the dataset that we're are looking for on our top. Okay so we can also go ahead and do the bottom if we want to. I won't go through Google's examples in this case but let's go ahead and carry on with the next filter. And that is going to be taking a look at the sum. So I'm just gonna go ahead and go back down to here. Let's go ahead down to the value filters and go down to top 10 here. And this time what we're gonna do is we're gonna go in and choose the sum like so and this time what I'm wanna do here is I'm gonna go in and try and find the top sum values that are at least covering 20, actually I say $19,000 here like this. And when I do that we're only gonna get this value right here. So Southwest should be going on when this shows up like so, but now if I go back in here again and go back to the value filters and say top 10, now this changes to $20,000. Now this one only covers up to $19,000 and it's once again, you're gonna bring, oops, I guess I should type in 20,000. Go ahead and click here. And we're gonna get that next value in here again. So you can do it on percentages of the grand total, not this grand tool right here but the grand total as a whole. So if I just clear this, I can do the percentages with look at the grand total down here and I can take a look at sums that I wanna type in any hard-coded version as well. So a couple of different ways to go ahead and take advantage of that top function. Okay so that is it around filtering our dataset. Now let's go ahead and take a look at sorting our datasets. So let's go ahead and go to our table over here. So what I'm gonna do here is I'm gonna click on the sales territory right here. And right now my list is already sorted in alphabetical order. So I've got Australia, Canada, Central, France, Germany. And if you go to the rest of the list you're gonna see it sorted that way. I can go here and sort from bottom to top. So now at the top of my list is United Kingdom, Southwest, Southeast, Northwest. Now that type of sorting is nice sometimes. So I'm just gonna go ahead and switch it back here, but what if we wanna go and sort on the dollar values that over here? So let's go ahead and take a look at doing that. So we click on this and I can go over to more sort options. And now I have a little bit more control over how the sorting is actually happening. We can see right now it is currently sorting ascending on sales territory. So let's go in here and instead of sorting by the sales territory from A to Z here, let's go and click on the order quantity. So let's go ahead and sort on that in ascending order like this and now we're gonna see is we've got the lowest grand total at the top, then the second lowest, third lowest all the way down to the bottom. So a couple of different ways and very basic manner here to go ahead and sort your datasets. And I'll also show you how to go ahead and do multiple sorts. So we've got the sort rate now on the sales territory going from lowest to highest but now if we want to do some sorting within the product category so I can go ahead and click on the product category here. I'm gonna go to more sort options once again. I'm gonna say ascending A to Z and within that, within the actual product category itself I'm gonna go and sort on the total order quantity. And just notice here the right now the default is this is going in alphabetic sort particularly in each one of these things here when I click okay now each one of these groups here will be sorted from lowest to highest within the actual product category. All right, that brings an end to this particular sub lesson on filtering our pivot tables and sorting the data in the pivot tables.