Menu
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!
880
Whether you are using the Report Filter or the new Excel 2010 Slicer feature in a pivot table, there is really no good way to change the filter with a formula. Instead, you could add a formula to the original data set and then use that new field in the filter.
YES it's very much possible that you can use a Cell Reference to Filter records while using the Pivot Table.
Select the data on which you want to apply conditional formatting. Go to Home –> Conditional Formatting –> Top/Bottom Rules –> Above Average. Specify the format (I am using “Green Fill with Dard Green Text”). Click Ok.
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.
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
