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 (00:04)
If you want to view a few entries in the Pivot Table, you can apply a filter using the Pivot Table field list. You select the field’s drop down list, then select the values that you want to see. You can apply these filters for multiple fields from the field list. The manual selection method works well for small data sets with a small number of field entries.
You can remove a filter by selecting a row or column label and pressing Right Mouse Button Key, T, C. This removes the filter on the currently selected label.
Filtering by Rules (01:32)
Filtering by Rules applies a rule to a row or column label, and shows only entries that conform to that rule. It’s a useful technique when filtering a field with a large number of entries, like a numeric field.
We use the method to show only salespeople generating over $200,000 in revenue from large transactions. We create a rule by creating a value filter with Right Mouse Button Key, T, S. We can then create the rule we want. It’s also possible to create a rule based on the Pivot Table labels, but this is less common.
Filter by Search (02:37)
Filtering by search is a method that’s useful when you want to find one entry from a long list. We use this method to find sales for a specific salesperson. We select the salesperson dropdown in the field list, then use the search bar to filter by the salesperson of interest.
In the previous lesson, we learned how to sort data in a PivotTable.
In this lesson, we'll learn how to apply filters to a PivotTable. There are three ways of performing simple filters on a PivotTable. 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 PivotTable field list on the right-hand side of the page. Let's say we'd only like to view the large transactions for two salespeople, Kroll and Levy, for each quarter.
We'll start by selecting the sales person dropdown in the field list, de-select all, and then select Kroll and Levy.
When we press Enter the PivotTable updates to only show those two salespeople.
Now we need to filter the subscription type to only show the large subscriptions. So again, we'll select the dropdown, de-select all, and then select large. Again, we'll press OK, and now we have exactly the data we want in our PivotTable. To remove the filters we can use the right mouse button key, T to open up the filter menu, and then C to clear the filters. Because we had a sales person selected this only cleared this salesperson filter. If we go up to large and perform the same shortcut, right mouse button, T, and then C, this removes the subscription 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 PivotTable.
Let's say that we only want to view salespeople who have generated over $200,000 in total revenue from large transactions in the last year.
We'll first select a salesperson, then right mouse button key + T for the filter option, and S for value filters.
We want to show items for which the sum of revenue is greater than $200,000.
We'll then press OK, and now the PivotTable only shows the six salespeople that satisfy this criteria.
In this scenario, we used a value filter which applied a rule to the data within the PivotTable. We can also use a label filter that applies a rule to the salespeople or the quarters. However, 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 we'll cover uses a search bar.
Let's say we'd like to find the revenues associated with the salesperson Gilman. We'll go to the salesperson dropdown and in the search bar type "gilman".
We'll then press Enter, and as expected, Gilman is now the only salesperson in our PivotTable.
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 PivotTable 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.