Exceljet

Quick, clean, and to the point

How to filter a pivot table to show top values

Filtering a pivot table for top or bottom values, is a special kind of value filtering.

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 the top 3 products by orders. Notice that we need to switch the field 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 sales breakdowns by region. If we move Region above Product, the behavior of the filter changes. It now shows  the top 3 products in each region, by order count.

One more thing to be aware of with value filters is that you can’t filter a field on more than one value field. If we add a value filter for top 2 products by sales, the order count filter is removed in the process.

Course 
Core Pivot
Author 
Dave Bruns