Microsoft KB Archive/114674

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

 To remove the AutoFilter, choose Filter from the Data menu. 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). From the Data menu, choose Filter, and then choose Advanced Filter.  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  Choose OK to extract the records.</li></ol>

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.