Filtering the Value field in a Pivot Table

K

katiapro93

Board Regular
Joined
Jun 25, 2009
Messages
140
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
MisterBate5 said:
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.
Click to expand...

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).
 
Last edited:
Upvote 0
My pleasure! :beerchug:

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.
 
Upvote 0
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!
 
Upvote 0
Most the steps on how to do this are in the first reply...

JS411 said:
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).
Click to expand...

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

14706743556_7d45a5918d_z.jpg
 
Last edited by a moderator:
Upvote 0
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!!!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
357
Anthony47
A
F
  • Question Question
Showing details of Pivot table from Grand total, including Row filtering
Replies
0
Views
65
Francois2France
F
S
  • Question Question
Filter in Pivot Table Field List Clearing when The Pivot Table Is Refreshed
Replies
1
Views
575
arun_eagertolearn
A
R
  • Solved
XLOOKUP/VLOOKUP based on Pivot Table Response
Replies
6
Views
315
rachel06
R
T
  • Question Question
Clear FIlters of Pivot Table after Cell Value Changes
Replies
4
Views
408
Alex Blakenburg
A

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top