In this example any 4-letter product that begins with c, and ends with ke, will pass through the filter. The question mark (?) wildcard character represents one characters in that position. In this example, any customer whose name contains "mart" will pass through the filter. The asterisk (*) wildcard character represents any number of characters in that position, including zero characters. Use wildcard characters to filter for a text string in a cell. Product must be Cookies AND total must be greater than 500.
In the third example at right -Ĭustomer must be MegaMart AND product must be Cookies In the second example at right -customer must be MegaMart OR product must be Cookies OR total must be greater than 500.īy using multiple rows, you can combine the AND and OR operators. AND total must be greater than 500.Ĭriteria on different rows are joined with an OR operator. In example 1, at right -customer must be MegaMart AND product must be Cookies 1. If a record meets all criteria on one row in the criteria area, it will pass through the filter. (optional) Check the box for Unique Values Only Select the cell on Sheet 2 in which you want the results to start, or select the headings that you have typed on Sheet 2.ġ1. (optional) Click in the Criteria range box.Ĩ. Select Sheet 1, and select the database.ħ. From the Data menu, choose Filter, AdvancedĦ. Select a cell in an unused part of the sheet (cell C4 in this example).ģ. If the database is on Sheet1 and you would like to extract data to Sheet2:Ģ. Add a check mark to the Unique records only box. Select a starting cell for the Copy to location.ħ. For the List range, select the column(s) from which you want to extract the unique values.Ħ. From the Data menu, choose Filter, Advancedįilter.(In Excel 2007, click the Data tab on theĤ. Note: The list must contain a heading, or the first item may be duplicated in the results.Ģ. For example, get a list of customers from an order list, or compile a list of products sold: You can use an Advanced Filter to extract a list of unique items in the database.
Note: If you copy to another location, all cells below the extract range will be cleared when the Advanced Filter is applied. If you are copying to a new location, select a starting cell for the copy Select the criteria range on the worksheetĦ. If not, you can select the cells on the worksheet.ĥ. Excel should automatically detect the list range. You can choose to filter the list in place, or copy the results to another location.Ĥ. (In Excel 2007, click the Data tab on the Ribbon, then click Advanced Filter.)ģ. From the Data menu, choose Filter, Advanced Filter. The column order can be different, and any or all of columns can be included.Ģ. These must be an exact match for the column headings, in spelling and punctuation. Type the headings for the columns that you want to extract. Select the cell at the top left of the range for the extracted data.Ģ. If you want to extract ALL columns, you can leave the extract range empty.ġ. If you plan to copy the data to another location, you can specify the columns that you want to extract. The > (greater than) operator is used, with the number 500 (no $ sign is included).Īfter the filter is applied, orders with a total greater than $500 will remain visible. The heading in F1 exactly matches a heading (D1) in the database.ģ. In this example, cells F1:F2 are the criteria range.Ģ. In the criteria range, you can set the rules for the data that should remain visible after the filter is applied.
There is a blank row at the end of the database, and a blank column at the right. There are no blank rows within the database.Ĥ. Excel - Filters - Advanced Filters - IntroductionĪpply an Advanced Filter For a zipped workbook with sample data and criteria, clickģ.
0 kommentar(er)
