3 ways to filter pivot tables

There are time when you learn something incredibly simple and can’t resist sharing it.

This is one of that post where filtering the pivot table is extremely easy, not the fact that it was hard (concept) earlier.. but still this trick is fun!

 

Consider this Pivot

3 ways to filter pivot tables - 1

2 insanely simple things here – Sum and Count of Sales

  1. Now let’s say I want to filter only the last 3 Customers (Data Tronics, White Associates and MNTL). How should I filter the pivot table ?
  2. If you thinking of pulling the Customer Dropdown and checking those customers.. sure that is cool
  3. But there is another cooler way of doing it

 

Here is what you do..!

3 ways to filter pivot tables - 2

  1. Select the 3 customers
  2. Right click and go to Filter
  3. Keep Only Selected Items

Cool thing = The items that you select need not be in a contiguous range. Cool.. eh ?

 

2 More fun tricks …

  1. In the same filter option menu, you can also do the reverse – Hide selected items
  2. Or filter by specifying the value manually or even do a wild card filter (search)

 

3 ways to filter pivot tables - 3

  • Right click on any one of the customer (in the pivot)
  • Go to Filter
  • Choose Label Filters
  • You can write the complete name or search using a wild card, in my case I am searching for all customer with the name ‘Sharma’. So I write *Sharma*
  • Resulting Pivot table gets me all the ‘Sharma’ customers

I learnt this from Debra, the queen of Pivots (I like that title for her 🙂

 

More Pivot Table Hacks

  1. Pivot Table formatting hacks
  2. Conditional formatting in Pivot Tables
  3. Change calculations using Slicers in Pivot Tables
  4. Find the top X Items
  5. 16 Life saving hacks in Pivot Tables

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI