The Advanced Filter provides the user with more options for specifying selection criteria, taking into account AND, OR, and calculated criteria. Searching with the Advanced Filter is carried out according to the following guidelines:
- Prepare the criteria range for the Advanced Filter:
- The top row contains the field headers on which the selection will be based (these must exactly match the field headers of the list).
- The search criteria are entered into the empty rows below the prepared header row. Keep in mind:
- To apply an AND condition, place the criteria side by side in the same row.
- To apply an OR condition, place the criteria on different rows.
- To use a calculated criterion, include formulas (custom or Excel functions) whose arguments are fields of the list.
- The calculated criterion is placed under a custom header (for example, Condition), which must not match any existing field name of the list.
- Relative references are used for list fields, pointing to the top record in the list’s data range.
- References to cells outside the list must be absolute.
- The result of evaluating the criterion must be a logical value (TRUE or FALSE). The Advanced Filter selects records where the result is TRUE.
- In the case of complex conditions, the search is carried out using composite criteria with AND and OR. The criterion should be constructed with logical functions AND(), OR(), NOT().
- Place the cell pointer in the list (select the entire required list or a part of its range).
- Go to the Data tab on the ribbon, and in the Sort & Filter group, click the Advanced button. In the Advanced Filter dialog box:
- Specify the location where the filtered results should be placed.
- In the List range field, mark the entire list to be filtered (if the pointer was placed in the list, this range is selected by default).
- In the Criteria range field, specify the prepared criteria range (you can select it directly on the worksheet with the mouse).
- If the filtered records need to be copied elsewhere, specify the Copy to range.
- To filter out only unique records (without duplicates), select the checkbox Unique records only.
The results of the Advanced Filter are displayed on the same worksheet or copied to another location.
Examples
Example 1. Determine if the list contains yellow or black cars, manufactured after 2003, priced between 2,500 and 15,000 USD, or beige Mercedes cars with mileage greater than 20,000 km but less than 100,000 km.
- Open the list to be filtered (range A1:J58, header row A1:J1, worksheet Cars_List-1).
- Prepare the criteria range for the Advanced Filter.
- Go to the Data tab, in the Sort & Filter group, click Advanced.
- In the Advanced Filter dialog box, specify the required ranges and filter parameters. Click OK.
Example 2. Determine if the list contains cars manufactured after 2000 with mileage greater than 10,000 km but less than 100,000 km, or black Mercedes cars priced between 20,000 and 30,000 USD.
- Open the list to be filtered (range A1:J133, header row A1:J1, worksheet Cars_List-2).
- Prepare the calculated criterion for the Advanced Filter in the range M3:M4.
- Cell M3: enter the word Condition.
- Cell M4: enter the formula:
=OR(AND(G2>10000,G2<100000,D2>2000),AND(C2="Mercedes",F2="black",H2>20000,H2<30000))
- On the Data tab, in the Sort & Filter group, click Advanced.
- In the Advanced Filter dialog box, specify the required ranges and filter parameters. Click OK.
- The filtered data will be displayed on the worksheet.
Example 3. Determine white or red cars whose price is lower than the average price of all cars, and whose mileage is greater than or equal to the average mileage.
- Open the list to be filtered (range A1:J133, header row A1:J1, worksheet Cars_List-3).
- Prepare the calculated criterion for the Advanced Filter in the range L1:L2:
- Cell L1: enter the word Condition.
- Cell L2: enter the formula:
=AND(OR(F2="white",F2="red"),H2<AVERAGE($H$2:$H$133),G2>=AVERAGE($G$2:$G$133))
- On the Data tab, in the Sort & Filter group, click Advanced.
