3

Is it possible to have multiple label filters in a pivot table? I see that I can use 'contains' which could work but I am having issue with the syntax if this is possible.

I'm thinking something like contains "apples", "banannas" should work Any insight would be appreciated.

1
  • If you can't do what you need using vanila pivots, you might want to look at Microsoft PowerQuery which allows you to do simple or complex filtering and transformations on data. Apr 1, 2015 at 23:22

2 Answers 2

2

To answer your question - no you cannot have more than one label filter or more than one value filter on a single pivot.

Raystafarian did not understand you question..

  1. yes - clicking the "allow multiple filters" check-box will allow ONE label and ONE value filter BUT not two of the same type of filter.
  2. yes - of course you can go ahead and select "multiple items" but that is not the label or value filtering you are looking for

Workaround 1

Filter on the table for the first label filter, then copy and paste the filtered table into a new table and then pivot on the new data set(table).. not a best option but will certainly get the job done

Workaround 2

IF you can work through an if or statement this will work great for your purpose and you can have as many label filters as your heart desires.

Basically you would create an additional column on your data set that will return whether or not the label contains the value/string you are looking for, then you can pivot using the new column.

First the basic formula with one criteria:

=IF(ISNUMBER(SEARCH("Apple",A1)),"Contains","Does not Contain")

a. The SEARCH() function searches a cell for the "apple" text and returns the character number where that text begins within the cell

b. The ISNUMBER() function will return a true or false value if the search is successful

  • eg1. if the search function is not successful an error will return(not a number) so the ISNUMBER() function will be false.

  • eg2. if search function is successful a number will be returned, so the ISNUMBER() function will be true

c. The IF() statement will return the personalized message "Contains" if true and "Does not Contain" if false

Now the formula with multiple criteria

=IF(OR(ISNUMBER(SEARCH("Apple",A2)),ISNUMBER(SEARCH("banana",A2))),"Contains","Does not Contain")

just throw an or statement before your first logic, which is ISNUMBER(SEARCH()) and so on for as many more criteria as you want

1

I'm confused by your question. If you are talking about label filters, which I think you are, yes this is in

pivot table tools - PivotTable Name section - Options dropdown - options - totals and filters tab

enter image description here


But, if you're talking about report filters, yes, you can just select multiple -

enter image description here

For instance, here I excluded only "cow"

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .