Search code examples
excelpivot-tableexcel-2011

Filtering a PivotTable by Boolean

The source data for my Excel PivotTable looks like the following (this is a simplification):

id name score
1 john  15
2 james 2
3 pat   14
4 jake  12
...

I have a PivotTable that uses this as a data source. Now, what I want to do is have the PivotTable only consider entries if their id is less than 100. This is theoretically achievable by having a Report Filter on id, and de-selecting any number greater than 100. But that's rather absurd.

How can I filter out data using a Boolean constraint? I've tried various methods, none of which worked. It seems like calculated fields are the key, but it doesn't seem possible to create a filter on calculated fields.

I'm using Excel 2011 for Mac, if that makes a difference. I'm a programmer, but I've never programmed in Excel, so if that's the solution, I'd request baby steps. :) Thank you!

Solution

  • AFAIK, In Excel 2011, you cannot use a report filter to apply any kind of filter. You have to manually check/uncheck the values that you want or don't want.

    The alternative that I can think of is to insert a column before your data and enter the formula

    =If(B2<100,TRUE,FALSE)

    and copy it down using Autofill. (See screenshot below)

    enter image description here

    Now create a pivot and put the field "Less Than 100" in the report filter and simply select TRUE (See screenshot below)

    enter image description here

    If you don't want to go down that path then move the ID field to ROW LABEL from REPORT FILTER where you can use a filter.