Let’s take a look. Here is the same pivot table we’ve looked at previously, showing Sales and Orders by product. Let’s add a Value Filter on the Product field that limits products to the top 5 products by Sales. Top and bottom Value Filters are a special kind of Value Filter, so you’ll find the option under Value Filters in the drop-down menu for the field you want to filter. The option we want is called Top 10. Now simply adjust the controls to display the top 5 items for the Sales field. The result is a pivot table with 5 rows only - the 5 top products by Sales. In a similar way, we can adjust the filter to show the 5 bottom products by Sales. Simply navigate back to the Top 10 filter option, and switch Top to Bottom. Let’s clear the filter and add a new filter to show only the top 3 products by Orders. Notice that we need to switch the field we’re filtering on from Sales to Orders. The results of a Value Filter depend on the location of the field the filter has been applied to, in relation to other fields in the pivot table. For example, if we add Region to our Row Labels, we see the top 3 products each with a sales breakdown by region. If, however, we move Region above Product, the behavior of the filter changes. Now, the pivot table shows the top 3 products in each region by Order Count.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
