To work with this data, select it in the following table, copy it, and then paste it in cell A1 of a new Excel worksheet.

The criteria range has column labels and includes at least one blank row between the criteria values and the list range. The data includes four blank rows above the list range that will be used as a criteria range (A1:C4) and a list range (A6:C10). The following sample data is used for all procedures in this article. Microsoft Office Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the advanced criteria. You type the advanced criteria in a separate criteria range on the worksheet and above the range of cells or table that you want to filter. It displays the Advanced Filter dialog box instead of the AutoFilter menu. The Advanced command works differently from the Filter command in several important ways. Salesperson = a name with 'u' as the second letter (Salesperson = 'Buchanan' AND Sales > 1500)

Multiple sets of criteria, one column in all sets Type = 'Produce' OR Salesperson = 'Buchanan' Multiple criteria, multiple columns, any criteria true Multiple criteria, multiple columns, all criteria true Salesperson = 'Davolio' OR Salesperson = 'Buchanan' Multiple criteria, one column, any criteria true To open the Advanced Filter dialog box, click Data > Advanced. If the data you want to filter requires complex criteria (such as Type = 'Produce' OR Salesperson = 'Davolio'), you can use the Advanced Filter dialog box.

Advanced excel 2021