When you use advanced filtering in Excel 2013, you don’t use the field’s AutoFilter buttons and associated drop-down menu options. Instead, you create a so-called Criteria Range somewhere on the worksheet containing the data list to be filtered before opening the Advanced Filter dialog box.
If you use the Advanced Filter feature to do a query, you extract copies of the records that match your criteria by creating a subset of the data list. You can locate the Criteria Range in the top rows of columns to the right of the data list and then specify the Copy To range underneath the Criteria Range, similar to the arrangement shown.
To create a Criteria Range, you copy the names of the fields in the data list to a new part of the worksheet and then enter the values (text, numbers, or formulas) that are to be used as the criteria in filtering the list in rows underneath. When setting up the criteria for filtering the data list, you can create either comparison criteria or calculated criteria.
After you’ve set up your criteria range with all the field names and the criteria that you want used, you click the Advanced command button on the Ribbon’s Data tab (or press Alt+AQ) to open the Advanced Filter dialog box similar to the one shown.
Here, you specify whether you just want to filter the records in the list (by hiding the rows of all those that don’t meet your criteria) or you want to copy the records that meet your criteria to a new area in the worksheet (by creating a subset of the data list).
To just filter the data in the list, leave the Filter the List, In-Place option button selected. To query the list and copy the data to a new place in the same worksheet (note that the Advanced Filter feature doesn’t let you copy the data to another sheet or workbook), you select the Copy to Another Location option button.
When you select this option button, the Copy To text box becomes available, along with the List Range and Criteria Range text boxes.
To specify the data list that contains the data that you want to filter or query, click the List Range text box and then enter the address of the cell range or select it directly in the worksheet by dragging through its cells.
To specify the range that contains a copy of the field names along with the criteria entered under the appropriate fields, you click the Criteria Range text box and then enter the range address of this cell range or select it directly in the worksheet by dragging through its cells. When selecting this range, be sure that you include all the rows that contain the values that you want evaluated in the filter or query.
If you’re querying the data list by copying the records that meet your criteria to a new part of the worksheet (indicated by clicking the Copy to Another Location option button), you also click the Copy To text box and then enter the address of the cell that is to form the upper-left corner of the copied and filtered records or click this cell directly in the worksheet.
After specifying whether to filter or query the data and designating the ranges to be used in this operation, click OK to have Excel apply the criteria that you’ve specified in the Criteria Range in either filtering or copying the records.
After filtering a data list, you may feel that you haven’t received the expected results — for example, no records are listed under the field names that you thought should have several. You can bring back all the records in the list by clicking the Clear command button on the Data tab of the Ribbon or by pressing Alt+AC.
Now you can fiddle with the criteria in the Criteria Range text box and try the whole advanced filtering thing all over again.
