Use an Advanced Filter in Excel to create a list of unique items, or to extract specific items to a different worksheet. You can also use complex criteria with an Advanced Filter, so it is useful when a simple AutoFilter can't do what you need.
Watch this video to see the steps for setting up your criteria range, and running an Advanced Filter. The written instructions are below the video.
Here are the steps for setting up your data, and creating an Advanced Filter.
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.
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.
With an Excel Advanced Filter, you can filter the data in place, or to a different location. When applying the advanced filter, you can specify that you want to see unique items only. With that option selected, all the duplicates are removed from the filtered list.
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. In this example, the unique list is copied to a different location, and the original list in unchanged.
Note: The list must contain a heading, or the first item may be duplicated in the results.
In Excel 2007, and later versions, there is a Remove Duplicates tool. Unlike the Advanced Filter's Unique Records Only option, this does not leave the original list unchanged -- it completely removes all duplicate items from the list. Only the first instance of each item is left.
You can download the sample file that was used in this video.
With an advanced filter, you can extract data to a different sheet. Watch this video to see the steps, and the written instructions are below the video.
If the database is on one sheet, you can extract data to a different sheet, by using an Advanced Filter. In this example, the data is on Sheet1, and will be copied to Sheet2.
If a record meets all criteria on one row in the criteria area, it will pass through the Excel advanced filter. In example 1, at right --
- the customer must be MegaMart
- AND the product must be Cookies
- AND the total must be greater than 500.
Criteria on different rows are joined with an OR operator. In the second example at right --
- the customer must be MegaMart
- OR the product must be Cookies
- OR the total must be greater than 500.
By using multiple rows, you can combine the AND and OR operators. In the third example at right --
- the customer must be MegaMart AND the product must be Cookies
- the product must be Cookies AND the 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 4-letter product that begins with c, and ends with ke, will pass through the Excel advanced filter. Both Coke and Cake are in the filtered results.
The tilde (~) wildcard character lets you search for characters that are used as wildcards.
In the first example at right, an asterisk is in the criteria cell -- Good*Eats -- so any products that begins with Good and ends with Eats, will pass through the Excel advanced filter.
To find only the product that is named Good*Eats, use a tilde character in front of the asterisk in the critereia cell. -- Good~*Eats.
To extract a list of items in a range, you can use two columns for one of the fields. In this example, two columns are used for the Date field.
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 or equal to the first date AND less than or equal to 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.
In the results, only the records for MegaMart Cookies, or for MiniMart Milk will be in the filter results.
When you use text as criteria with an Excel advanced filter, Excel finds all items that begin with that text.
To extract only the records for Ice, use the following format:
Download zipped Excel advanced filter workbook with sample data and criteria.
For Excel 2003 instructions, see Excel 2003 Advanced Filter Introduction
Last updated: April 16, 2015 7:13 PM