How to filter a pivot table by value

In addition to filtering a pivot table using Row or Column Labels, you can also filter on values that appear inside the table.

Let’s take a look.

Here we have an empty pivot table using the same source data we’ve looked at in previous videos. Let’s add Product as a Row Label, and Total Sales as a Value. Now let’s sort the pivot table by Total Sales in descending order.

To illustrate how Value Filters work, let’s filter to show only shows products where Total Sales are greater than $10,000.  This will eliminate all of the products below “White Chocolate”.

Value Filters are located in the same drop-down menu as Label Filters. Instead of filtering the label itself, Value Filters work on the values associated with a Label Field. As you can see, the options available under Value Filters are all related to filtering numeric data. In this case, we need to choose “greater than” and then enter 10,000 in the dialog box.

Now the pivot table only shows products with total sales over 10,000. If we hover over the Filter symbol, we see a summary of the filter and sort options currently applied.

Let’s clear that filter and add a Value Field to count orders. Because our source data is organized by order, we can determine orders by counting any non-blank field. Let’s use Customer, and then rename the Value Fields to Sales and Orders to make things clear.

Now let’s add a new filter to show only products with more than 250 orders. As before, we navigate to Value Filters, and then select Greater Than. This time, however we need to switch the Value Filter from Sales to Orders. Then we enter 250 and press OK.

As always, if you hover over the Filter icon, Excel will display the currently applied filter.