I'm looking for a way to filter out or hide all rows that contain a blank value for the aggregated COLUMN values. For example, I need a way to mass hide (through sorting the column values across the entire pivot table) or filter out the orange highlighted rows.
1 Answer
An easy way to solve this would be to add a formula column in your raw data.
I assumed that you had something like this in your raw data:
A B C D E
1 Col1 Col2 Col3 Region Price
2 A A E A 0.01
3 A A E B 0.04
4 A A D A 0.02
5 ...In your example you have Region A and B. Add a column named "Check" in your raw data (in column F) and then below that (F2) a formula like this:
=IF(D2="A",COUNTIFS(B:B,B2,C:C,C2,D:D,"B"),COUNTIFS(B:B,B2,C:C,C2,D:D,"A"))
If Region shows "A" it will count the combination of column B and C for Region "B" and vice versa. If Region A or B does not have this combination it will show 0.
Example with formula:
A B C D E F
1 Col1 Col2 Col3 Region Price Check
2 A A E A 0.01 1
3 A A E B 0.04 1
4 A A D A 0.02 0
5 ...In your pivot table you can now simply add the "Check" column to your filters area in the pivot and filter out all values that have 0 in it.
1
