Locked lesson.
About this lesson
When you want to filter your PivotTable to show the top/bottom x items or top/bottom x% of items in the data set.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Top and Bottom x Items.xlsx82 KB Top and Bottom x Items - Completed.xlsx
75.4 KB
Quick reference
Top and Bottom x Items
Display the top/bottom x items/percentage of items in a PivotTable.
Where/when to use the technique
When you want to filter your PivotTable to show the top/bottom x items or top/bottom x% of items in the data set.
Instructions
Accessing the top/bottom x filter area
- Identify the last item in the rows area of the PivotTable
- In the field list (not the Rows area) of the Field well, select the item and click the drop-down arrow
- Choose Values Filters > Top 10
Top x filters
Top x items
- Set the Top 10 Filter to:
- Top
- Set the value of 10 to the number of Items (plus ties) you’d like to show
- Leave Items as is
- Choose the field you wish to filter your top x from
Top x %
- Set the Top 10 Filter to:
- Top
- Set the value of 10 to the number of Items (plus ties) you’d like to show
- Change Items to Percent
- Choose the field you wish to filter your top x from
Bottom x filters
- Set the Top 10 Filter the same as the Top x filter (listed above)
- Change “Top” to “Bottom”
- 00:04 What I want to show you now is not actually something that's really
- 00:08 a calculation technique as much as it's actually a filtering technique.
- 00:12 And the whole point behind this is trying to get the top x or
- 00:16 bottom x items from a particular list.
- 00:19 In food and beverage, this is particularly important because we always want to know
- 00:22 what our top selling menu items are.
- 00:24 When we re-jig our menus, we want to keep those around.
- 00:27 And we also want to look at what our bottom selling menu items are,
- 00:30 because when we rejig the menu, we want to get rid of those and make room for
- 00:33 something else that could hopefully become a top selling item.
- 00:36 Now when you look at this particular pivot table,
- 00:38 we've got it set up with the category or
- 00:40 item names, we've got account of units and account of sales dollars.
- 00:43 So what I'd like to do is I'd like to figure out a way to say,
- 00:47 show me only the top five sellers by category.
- 00:50 And you're going to be amazed, this is actually super, super easy.
- 00:53 What we're going to do is we're going to to pick on the item name here.
- 00:56 I'm just going to click on the filter and I'm going to go to Value Filters.
- 01:00 And down in the bottom you'll notice that we have a Top 10.
- 01:02 Well that's cool, but I only want five.
- 01:05 But you'll see that when I actually go and select the Top 10 filter, I can say,
- 01:10 hey, I really only want this to be the Top 5.
- 01:13 I can choose by items, I can choose it by percent or sum, I can go by units or
- 01:18 sales dollars, whichever my value field areas are listed here.
- 01:22 Now I'm going to go with units, that's going to be good for this one, and
- 01:26 we'll say OK.
- 01:26 And at that point you can see the table filters down a little bit.
- 01:29 And if I go and run account over these guys here, you can see,
- 01:32 down on the bottom, that I have six items.
- 01:35 You go, wait, but you asked for five.
- 01:36 And this is true.
- 01:38 But what happens is it actually gives me the top five and ties.
- 01:44 And this is important, because Corona and Coors Light, in this case,
- 01:49 are actually tied.
- 01:50 In the case of canned beer,
- 01:51 I only actually have four items, because there's only four items in the dataset.
- 01:54 It's not going to invent any new ones, of course.
- 01:56 Coolers and ciders, there's only three.
- 01:58 But when we get to draft beer, it's now been cut down to only five units,
- 02:02 because there were no ties.
- 02:03 So 36, the pint of Sleeman Honey.
- 02:05 The next value must be 35 or lower.
- 02:08 And the same with liquor, we're down to five items, and wine as well.
- 02:12 Now that's cool, that gives me the top items.
- 02:15 But what about the bottom items?
- 02:17 Again, this is actually really, really easy once you know the trick,
- 02:21 but it's not exactly super intuitive.
- 02:23 So let's filter this pivot table.
- 02:24 And what we'll do is we'll go to Value Filters.
- 02:27 And again, we're going to choose Top 10 to get to our bottom five.
- 02:33 Once you're in here, one of the other items on the filter is Bottom.
- 02:38 So we can choose it to be the bottom five items, again, by units.
- 02:43 And we'll say, OK, and just like that it's filtered again.
- 02:47 Now one of the things you'll notice that's actually somewhat interesting here is that
- 02:52 Kokanee is one of our bottom selling items.
- 02:54 It's also one of our top selling items.
- 02:56 And the reason for
- 02:57 this is because there's only actually seven values in this category.
- 03:00 So therefore when it actually filters them down, there's going to be some overlap.
- 03:04 So it does make sense.
- 03:05 But if you look through anything else,
- 03:07 we can see we only have three items in the Cooler/Cider category.
- 03:09 So they show up in both places.
- 03:11 But when we actually start looking at things like liquor, for example, these
- 03:16 are the bottom five with ties, because they're all listed with a value of one.
- 03:20 Not a single one of these is on the list that you see in our liquor items here,
- 03:25 because they all have 11 units.
- 03:27 So this kind of makes some sense.
- 03:29 Now what about working with percentages?
- 03:31 Well we can do that as well.
- 03:33 It's all done through the exact same place.
- 03:36 So we'll go through Value Filters, we'll go to Top 10 and
- 03:41 we're going to say we'd like to see our Top 5% based on our items or units.
- 03:48 So at this point, when we filter it, there's not very many items in the top 5%.
- 03:53 So these are going to be our very best sellers.
- 03:56 What about the bottom five?
- 03:57 You're going to see a lot more units in this case,
- 04:00 because there's a lot more that have a quantity of one.
- 04:02 So in this case, we'll go to Bottom and we'll choose again, the Bottom 5.
- 04:08 And instead of items, we'll choose percent, by units, and away we go.
- 04:13 And now as you can see, particularly in liquor, 2 and
- 04:17 1 units, fall into the 5% range.
- 04:20 And there we go,
- 04:21 we've now got a couple of lists that are actually filtered in different ways,
- 04:25 showing us the top 5 items, top 5% items, bottom 5 items, bottom 5% items.
- 04:29 So you can see how you can reconfigure this to make this work for
- 04:32 whatever view you need.
Lesson notes are only available for subscribers.