How To Filter A Pivot Table

In that location are different ways you lot can filter information in a Pin Table in Excel.

As yous'll go through this tutorial, you lot'll see there are different data filter options available based on the data type.

Types of Filters in a Pivot Table
Examples of Using Filters in Pivot Table
- Filter Acme ten Items in a Pivot Table
  - Filter Summit/Lesser Items past Value
  - Filter Height/Lesser Items that make up a Specified Percent of the Value
  - Filter Meridian/Bottom Items that make upwardly a Specified Value
- Filter Items based on Value
- Filter Information Using Label Filters
- Filter Data Using Search Box

Types of Filters in a Pivot Table

Here is a demo of the types of filters available in a Pivot Table.

Allow's look at these filters 1 past i:

Report Filter: This filter allows yous to drill down into a subset of the overall dataset. For instance, if you take retail sales information, y'all tin can clarify data for each region by selecting i or more than regions (yep, it allows multiple selections equally well). You create this filter past dragging and dropping the Pivot Table field into the Filters expanse.
Row/Cavalcade Label Filter: These filters permit you to filter relevant data based on the field items (such as filter specific particular or detail that contains a specific text) or the values (such as filter tiptop 10 items by value or items with a value greater than/less than a specified value).
- Search Box: Yous tin admission this filter within the row/cavalcade label filter and this allows yous to quickly filter based on the text you enter. For case, if you want data for Costco only, just type Costco here and it will filter that for you.
- Cheque Boxes: These permit you to select specific items from a listing. For instance, if you want to mitt pick retailers to analyze, yous can do this here. Alternatively, you lot can also selectively exclude some retailers by unchecking it.

Annotation, in that location are 2 more filtering tools bachelor to a user: Slicers and Timelines (which are not covered in this tutorial).

Permit's see some practical examples of how to utilize these to filter data in a Pivot Table.

Examples of Using Filters in Pivot Table

The following examples are covered in this department:

Filter Top x Items by Value/Percent/Sum.
Filter Items based on Value.
Filter Using Label Filter.
Filter Using Search Box.

Filter Elevation 10 Items in a Pivot Table

Yous can apply the top 10 filter option in a Pin Table to:

Filter meridian/bottom items by value.
Filter top/lesser items that make up a Specified Percent of the Values.
Filter top/bottom Items that make upwards a Specified Value.

Suppose you lot have a Pivot Tabular array equally shown below:

Let's come across how to use the Top x filter with this data set.

Filter Pinnacle/Bottom Items by Value

Y'all tin can use the Top 10 filter to go a list of top 10 retailers based on the sales value.

Hither are the steps to practise this:

Go to Row Characterization filter –> Value Filters –> Peak ten.
In the Top 10 Filter dialog box, there are iv options that yous need to specify:

This volition give you a filtered list of ten retailers based on their sales value.

You tin apply the aforementioned process to get the bottom 10 (or any other number) items by value.

Filter Superlative/Bottom Items that brand up a Specified Percent of the Value

You can apply the pinnacle 10 filter to get a listing of meridian 10 percent (or any other number, say twenty percentage, l percent, etc.) of items past value.

Let'southward say you desire to get the listing of retailers that make up 25% of the total sales.

Here are the steps to exercise this:

Go to Row Label filter –> Value Filters –> Top 10.
In the Top ten Filter dialog box, there are four options that you need to specify:

This will give you a filtered list of retailers that brand up 25% of the total sales.

You can utilise the aforementioned process to get the retailers that make upwards the bottom 25% (or whatsoever other pct) of the total sales.

Filter Top/Bottom Items that make up a Specified Value

Let's say you lot want to find out the top retailers that account for twenty one thousand thousand in sales.

You can do this using the Top 10 filter in the Pivot Table.

To do this:

Go to Row Label filter –> Value Filters –> Superlative x.
In the Height ten Filter dialog box, there are 4 options that you lot need to specify:

This volition give you lot a filtered list of top retailers that make up 20 Million of the full sales.

Filter Items based on Value

You lot can filter items based on the values in the columns in the values area.

Suppose you have a pin tabular array created using retail sales data as shown below:

Yous tin filter this list based on the sales value. For case, suppose you lot want to become a list of all the retailers that take sales more than three meg.

Here are the steps to do this:

Become to Row Label filter –> Value Filters –> Greater Than.
In the Value Filter dialog box:
Click OK.

This would instantly filter the list and show only those retailers that have sales more than 3 million.

Similarly, in that location are many other conditions that you lot can utilise such every bit equal to, does not equal to, less than, betwixt, etc.

Filter Data Using Label Filters

Characterization filters come in handy when you have a huge list and y'all want to filter specific items based on its proper noun/text.

For example, in the list of retailers, I can quickly filter all the dollar stores by using the status 'dollar' in the name.

Here are the steps to do this:

Become to Row Label filter –> Label Filters –> Contains.
In the characterization filter dialog box:
Click OK.

You tin also employ wildcard characters along with the text.

Note that these filters are not additive. And then if y'all search for the term 'Dollar', it will give you a list of all the stores that have the word 'dollar' in information technology, just if you so again use this filter to get a list using another term, it volition filter based on the new term.

Similarly, you can utilise other label filters such every bit begins with, ends with does not contain, etc.

Filter Data Using Search Box

Filtering a list using search box is a lot like the contains option in the label filter.

For case, if y'all take to filter all the retailers that have the proper name 'dollar' in information technology, simply type dollar in the search box and it will filter the results.

Here are the steps:

This would instantly filter all the retailers that incorporate the term 'dollar'.

You tin can use wildcard characters in the search box. For example, if you lot desire to get the name of all the retailers that commencement with the alphabet T, use the search cord equally T* (T followed by an asterisk). Since asterisk represents whatever number of characters, this means that the proper noun can contain whatever number of characters afterward T.

Similarly, if yous want to get the list of all the retailers that stop with the alphabet T, use the search term as *T (asterisk followed by T).

There are a few important things to know virtually the search bar:

If yous filter once with one criterion and then filter again with another, the first benchmark is discarded and you become a list of the second criteria. The filtering is not additive.
A benefit of using search box is that yous tin manually deselect some of the results. For example, if you lot have a huge list of financial companies and y'all just want to filter banks, you can search for the term 'banking concern'. But in instance some companies pitter-patter in that are not banks, you lot tin can but uncheck it and keep information technology out.
Y'all can not exclude specific results. For instance, if you want to exclude merely the retailers that contain dollar in information technology, there is no way to do this using the search box. This can, however, be done using the label filter using the 'does non incorporate' condition.

You May Also Like the Post-obit Pin Table Tutorials:

Creating a Pin Table in Excel – A Pace by Pace Tutorial.
Preparing Source Information For Pivot Table.
Group Numbers in Pivot Table in Excel.
Grouping Dates in Pivot Tables in Excel.
Refresh Pivot Tabular array in Excel.
Delete a Pivot Table in Excel.
How to Add and Use an Excel Pin Tabular array Calculated Field.
Apply Provisional Formatting in a Pivot Tabular array in Excel.
Pivot Cache in Excel – What Is Information technology and How to Best Use It?
Replace Bare Cells with Zeros in Excel Pivot Tables.
Count Distinct Values in Pin Tabular array