Filtering the Value field in a Pivot Table

[katiapro93]

I give up! For the past few hours, I have been trying to figure out how do I filter the value field in a pivot table. I have a pivot table which gives me all the clients who purchased different items. I have placed the product in the column field and filtered it by the product that I want. Now I need to just show the ones who meet a certain number.
For Example: (Any client who buys 5 or more qualifies)

Client Product Qty
12 7oz 5
14 12oz 5
11 7oz 1

Report should show only the ones with Qty 5

Can anyone help me filter the Value field?

 

[Jerry Sullivan]

I'm guessing that you are trying to get a filter dropdown above the value column that you want to filter (like an autofilter in a simple table).

For a PivotTable, you control the value filters from the Row Label column(s) on the left.

Right click on an item your Client column, then pick Filter > Value Filters...
A Value Filter dialog box will pop up. You can use the dropdown box on the left to select the PivotTable field that you want to filter (Qty).

If I misunderstood the problem you were having, please clarify.

 

[MisterBate5]

Sounds like you're trying to get the pivot to only show rows where the value is above a certain amount. I don't think it's possible to filter that way using a pivot table.

An alternative might be to sort the values descending from highest to lowest. You need to apply the sort to one/more of your row fields (ther's usually a "more sort options ..." and in there you can choose descending by count of x or sum of y.

And then rather than filter, you could use conditional formatting to highlight values above a certain number. When doing that, be sure (in XL 2007/2010) to apply the conditional formatting to "all fields showing count of x" or "sum of y", then the conditional formatting will be applied to new pivot rows as you add more data

 

[Jerry Sullivan]

Sounds like you're trying to get the pivot to only show rows where the value is above a certain amount. I don't think it's possible to filter that way using a pivot table.

Xl2007 allows you to enter criteria for the value filters. There are 3 parts to the filter dialog. The first is the Field Name, the second the operator (is Greater than or equal to) and third is the value (5).

 

[MisterBate5]

Wow. It's true you never stop learning.

@JS411 - I just now found that feature. Thanks!

 

[Jerry Sullivan]

My pleasure!

I experienced the same frustration as the original poster in trying to get the filter dropdowns to appear in the columns above my PivotTable Values and was lucky enough to stumble accross the solution.

 

[katiapro93]

You guys are just great! How you think of these things. Now I am not sure what I need to do. I need to put my value in the filtering box? Then how do I feed it the criteria. MisterBate5 was correct in what I want the pivot to do. I just want it to show me the subtotals that are equal to or greater than a number I select. I am kind of new to pivots, so can you explain exactly how to get the filter accomplished? Thanks!

 

[Jerry Sullivan]

Most the steps on how to do this are in the first reply...

Right click on an item your Client column, then pick Filter > Value Filters...
A Value Filter dialog box will pop up. You can use the dropdown box on the left to select the PivotTable field that you want to filter (Qty).

The last step is to fill in your criteria and value:

 

[katiapro93]

AWESOME!!!! It worked. Thank you so much. You don't know how much time you have save me and all my clients. Again, Thank you!!!!!

 

[Jerry Sullivan]

Happy to help. Good luck!