Pivot Table Filter - Or Function

ollieotis

New Member
Joined
Jun 6, 2006
Messages
44
Hello,

Been poking around on this board and the web for the past hour trying to find out if this is even possible... started to give up, but thought I'd ask the experts first.

Is there a way to filter data in a pivot table using an or function instead of an and function, e.g. I have a standard pivot table with two fields in the report filter section. I'd like to set up the table so that it pulls all data where Field A = X or Field B = X, not Field A and B are = X.

Any help you can provide would be much appreciated. The data set will subsequently be updated by novices (more so than me), so I'm hoping there's a simple solution with a pivot table where I can just instruct them to update the data and hit refresh.

Many thanks in advance!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi ollieotis,

I think you would need to create a separate field for that purpose. Field C could have a formula in the source data like =OR(A2="X",B2="X"), then the users would filter by FieldC=TRUE. Fields A and B shouldn't be made report fields.

The problem is that PivotFilters are subtractive instead of additive. So even though you are thinking: Show all records where FieldA equals "X"; Excel thinks of that as: Hide all records where FieldA is not equal to = "X".

If the selection needs to be more dynamic, that can be done by having the Field C formula reference input cells on the same worksheet as the PivotTable.
 
Upvote 0
Hi Jerry,

Thank you for the response. That's kind of what I figured, and already addressed the issue using a solution similar to the one you mentioned. Thought I'd check though to see if I wasn't missing something here.

Thanks again!
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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