# Microsoft Knowledge Base

## XL: Advanced Filter Deactivates AutoFilter

Last reviewed: March 27, 1997 Article ID: Q114674
5.00 7.00 | 5.00
    WINDOWS   | MACINTOSH
The information in this article applies to:

- 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.

The AutoFilter drop-down arrows will appear in cells A1, B1, and C1.

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

All records that contain the value 2 in the Bravo column will be visible, and the rest of the records will be hidden.

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.

The correct records will be extracted.

## 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

> KBCategory: kbusage
> KBSubcategory:

> Additional reference words: 5.00 7.00
> Keywords : kbusage
> Version : 5.00 7.00 | 5.00
> Platform : MACINTOSH WINDOWS

> THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 27, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.