Home > Filters > Advanced > Get Started Advanced Filter Criteria ExamplesThese Microsoft Excel advanced filter examples take you beyond the basic steps. See how to find top records, filter for blank cells, find records in a date range, and more examples of complex criteria. There are written steps, videos, and a sample file to download. NOTE: In Excel 365, you can use the new FILTER Function to return a dynamic list of records based on criteria. Author: Debra Dalgleish |
Set Up Advanced Filter Criteria RangeThis video shows the steps for setting up a custom filter with a criteria range, and running an Advanced Filter. The examples below show how to set up more complicated criteria, such as finding rows with blank cells. For the written steps on setting up a basic Excel Advanced Filter, go to the Advanced Filter Introduction page. |
What Goes in Criteria Cells?Below the heading row, at least one row with criteria is required. In the criteria cells, you can enter the following:
Here is a simple criteria range example:
More ExamplesThere are many more criteria range examples in the sections below, that show how, and where, to enter the criteria that you need. |
Where Does the Criteria Range Go?The examples on this page show the criteria range near the data, but that isn't required. For example, the criteria range can be located:
|
Why Put Criteria Range Above Data?Many beginner tutorials that I've seen rcommend putting the criteria range above the data that will be filtered. That location is NOT required, but it does have the following advantage:
Note: This setup requires extra room at the top of the worksheet, and works best for a small criteria range. Criteria Beside the DataIf the criteria range is set up in columns to the left or right of the data, and in some of the same rows there is the following disadvantage:
Note: The hidden criteria rows will still be applied to the advanced filter. You just won't be able to see the criteria rules in those cells. Where Do I Put the Criteria Range?Usually, I put the Advanced Filter criteria range to the right of the source data, with one or more blank columns between them. That way, I don't need to leave space at the top of the worksheet, which is usally "prime real estate". The examples on this page show the criteria range to the right of the data, but you can choose whatever location you prefer. |
Why Are Criteria on Multiple Rows?If one or more criteria are in the same row of the criteria range, the Advanced Filter treats those criteria as if they are inside an AND function. AND RuleFor example, in the screen shot below, there are 3 criteria rules in the same row. As a result, the Advanced filter uses AND logic:
OR RulesIf there are criteria on multiple rows, the Advanced Filter treats those criteria as if they are inside an OR function. In this example, there are 3 rows with criteria rules, with one criterion in each row. As a result, the Advanced filter uses OR logic:
Note: The empty cells mean, "no criteria for this data" AND/OR RulesBy using multiple rows, you can combine the AND and OR operators, like the criteria rules in this example:
|
Using Wildcards in CriteriaWhen typing criteria for a filter, you can include wildcard characters, to make the criteria rule more flexible. There are three Excel wildcard characters shown in the examples below -- The * Wildcard (asterisk) -- The ? wildcard (question mark) -- The ~ wildcard (tilde) NOTE: Because Excel interprets text criteria as "begins with", you don't need to use the * or ? wildcard characters at the end of the string The * wildcardThe asterisk (*) wildcard character represents any number of characters in that position, including zero characters.
NOTE: Upper and Lower case letters are treated equally - the filter is not case sensitive. |
Formulas in Advanced Filter CriteriaMost of the examples below use formulas in the criteria area. When you use a formula:
This video shows an example. Advanced Filter Sample FileTo follow along with the examples on this page, get the Excel workbook with these Advanced Filter criteria examples and sample data. The zipped workbook is in xlsx format, and does not contain any macros. |
Run an Excel Advanced FilterFor all of the examples shown below, you will follow these steps, to run the Advanced Filter tool, after setting up the criteria area. NOTE: If you want to send the data to a different sheet, follow the steps in the section Send Data to Another Sheet, below
Advanced Filter Dialog BoxWhen the Advanced Filter dialog box opens, follow these steps:
|
Top Records After Specific DateIn this example, the filter will extract the five records with highest totals, for records that were entered on or after a specific date. If you download the sample file, there is also an example with number filters for finding the highest totals within a specific date range. NOTE: To see the steps for showing top 10 based on additional criteria in an AutoFilter, go to my blog post on Top 10 in Filtered Rows
|
Filter Rows with Blank CellsThis video shows the steps for creating an Advanced Filter for rows with blank cells, please watch this short video. The written instructions are shown below the video. |
Option 1 - Filter for Blank CellsThis is the option shown in the video, above. Use these criteria range settings to filter rows with blank cells
In each record, the value in column C is checked. If it is an empty string, the record passes through the Excel advanced filter. |
Option 2 - Filter for Blank CellsUse these criteria range settings to filter rows with blank cells
In each record, the value in column C is checked. If the cell is empty, the record passes through the Excel advanced filter. |
Filter to Match Items in a ListYou can create a list of items on a worksheet, then filter another list, to show only the records that contain those items. There are 2 examples below:
For more examples, see the Filter with Criteria List page. 1. Exact Match For Items in ListThe COUNTIF function is used in the Criteria range, to check each record, and test for the list items. Rows with an exact match are returned in the filter. Written instructions are below the video.
|
Two criteria are used in this advanced filter
NOTE: To filter the results to a different sheet, start the filter from the destination sheet. See the details here. To set up the Advanced Filter:
In each record, the value in column C is checked. If it is in the list
in column I, and the total is greater than 1000, the record passes through
the Excel advanced filter. |
2. Contain an Item in the ListThe SUMPRODUCT and COUNTIF functions are used in the Criteria range, to check each record, and test for the list items. Rows that contain an item in the list, anywhere in the Product cell, are returned in the filter. Two criteria are used in this advanced filter
To set up the Advanced Filter:
In each record, the value in column C is checked. If it contains an item from the list in column I, and the total is greater than 100, the record passes through the Excel advanced filter.
|
Get Sample FilesCriteria Examples: Get the Excel workbook with these Advanced Filter criteria examples. The zipped workbook is in xlsx format, and does not contain any macros. Match Items in List: Get the Excel workbook used in the video, Filter to Match Items in List. The zipped workbook is in xlsx format, and does not contain any macros. Advanced Filter Macros: To see VBA code for working with advanced filters, and to get sample files, go to the Advanced Filter Macros page. |
More Filter Tutorials |
Last updated: July 19, 2023 3:13 PM