If you have ever used Pivot Tables in Excel, you have no doubt discovered the wonders of filtering. When you don’t want to analyze all the items in the driving data set, the ability to filter row or column items can be extremely helpful.
Types of Filters in a Pivot Table
Types of filters available in a Pivot Table In this snapshot .
- Report Filter: This filter allows you to drill into a subset of the entire dataset. For example, if you have retail sales data, you can analyze the data by selecting one or more regions for each region (yes, it also allows multiple selections). You create this filter by dragging and dropping the Pivot Table field into the filter area.
- Row / Column Label Filters: These filters allow you to filter relevant data based on field items (such as specific items or items that filter out a specific text) or values (such as filter top 10 items with values or items with values). Allow more than / less than a specified value).
- Search box: You can access this filter within a row / column label filter and this allows you to filter faster based on the text you have entered. For example, if you only want data for Costco, type Costco here and it will filter for you.
- Check the boxes: These allow you to select specific items from the list. For example, if you want to entrust retailers to analyze, you can do so here. Alternatively, you can selectively exclude some retailers by unchecking it.
Examples of Filters in Pivot Table
- Filter the top 10 items by value / percentage / even.
- Filter items by value.
- Filter using a filter.
- Filter using the search box.
Filter the top 10 items in a heading table
You can use the top 10 filter options in the pivot table:
- Filter top / bottom items by value.
- Filter top / bottom items that make up a specified percentage of values.
- Filter top / bottom items that create a specific value.
Top 10 filter with this data set – example like below table data
Filter Top/Bottom Items by Value
Top 10 retailers based on the sales value
- Go to Row Label filter – Value Filters – Top 10.
- In the Top 10 Filters dialog box, there are four options that you need to specify:
- Top / Bottom: In this case since we are looking for the top 10 retailers, select the top.
- Number of items you want to filter. In this case since we want to get the top 10 items, it will be 10.
- The third area is a drop down with three options: item, percentage, and even. In this case, since we want the top 10 retailers, select the item.
- The last field lists all the various values listed in the Price field. In this case, since we only have sales totals, it shows only ‘sales totals’.
This will give you a filtered list of 10 retailers based on their selling price.
This will give you a filtered list of 10 retailers based on their selling price.
Filter top / bottom items that make up a specified percentage of the price
You can use the top 10 filter to get a list of the top 10 percent (or any other number, 20 percent, 50 percent, etc.) by value.
Suppose you want to get a list of retailers that make up 25% of total sales.
Here are the steps to do this:
- Go to Row Label Filter – Price Filter – Top 10.
- In the Top 10 Filters dialog box, there are four options that you need to specify:
- Top / Bottom: In this case since we are looking for top retailers that make up 25% of total sales, select Top.
- In another area, you need to specify the percentage of sales that top retailers need. In this case, since we want to get top retailers that make up 25% of sales, it will be 25.
- In the third field, select Percent.
- The last field lists all the various values listed in the Price field. In this case, since we only have sales totals, it shows only ‘sales totals’.
This will appear a filtered list of retailers that make up 25% of total sales.
Use the same process to obtain retailers that make up the lower 25% (or any other percentage) of total sales.