Tuesday, 4 February 2014

Excel - Custom Filter in Pivot Tables

Introduction
There are times when you may wish to filter data in a pivot table. It's easy enough, click the corner filter button and select your filter. You can also select multiple items to filter. But if you want to select multiple items based on some criteria, how do you do that? In a normal (non-pivot) filter it is possible using a Custom Filter to use AND/OR. But in a pivot table there's no Custom Filter option.


Solution 
  1. With your pivot table on the screen, click the data you want to filter on
  2. Click the filter button (top right hand corner)
  3. In the Search box enter the first text you want to search for
  4. Click OK - the filter will work, it will show you only the results based on this first text you are looking for
  5. Click again on the filter button (top right hand corner)
  6. In the Search box enter the second text you want to search for
  7. Click the option Add current selection to filter
  8. Click OK

The result is that your pivot table data is filtered on two criteria. You can continue to filter and click the Add current selection to filter if you need to (it is the equivalent of <something> OR <something> OR <something>, etc).

That's it!


Excel Version?
It works the same in Excel 2013 and 2010.

Post a Comment