How to set up an Advanced filter in Excel 2003, and send data to a different sheet, or remove duplicates.
For newer versions of Excel, see Advanced Filter Introduction
In the criteria range for an Excel advanced filter, you can set the rules for the data that should remain visible after the filter is applied. You can use one criterion, or several.
After the Excel advanced filter is applied, orders with a total greater than $500 will remain visible.
Other operators include:
< less than
<= less than or equal to
>= greater than or equal to
<> not equal to
If you plan to copy the data to another location, you can specify the columns that you want to extract. If you want to extract ALL columns, you can leave the extract range empty for the Excel advanced filter.
You can use an Excel Advanced Filter to extract a list of unique items in the database. For example, get a list of customers from an order list, or compile a list of products sold:
Note: The list must contain a heading, or the first item may be duplicated in the results.
In this short video, you'll see the steps for setting up an advanced filter so the results are moved to a different worksheet. The written steps are below the video
NOTE: It's important to select the right place in the workbook, before you start filtering!
If the database is on Sheet1, you can extract data to Sheet2, by using an Excel Advanced Filter:
If a record meets all criteria on one row in the criteria area,
it will pass through the Excel advanced filter. In the example below
customer must begin with MegaMart AND product must begin with Cookies AND total must be greater than 500.
Criteria on different rows are joined with an OR operator. In the
second example at right --
customer must begin with MegaMart OR product must begin with Cookies OR total must be greater than 500.
By using multiple rows, you can combine the AND and OR operators.
In the third example at right --
customer must begin with MegaMart AND product must begin with Cookies
product must begin with Cookies AND total must be greater than 500.
Use wildcard characters to filter for a text string in a cell.
The asterisk (*) wildcard character represents any number of characters in that position, including zero characters.
In this example, any customer whose name contains "mart" will pass through the Excel advanced filter.
The question mark (?) wildcard character represents one characters in that position. In this example any product that begins with c, followed by one character, then followed by ke, will pass through the Excel advanced filter.
The tilde (~) wildcard character lets you search for characters that are used as wildcards. In this example any products that begins with Good and has Eats somewhere in the remaining text, will pass through the Excel advanced filter.
To extract a list of items in a range, you can use two columns for one of the fields (e.g. Date). If you enter two criteria on the same row in the criteria range, you create an AND statement. In this example, any records that are extracted must be greater than the first date AND less than the second date.
If you enter criteria on different rows in the criteria range, you create an OR statement.
In this example, extracted records must meet both conditions in row 2 OR both conditions in row 3.
When you use text as criteria with an Excel advanced filter, Excel finds all items that begin with that text. For example, if you type "Ice" as a criterion, Excel finds "Ice", "Ice Cream" and "Ice Milk"
To extract only the records for Ice, use the following format:
Download zipped Excel advanced filter workbook with sample data and criteria.
For Excel 2007 and Excel 2010 instructions,
see Excel Advanced Filter Introduction
Last updated: August 20, 2020 2:42 PM
Contextures RSS Feed