Microsoft Knowledge Base

WINDOWS   | MACINTOSH

• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for the Macintosh, version 5.0
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, you cannot use the Advanced Filter and the AutoFilter together. If you use the Advanced Filter command to extract data from a list while the AutoFilter is active, the AutoFilter will be deactivated and all hidden rows will be restored before the Advanced Filter extracts data. Because the AutoFilter is deactivated before the data is extracted, you may not receive the results you expect.

WORKAROUND

Although you cannot use the Advanced Filter to extract data from a range that has an AutoFilter applied to it, you can add the conditions of the AutoFilter to the Advanced Filter criteria range. Applying this criteria will produce desired results (that is, you can use Advanced Filter criteria to filter out the data that you would have filtered out with the AutoFilter, and you can also use criteria to extract the data that you want to extract with the Advanced Filter). An example of how to do this is shown below.

To set up a sample list of data and a criteria range

1. On a new worksheet in a new workbook, enter the following values:

         A1: Alpha   B1: Bravo   C1: Charlie
         A2: 3       B2: 1       C2: a
         A3: 3       B3: 1       C3: b
         A4: 3       B4: 2       C4: a
         A5: 3       B5: 2       C5: b
         A6: 4       B6: 1       C6: a
         A7: 4       B7: 1       C7: b
         A8: 4       B8: 2       C8: a
         A9: 4       B9: 2       C9: b
   
      (This list of data will be used in the examples below.)
   
   

2. On the same worksheet, enter the following values:

         E1: Alpha   F1: Bravo   G1: Charlie
         E2: 4
   
      (These values are the criteria range you will use in the Advanced
      Filter.)
   
   

To apply an AutoFilter

1. Select cell A1.

2. From the Data menu, choose Filter, and then choose AutoFilter.

1. From the Bravo drop-down list(cell B1), choose the value 2.

To Apply an Advanced Filter that extracts data based

on the filter criteria you specified in the AutoFilter

To simulate the results of the AutoFilter with the Advanced Filter, you must use the same criteria in the Advanced Filter that you used in the AutoFilter. For example, if a field in the AutoFilter is set to show only the name "Smith," you must enter the name "Smith" into the appropriate cell below the corresponding field name in the Advanced Filter criteria range.

1. To remove the AutoFilter, choose Filter from the Data menu.

2. Because the AutoFilter in the example above used a filter of 2 in the Bravo column, enter a 2 in cell F2 (directly below the Bravo field name in cell F1).

3. From the Data menu, choose Filter, and then choose Advanced Filter.

4. In the Advanced Filter dialog box, set the following options:

         Action         : Copy to Another Location
         List Range     : $A$1:$C$9
         Criteria Range : $E$1:$G$2
         Copy to        : $I$1:$K$1
   
   

5. Choose OK to extract the records.

STATUS

This behavior is by design of Microsoft Excel.

MORE INFORMATION

In Microsoft Excel, the Advanced Filter allows you to extract data from a list. When you do this, Microsoft Excel compares each record in your list to the values entered in your criteria range, and extracts only those records that match the criteria.

However, if the AutoFilter is active when you use the Advanced Filter, Microsoft Excel will turn off the AutoFilter, unhiding any rows that were hidden by the AutoFilter.

REFERENCES

"User's Guide," version 5.0, pages 393-407