Page 632 - Excel 2013 All-in-One For Dummies

614 Filtering Data
that you’ve specified in the Criteria Range in either filtering or copying the records.
After filtering a data list, you may feel that you haven’t received the expected results — for example, no records are listed under the field names that you thought should have several. You can bring back all the records in the list
by clicking the Clear command button on the Data tab of the Ribbon or by pressing Alt+AC. Now you can fiddle with the criteria in the Criteria Range text box and try the whole advanced filtering thing all over again.
Specifying comparison criteria
Entering selection criteria in the Criteria Range for advanced filtering is
very similar to entering criteria in the data form after selecting the Criteria button. However, you need to be aware of some differences. For example,
if you are searching for the last name Paul and enter the label Paul in the criteria range under the cell containing the field name Last Name, Excel will match any last name that begins with P-a-u-l such as Pauley, Paulson, and so on. To avoid having Excel match any other last name beside Paul, you would have to enter a formula in the cell below the one with the Last Name field name, as in
=”Paul”
When entering criteria for advanced filtering, you can also use the question mark (?) or the asterisk (*) wildcard character in your selection criteria just like you do when using the data form to find records. If, for example, you enter J*n under the cell with the First Name field name, Excel will consider any characters between J and n in the First Name field to be a match includ- ing Joan, Jon, or John as well as Jane or Joanna. To restrict the matches to just those names with characters between J and n and to prevent matches with names that have trailing characters, you need to enter the following formula in the cell:
=”J*n”
When you use a selection formula like this, Excel will match names such as Joan, Jon, and John but not names such as Jane or Joanna that have a char- acter after the n.
When setting up selection criteria, you can also use the other comparative operators, including >, >=, <, <=, and <>, in the selection criteria. See Table 2-1 for descriptions and examples of usage in selection criteria for each of these logical operators.