How to Create Multiple Filters

At times you might want to filter same data field with multiple filters. Normally PivotTable allows you to filter data using only one filter. However, with PivotTable Options – Totals & Filters you can create two filters on same field. BestStatesForBusiness.xlsx provides you Forbes ranking list of Best States for Business.

On this data you want to shortlist the States that begin with letter A and ranks between 20 and 40.

First create a PivotTable by placing cursor anywhere on the source data, from Menu bar click Insert tab, then click PivotTable (PivotTable dialogue box opens), check whether source data is properly selected, choose New Worksheet for PivotTable report, and click OK. A Skelton PivotTable appears on screen.Now drag State to Row Labels, and Rank to Values. PivotTable is filled with data.

To create a Label Filter click the down arrow appearing on right side of Row Labels (pop up window opens) now click Label Filters, from Label Filters options select / click Begins with, Label Filter (State) dialogue box appears on screen. In the input box enter A and click OK. Now PivotTable displays States beginning with A but of different ranks. You are interested in States whose Rank is between 20 and 40.

To know the States with rank between 20 and 40, create a Value Filter by clicking the right arrow appearing on Row Labels, click Value Filters, from popup menu click Between button, Value Filter (State) dialogue box appears on screen. In input boxes enter 20 and 40 and click OK.

PivotTable displays all States whose rank is between 20 and 40. Again, this is not what you intended to get. You want to have a list of States whose name Begins with A, and rank is between 20 and 40. To get the information needed you should apply Allow Multiple Filters per Field option.

Creating Multiple Filters

Right click on the Row Labels, from the popup menu select or click PivotTable Options (PivotTable Options dialogue box appears on screen), now click Totals&Filters tab and select Allow Multiple Filters per Field clicking the Check box, and then click OK.

Now once again click Label Filters, from Label Filters options select / click Begins with, Label Filter (State) dialogue box appears on screen. In the input box enter A and click OK. Now PivotTable displays States beginning with A and rank between 20 and 40

.