XL: Results of AutoFilter Criteria Different from Advanced

ID: Q115310

The information in this article applies to:

SYMPTOMS

In Microsoft Excel, when you use a single character as the criteria in the Custom AutoFilter dialog box to filter data, you receive different results than you do when you use a single character as the criteria for the list in the Data Form or the Advanced Filter dialog boxes.

CAUSE

This behavior is by design of Microsoft Excel. The behavior occurs because you can use the wildcard character (*) in the Custom AutoFilter dialog box to represent any series of characters. Because of this capability, only records that exactly match the criteria you enter (the exact characters you enter) are displayed.

When you use the Data Form or the Advanced AutoFilter dialog box, all of the records that begin with the characters you enter as the criteria are displayed as if a wildcard character is added to the characters that you enter as the criteria.

WORKAROUND

When you use the AutoFilter feature to match all records that contain data that begins with the characters you use as the criteria, type the wildcard character (*) at the end of the characters you enter in the Custom AutoFilter dialog box.

REFERENCES

In Excel 97

For more information about filtering a list using AutoFilters, click Contents And Index on the Help menu, click the Index tab in Excel 97 Help, type the following text 

   filters, autofilter
and then double-click the selected text to go to the "Display a subset of rows in a list by using filters" topic. If you are unable to find the information you need, ask the Office Assistant.

In Excel 5.0

For more information about filtering a list using AutoFilters, click the Search button in Help and type: 

   filtering lists
Additional query words: 5.00 5.00a 5.00c 7.00 7.00a 97 XL97 XL7 XL5 
Keywords          : kbdta xllist 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: January 7, 1999