Filter By Value allows you to apply a filter to a column from the Values section of a Pivot Table in Excel…unless you’re on a Mac where it’s broken!
Not a Mac user? The same trick can be used to solve another Filter By Value problem that exists in Excel on both the Windows and Mac platforms.
What does this mean in the real world? Consider these two scenarios:
Scenario 1: You have 2 columns of numbers in a Pivot Table…say, Revenue and Quantity Sold. You need to apply a filter to the rightmost column (Quantity Sold) such as “show me the Regions where Quantity Sold was greater than 100”. This doesn’t work in Excel on the Mac.
Scenario 2: This affects Excel on Windows and Mac. You want to apply multiple “Filter By Value” filters on multiple columns at the same time, such as “show me the Regions where Revenue is greater than 10000 AND Quantity is greater than 100”. But you can’t. Oh yes you can – and this trick show you how!