Using an OR condition to filter an Excel Pivot Table

Question

I'm an Excel amateur and I'm just starting to learn to use Pivot Tables. The issue is, I need the pivot table filtered results to be inclusive.

So, I have a table with some data rows and 3 Columns A, B, C with possible values X, Y, Z... I specify a filter for columns A, B, C such as that I can retrieve the data rows that have A=Y OR B=Z OR C=X.

The problem I have is that the default filters use an AND condition. So if I select the first value as A=Y, it will remove all other rows.

Any ideas? I know that you can do something similar with an Advanced Filter, but I don't know if there's a simpler solution.

Many thanks!

Answer 1

Ideally all data manipulation for a pivot table is done in a calculated field. However I've found that you can not use a calculated field for sections other than the value section (which you are trying to do here it seems).

A workaround I've used is to add a new column to the source data that contains a formula to group the data by.

You could do something like:

IF(OR(A=Y, B=Z, C=X), "Display", "Hide")

And filter on that column's Display or Hide results in the pivot.