Use advanced criteria to filter data in an Excel table

To pass a condition specified by a complex condition conditional: To limit the records contained in a query result set. For example, the following conditions are used to select records with a value greater than 30,000 in the Order Amount field: Order Amount > 30000. To filter the range of cells, use the advanced command in the sort and filter group on the Data tab. The advanced command works differently from the filter command in several important ways.

• It shows the Advanced Filter dialog box, not the AutoFilter menu.

• You can type advanced criteria in a worksheet and in a separate criteria area on a range of cells or tables that you want to filter. Microsoft Office Excel uses a separate criteria area in the Advanced Filter dialog box as the source for advanced criteria.

1. Insert at least three blank lines above an area that can be used as a condition area. The criteria range must have a column label. Make sure that you leave at least one blank line between the criteria value and the range.

　　Sample criteria range and the data region for the conditional sample in step 2

	A	B	C
1	type	Sales Person	Sales
2	 	 	 
3	 	 	 
4	 	 	 
5	 	 	 
6	type	salesperson	sales
7	"> drink	su operation Ping	￥5122
8	meat	Ted	Agricultural products	lindane	￥6328
Ten	agricultural products	Ted	￥6544

2, in the row below the column label, type the condition you want to match.

　　condition Example

Points

Because an equal sign (=) is used to represent a formula when you type text or a value in a cell, Excel evaluates what you type; however, this can result in unexpected filtering. To represent the equality comparison operator for text or values, type a condition as a string expression in the corresponding cell in the criteria range:

= ' = ' = Entry '

Where the entry is the text or value to find. For example:

what you type in a cell	What Excel calculates and displays
= "= Ted"	= Ted
= "= 3000"	=3000

Excel is case-insensitive when filtering text data. However, you can use a formula to perform a case-sensitive search. For an example, see filtering text with a case-sensitive search.

3, click the cell in the area.

4, on the Data tab, in the sorting and filtering group, click Advanced.

5. To filter the range by hiding rows that do not meet the criteria, click Show Filter results in an existing area.

To filter the range by copying rows of data that match the criteria to another location on the worksheet, click Copy Filter results to another location, click the left mouse button in the Copy to edit box, and then click the upper-left corner of the area where you want to paste the row.

6, in the Criteria range box, enter a reference to the criteria range, which includes the criteria label.

To temporarily remove the Advanced Filter dialog box when you select a criteria range, click Compress dialog Box .

7. To change how you filter the data, change the values in the criteria range, and then filter the data again.

Comments

• You can name a range as criteria, and a reference to that range appears automatically in the Criteria range box. You can also name the range of data that you want to filter as "Database" and name the area of the row you want to paste as "Extract," so that these areas automatically appear in the data range and copy to box.

• When you copy filtered rows to another location, you can specify the columns you want to copy. Before you filter, copy the column labels for the columns you want to the first row of the area where you plan to paste the filtered rows. When filtering, enter a reference to the copied column label in the Copy to box. In this way, the copied rows will contain only the columns that have been copied.