Filter Excel Pivot Tables by Values

This is a guest tutorial written by Bryon Smedley of Bristol, Tennessee.

If you’ve ever used Pivot Tables in Excel, you no doubt have discovered the wonders of filtering.  The ability to filter row or column items can be extremely helpful when you don’t wish to analyze all of the items in the driving data set.

But what do you do if you wish to filter by the Value-based items?  In other words, the numbers in the “connect the dots” area where row and column choices intersect.  These, on first glance, don’t appear to have sorting and filtering controls available.

Rest assured, they do exist; you just have to dig a bit to find them.

If you right-click on any row-based item, the following menu will appear.

From here, you can filter by any of the traditional controls:

Top 10

• Filter in either direction. Top or Bottom
• Filter the target by any Value-based entry (ex: Cost or Sales)
• Filter by Item count from 1 to the maximum item count in your data set
• Filter by Percentage from 0% to 100%
• Filter by Sum from 1 to the maximum sum in your data set

Label Filters

Filter the selected column by any text or date criteria (depending on the nature of the data) with the obligatory text-based or date-based filters (i.e. Greater Than, Less Than, Contains, Begins With, etc…)

Value Filters

Filter the data by the selected Value-based entry (ex: Sales or Cost) with any of the obligatory value-based (i.e. Equal to, Greater Than, Less Than, Is Between, etc…)

Bonus Tip

Another nice trick is to select row entries (via the traditional CTRL-Click or Click-Shift-Click selection techniques) and then Right-click on one of the selected items and choose:

• Keep Only Selected Items
• Hide Selected Items

This allows you to quickly reduce a long list to a short list without the need to check and uncheck a series of tiny boxes.

Thanks

Thanks to Bryon for writing this tutorial. We appreciate you sharing your knowledge.

Bryon is from Bristol, Tennessee and has been teaching Excel since version 7 which was included with Office 95. He is currently a Technical Training Analyst for one of the largest coal companies in the world. His key responsibility is to conduct all Microsoft Office training, but in addition he also serves as a technical consultant for any and all projects involving Microsoft Office applications.

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+1, Facebook and Twitter.