Microsoft Excel 2010 : Using the Advanced Filter (part 1) - Using the Criteria Range

In spite of the visual simplicity of the Advanced Filter dialog, it can fill a great choice of functions. According to the selected options and the installation on the sheet, the advanced filter can make what follows:

• Filter records in place

• Filter records to a new location on the same or different sheet

• Reorganize columns

• Use formulas as criteria

• Filter for unique records

Select either Filter the List, In-Place or Copy to Another Location to tell the function where to put the resulting dataset. If copying the results to a new location, specify the location in the Copy To field. When specifying the range

• If the results include all columns of the dataset in the original order, only the location of the first header needs to be specified.

• If the results consist of any change to the headers, whether it’s a new order or fewer headers, copy the headers to use in the desired order to a new location. The Copy To range must include the entire new range of headers.

• If results need to be on another sheet, the Advanced Filter must be called from the sheet where the results will be placed.

List Range is the dataset, including required headers. Most Advanced Filter functions require each column to have a header.

The Criteria Range is where rules are configured for the filter.

Select Unique Records Only if duplicates should not be included in the results.

Using the Criteria Range

Criteria can consist of exact values, values with operators, wildcards, or formulas. You should keep the following things in mind when setting up the criteria range:

• Except for when the criterion is a formula, the first row must consist of the column header used by the filter.

• Starting in the second row, enter the criterion to filter for in the column.

• Criteria entered on the same row are read as joined by AND. In the top table of Figure 1, the criteria in the first row is read West AND ABC.

  Figure 1. The top table shows the criteria properly configured to return all ABC products for West and East regions. The configuration of the bottom table’s criteria will return ABC products for West, but all products for East.

  

• Criteria entered on different rows are read as joined by OR. In the top table of Figure 1, the criteria is read West AND ABC OR East AND ABC.

• If a cell in the criteria range is blank and has a column header, this is read as returning all records that match the column header. In the bottom table in Figure 1, the data returned will be West and ABC or all data in East.

• Operators (<, >, <=, >=, <>) can be combined with numeric values for a more general filter.

• Wildcards can be used with text values. An asterisk (*) replaces any number of characters. A question mark (?) replaces a single character. The tilde (~) allows the use of wildcard characters in case the text being filtered uses such a character as part of its value.

• If the criterion is a formula, do not use a column header as it is applied to the entire dataset. The formula should be one that returns a TRUE or FALSE.