🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Top and Bottom x Items

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
74.5 KB
Top and Bottom x Items - Completed.xlsx
67.7 KB

Quick reference

Topic

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 Pivot
  • 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”
Login to download
  • 00:04 In this video we're gonna look at another really cool feature with pivot tables,
  • 00:10 and that's how to filter them to only show the top 10% or 5% or 10 items.
  • 00:16 This is really important in retail because you've got a whole bunch of sales items
  • 00:20 and you want to know, not only what your top sellers are,
  • 00:22 because those are the ones that you really want to promote a little bit, but
  • 00:26 you also want to know what your bottom items are.
  • 00:28 That way you know which things you should actually be taking off your menus or
  • 00:31 removing from your stores to try and make way for
  • 00:34 new products that you hope will bubble up to be your top sellers.
  • 00:37 So when we look at the particular pivot tables here,
  • 00:41 I've got two pivot tables set up.
  • 00:42 And you can see that we've got categories down the left and
  • 00:44 we've got our sales items.
  • 00:46 We've also got a sum of units and
  • 00:48 a sum of sales dollars that we actually have going in here.
  • 00:51 Now what I want to do is I want to filter this to show my top
  • 00:55 five sales items by category.
  • 00:58 That's where I'm gonna start, and you're going to find this is just amazingly easy.
  • 01:02 What we do is when we've got things on the pivot table, we've got a couple ways we
  • 01:06 can do this, but the easiest way I find is to click on the little filter arrow,
  • 01:10 which is by the last item in the row category here.
  • 01:15 So, we're gonna just left click on that rather, and go to Value Filters, and
  • 01:20 down at the bottom you can see Top 10.
  • 01:21 And when we go into Top 10 it says would you like to filter by the top.
  • 01:26 Sure, how many would you like?
  • 01:28 I'm gonna take five items, yeah that works, and by units.
  • 01:32 Now, I can choose by units or sales dollars.
  • 01:35 I'm going to go with units to start with here, and we'll say OK.
  • 01:39 You'll notice that there's been a little bit of movement that's been going on in
  • 01:42 this particular table.
  • 01:43 I should now see that each of my categories has a maximum of five
  • 01:48 different sales items, although you will occasionally see one or
  • 01:52 two more because it gives you the top five with ties.
  • 01:57 So if we have a whole bunch of items, for example, in this area,
  • 02:00 if I had another item that had 16 units,
  • 02:02 I would actually end up having six items in my bottled beer.
  • 02:06 Okay? Because it would actually rank the top
  • 02:08 five being one, two, three, four, five, and if there were two units of 16,
  • 02:13 it would put both of them on because it can't determine which belong there, so
  • 02:16 it's top x plus ties.
  • 02:19 Now you'll notice that trying to come up with the other side of things,
  • 02:23 where we want our bottom units, is actually amazingly easy as well.
  • 02:28 Again, we go and we click on little filter icon, we go to Value Filters, and again,
  • 02:34 and this is not intuitive, we go to top ten, but we change from top to bottom.
  • 02:41 We change our number from ten to five,
  • 02:47 and we're gonna leave this with items and units, and away we go.
  • 02:51 And you can now see that our top selling units in bottled beer,
  • 02:55 Budweiser and Heineken.
  • 02:57 Kokanee actually also happens to be one of our bottom selling units, so
  • 03:00 it looks like we don't have a ton of different sales items in here.
  • 03:02 However, when we go down and we look at things like liquor, you can see our top
  • 03:07 sellers, we've got items from Ceasars all the way down to Drambuie 11 units.
  • 03:12 When we look at our liquor here, I've got a lot more than
  • 03:15 five items that's only sold one unit each, but this is what I say.
  • 03:19 We've got our bottom five in ties, so, it's gonna give us at least five different
  • 03:23 items in this, but if there's a similar value,
  • 03:26 it's gonna repeat it across the board for all of those guys.
  • 03:29 So, we now know which items we should possibly be moving on, and our wines,
  • 03:33 doesn't look like these ones have done so well.
  • 03:35 Whereas these guys over here are doing really well, so
  • 03:38 we want to make sure that we keep promoting them.
  • 03:40 Now that's great but what if we want to do the top x percent,
  • 03:45 the top 5 percent for example.
  • 03:47 Well guess what?
  • 03:48 Same place, these pivot tables are exactly the same built off the same information.
  • 03:53 Once again we'll go and we'll click and we'll say Value Filters Top ten,
  • 03:58 and we'll go with Top.
  • 04:01 We're gonna change to five, but this time, instead of items,
  • 04:05 we'll say percent by units, and OK, and it knocks us down to the top five percent.
  • 04:12 And obviously, it's a very short list, and that's fine, we're okay with that.
  • 04:16 Let's take a look at what the bottom 5% gives us.
  • 04:18 Value filters, top 10, we'll go with bottom,
  • 04:23 5 and percent by units.
  • 04:31 And you can see we get a lot longer list because there are a huge amount of items
  • 04:35 that only sold one or two units.
  • 04:37 So in the bottom five when we actually scan the entire list of all of the units
  • 04:42 that we've actually summed up, it looks like anything with ten units or
  • 04:46 below is going to end up showing in this list.That's
  • 04:48 how these particular guys work.
  • 04:51 There's one other place that we could actually do this, and
  • 04:54 that is by filtering from the little icon over here.
  • 04:56 When we actually come over to our field and you see this little filter icon,
  • 05:00 you can again get to your value filters in the same way.
  • 05:03 Personally I find it easier to do it by the pivot table on the side here because
  • 05:06 it's a little bit closer to the work surface that I'm actually working with.

Lesson notes are only available for subscribers.

Percentage Difference From Calculations
04m:10s
Calculated Fields
06m:31s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement