7

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!

cesil99
  • 103
  • 1
  • 1
  • 6
  • Suggest you elaborate on what (analysis) you are trying to accomplish and not focus on how (pivot table). There might be a better tool for the job. – andy holaday Jul 11 '12 at 00:13

1 Answers1

12

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.

danielpiestrak
  • 5,279
  • 3
  • 30
  • 29