Sign in or start a free trial to avail of this feature.
9. Apply Filters to Pivot Tables
Simple filters can be applied to Pivot Tables by manual selection, creating rules and using the search bar. This lesson covers all 3 methods.
Manual selection filters
- This filter manually selects individual entries in the field list dropdown menu
- It's particularly useful when you have a small number of field entries
- When removing filters, the selected cell determines which filter will be removed
Filtering by rules
- Only the entries that abide by a specific rule are show on Pivot Table
- Rules can be implemented for numerical values using <,=, >, operators - Rules-based filters are useful when you have very large number of field entries
Filter by search
- The most efficient method if you want to find one entry from a large list
- Otherwise, use manual selection for filtering
Useful keyboard shortcuts
RMBK, T, C: Remove a filter
RMBK, T, S: Create a rules filter for Values
There are three ways of performing simple filters on pivot tables.
The first of these is manual selection.
If you have a few entries that you wish to view, you can apply simple filters by using the pivot table field list on the right-hand side of the page.
Let’s say you’d only like to view the large transactions by two sales people, Kroll and Levy, for each quarter.
Let’s start by selecting the sales person drop down in the field list.
We’ll deselect all, and then we’ll select Kroll and Levy.
And when I press Enter, the pivot table updates to only show those two sales people.
Now I need to filter the subscription type to only show the large subscriptions.
So again I’ll select the drop down, deselect all, and then select Large.
Again I’ll press OK, and now I have exactly the data I want in my pivot table.
To remove the filters, I can use the right mouse button key T to open up the Filter menu, and then C to clear the filters.
Because I had a sales person selected, this only cleared the sales person filter.
If I go up to Large and perform the same shortcut, right mouse button T and then C, this removes the subscription type filter as well.
For smaller data sets such as this one, manual selection is a particularly effective filtering method.
The second method we’re going to cover is filtering by rules.
In this scenario, the user sets a rule, and only the numbers that abide by that rule are shown on the pivot table.
Let’s say that I only want to view the sales people who generated over $200,000 in total revenue from large transactions in the last year.
I’ll first select a sales person, and then right mouse button key T for the filter option, and then I’ll press S for value filters.
And I want to show the items for which the sum of revenue is greater than $200,000.
I’ll then press OK, and now the pivot table only shows me the six sales people that satisfy this criteria.
In this scenario, I apply the values filter which applied a rule to the data within the pivot table.
I can also apply a labels filter that applies a rule to the sales people or the quarters, but this is not nearly as useful because the labels are rarely numerical values which a rule requires to be effective.
The last method of simple filtering I’d like to show you uses a search bar.
Let’s say I’d just like to find the revenues associated with the sales person Gilman.
I’d go to the sales person drop down, and in the search bar I’ll type Gilman.
Then I’ll press Enter and as you’d expect, Gilman is now the only sales person in our pivot table.
Filtering by search works quite well when you only want to find one entry among a long list of alternatives.
Otherwise, manual selection is typically the better way to go.
Unfortunately, traditional filters, be they search, rules or manual selection, have one big drawback.
Although Excel tells you what fields have filters in place using a funnel icon, they don’t tell you what those filters are.
If you have multiple filters applied to a pivot table at one time, it can be difficult to remember what rules or selections they include.
To remedy this problem, Excel 2010 introduced slicers, which we’ll explore in the next lesson.