Question: In Excel 2003/XP/2000/97, I have an Excel spreadsheet and I would like to filter more than 3 types of criteria from more than 1 column. The custom auto filter only allows for 2 types at a time. How can I filter more than 1 column based on 3 or more criteria?
Answer: You can filter multiple columns based on 3 or more criteria by applying an advanced filter. To do this, open your Excel spreadsheet so that the data you wish to filter is visible.
In a blank column, add the column heading and the values that you'd like to filter on. In this example, we want to filter on both Order ID and Quantity. The filter should display all records with the following conditions:
Order ID of 10248 and quantity > 5
Order ID of 10251 and quantity >=7
Order ID of 10253 and quantity < 40
Order ID of 10251 and quantity >=7
Order ID of 10253 and quantity < 40
We've entered these values into columns F and G.
Highlight the data that you wish to filter. We've highlighted columns A to D.
Under the Data menu, select Filter > Advanced Filter.
When the Advanced Filter window appears, the List range field should display the data that you highlighted in the previous step.
Next, select the Criteria range. These are the filter values. In our example, we've entered the filter values into cells F1 to G4. Then click on the OK button.
Now when you return to your spreadsheet, your data should be filtered. (Note: The row numbers on the left will appear in blue when your data has been filtered.)
