XL: How to Use Advanced Filter to Exclude Records

ID: Q183512

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, version 5.0

SUMMARY

You can use the ISNA and MATCH worksheet functions exclude records in an Excel database that do not match another list.

To create exclusion criteria, use the following formula

   =ISNA(MATCH(<firstcell>,<excludelist>,0))

where <firstcell> is the first cell in the field of your database that you want to filter, and <excludelist> is the list of entries that you want to exclude.

MORE INFORMATION

When you filter records in a Microsoft Excel database, you usually specify criteria you want the records to match. However, when you use the ISNA and MATCH worksheet functions, you can create an advanced criteria that filters all records that do not match the criteria.

Example

To use this example, follow these steps:

1. On a new worksheet, type the following values:

      A1: Region  E1:         F1: Criteria
      A2: 1       E2: 1       F2: =ISNA(MATCH(A2,$E$2:$E$3,0))
      A3: 2       E3: 3       F3:
      A4: 3       E4:         F4:
      A5: 4       E5:         F5:
      A6: 1       E6:         F6:

2. Click cell A1 (or any cell in the list).

3. On the Data menu, point to Filter, and then click Advanced Filter.

4. In the Advanced Filter dialog, click "Filter the list, in place."

5. In the List Range box, type A1:A6.

6. In the Criteria range box type F1:F2.

7. Click OK.

NOTE: When you create an advanced criterion formula that excludes records, the cell reference to the list of entries that you want to exclude must be an absolute reference.

When you specify an advanced criterion formula, the cell above the formula cannot contain an actual field name; it must either be blank or contain a different text string.

REFERENCES

For more information about advanced filters, click Contents And Index on the Help menu, click the Index tab in Microsoft Excel Help, type the following text

   advanced

and then double-click the selected text to go to the "Examples of advanced filter criteria" topic. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: XL97 howto computed extract xl5 xl7 xl95

Keywords          : kbdta xllist 
Version           : WINDOWS:5.0,7.0,97
Platform          : WINDOWS

Last Reviewed: January 8, 1999