Microsoft Excel 2007 tutorial: filter data with advanced criteria


Filter data with advanced criteria

III. Filter data with advanced criteria

You can filter data in a range of cells or table with complex criteria by using Advanced command in the Sort & Filter group on the Data tab. The Advanced command works differently from the Filter command as the following:

- It displays the Advanced Filter dialog rather than the AutoFilter menu.

- You enter the advanced criteria in a different criteria range on the worksheet, above the range of cells or table that you intend to filter. Excel uses the different criteria range in the Advanced Filter dialog box as the source for advanced criteria.

III.1. Filter text

Filtering text data by using Advanced command is more complicate filtering numbers or dates/times because you need to know the signs that are used with text. Those signs are shown the in the table. We use the following data range as the example to make you easy to understand.

Excel 2007 data range

What you type in the cellDescription
="=Lavy"Display cells that equal name Lavy.
<>Lavy Display cells that do not contain name Lavy.
LavyDisplay cells that starts with name Lavy.
>LavyDisplay cells data that are under the cell that contains name Lavy. The cells data are sorted in ascending order automatically when you use this criteria.
*Lavy* Display cells that contain name Lavy. For example, cell A2 contain name Lavy Kheam, so only this cell are displayed.
?Lavy Display cells that have one letter before name Lavy.
=" =???"Display cells that contain only three letters.

Ex1: By using the data range above, display data rows that exactly matches the name Lavy or Khemrin (Name="Lavy"or Name="Khemrin"). To do this task, do the following:

1. Insert at least three blank rows above the data range. The three blank rows must have the column labels because you will use them as the criteria range.

Excel 2007 insert 3 blank rows

2. Enter ="=lavy" and ="=khemrin" in separate rows below the Name column.

Excel 2007 enter criteria in cells

3. Select the data range (A5:C8) that you want to filter.

4. On the Data tab, in the Sort & Filter group, click Advanced.

5. In the Advanced Filter dialog box, in the Criteria Range box, enter or select the criteria range (A1:A3).

Excel 2007 type criteria in the Criteria range box

6. Click Ok button.

You get the result as the figure below:

Excel 2007 result of filtering data

Ex2: You want to displays all rows that are under the row containing name Khemrin. To do this task, follow setp1 to step6 of solving example one, but in step2 you need to change the criteria by entering >Khemrin in the first row below the Name column instead.

After you filtered text, you will get the result as the figure below:

Excel 2007 result of filter text of example2

III.2. Filter numbers

Filtering numbers is easier than filtering text, you just use the comparison operators, such as equal sign(=), greater sign (>), less sign (<), greater or equal to sign (>=), less than or equal to sign (<=), and not equal to sign (<>).

Ex1:  You want to display data rows that contain salary greater than or equal to $ 1000.00 (Salary>=$ 1000.00). To do this task, do the following:

1. Insert at least three blank rows above the data range. The three blank rows must have the column labels because you will use them as the criteria range.

2. Enter >=1000 in the first row below the Salary column.

Excel 2007 criteria below salary column

3. Select the data range (A5:C8) that you want to filter.

4. On the Data tab, in the Sort & Filter group, click Advanced.

5. In the Advanced Filter dialog box, in the Criteria Range box, enter or select the criteria range (C1:C2).

6. Click Ok button.

You get the result as the figure below:

Excel 2007 result of filter numbers example1

Ex2: Use the data range of example above to filter rows that contain values between 740 and 1800 (Salary >$ 750.00 and Salary <$1800.00). To do this task, do the following:

1. Insert at least three blank rows with the column labels and one more Salary column above the data range.

2. Enter >740 and <1800 in the same row below the Salary columns.

Excel 2007 enter multiple criteria

3. Select the data range (A5:C8) that you want to filter.

4. On the Data tab, in the Sort & Filter group, click Advanced.

5. In the Advanced Filter dialog box, in the Criteria Range box, enter or select the criteria range (C1:D2).

6. Click Ok button.

You get the result as the figure below:

Excel 2007 result filter numbers example2 

The following examples show you more how to filter text and numbers.

Ex1. Use the following data rang to display rows that contain name ending with letter "y" and salary greater or equal to $ 750.00 (Name="*y" and Salary>=$ 750.00).

Excel 2007 data range example 

Do do this task, do the following:

1. Insert at least three blank rows with column label that can be used as the criteria range above the data range.

2. Enter ="*y" or ="=*y" and >=750 in the same row below the Name and Salary columns. 

Excel 2007 enter criteria in the same row

Note: When you enter ="*y" in the cell and press Enter or lose focus from the cell, you don't see the cell display =*y; it displays *y. To see its formula, click the cell and see the formula bar.

3. Select the data range (A5:C9) that you want to filter.

4. On the Data tab, in the Sort & Filter group, click Advanced.

5. In the Advanced Filter dialog box, in the Criteria Range box, enter or select the criteria range (A1:C2).

6. Click Ok button.

The result you will get, see the figure below:

Excel 2007 result of filter text and numbers 

Ex2: Display only rows that have salary less than average of all salaries (A5:C9). To solve this problem, do the following:

1. Insert at least three blank rows with column label and one more column name Calculated Average that can be used as the criteria range above the data range.

2. Enter =C6< Average ($C$6:$C$9) in the first cell below the Calculated Average column. C6 refers to the filtered column (C) of the first row of data range.

Excel 2007 enter criteria below calculated average column

Note: After you type =C6<AVERAGE($C$6:$C$9) in the cell and press Enter or lose focus from the cell, Excel processes this formula so that you don't see this formula in that cell. It displays logical values (TRUE OR FALSE). If you want to see its formula, click the cell and you will see the formula appearing in the formula bar.

3. Select the data range (A5:C9) that you want to filter.

4. On the Data tab, in the Sort & Filter group, click Advanced.

5. In the Advanced Filter dialog box, in the Criteria Range box, enter or select the criteria range (D1:D2).

6. Click Ok button.

You get the result as the figure below:

Excel 2007 result filter value less than average

Ex3.  In the data range (A5:C9), you want to display rows that contain name "Lavy" and salary greater than or equal to $ 750.0. Note that the name must start with capital letter (L). Because Excel does not distinguish between upper character and lower character when filtering text data, you need to use the EXACT function to perform case-sensitive search. Do the following:

1. Insert at least three blank rows with column label and one more column name Exact Match that can be used as the criteria range above the data range.

2. Enter >=750 and =EXACT(A6,"Lavy") in the same row below the Salary and Exact Match columns. A6 refers to the filtered column (A) of the first row of data range.

Excel 2007 type criteria with function

Note: After you type =EXACT(A6,"Lavy") in the cell and press Enter or lose focus from the cell, Excel calculate this formula so that you don't see this formula in that cell. It displays logical values (TRUE OR FALSE). If you want to see its formula, click the cell. The formula appears in the formula bar.

3. Select the data range (A5:C9) that you want to filter.

4. On the Data tab, in the Sort & Filter group, click Advanced.

5. In the Advanced Filter dialog box, in the Criteria Range box, enter or select the criteria range (C1:D2).

6. Click Ok button.

You get the result as the figure below:

Excel 2007 result of filter text and numbers with function

Note: You can define the name Criteria for the criteria range, the name Database for the data range, and the name Exact for the area where you want to past the rows. These ranges will appear automatically in the List range, Criteria range, and Copy to boxes.

 


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.