In the old days, before I knew better, when I needed to filter a pivot table Value field I’d do it by using by throwing an autofilter from the Data menu on it:

It made me feel dirty but I didn’t know any other way to filter pivot table value fields. Clearly pivot tables provided no filter in the Values columns:

And then one day I read this SuperUser answer by mtone and never looked back. If you don’t know how to apply a Value Filter to a non-Value pivot field I recommend reading it right now:

Good wasn’t it? I started to write a post about this and realized this was the best explanation I’ve seen. I especially like that mtone explains how choosing different fields to apply the filter to will result in different levels of aggregation, and will change your results.

One Value Field Filtering Oddity

I was prompted to write this post after answering Bijan’s question on Stack Overflow. I quickly helped solve his problem but was perplexed because his original issue was that he was applying a value filter to the value field itself. As I said at the beginning, this isn’t possible. Turns out that’s not quite true. After an extended chat he showed me how he did it.

Before you can do this, you first need to use the field as a Row, Column or Report field. Simply dragging it to the Row area of the Show Filter dialog and then to the Values area will do the trick. Once you do so, you can click the down arrow next to the field name in the pivot’s Show Fields dialog:

At that point you can pick the various Value filters.

As Bijan discovered though this doesn’t actually do anything. No filtering occurs (and if it did, it wouldn’t be an aggregate filter anyways). And when you drag the field back to a non-value position it, the filter that is, goes away. Weird and bug-like.