Learn how to use Advanced Filters in Excel.
Advanced Filtering in Excel
Found on the Data tab, Excel offers the Advanced Filter tool, for setting up a series of criteria to filter your data based on a preset series of fields and values from those fields.
For demonstration purposes, I’ll add it to the same worksheet that my data lives in – you’ll be able to see me create the criteria range, enter the field names and criteria, and then watch as the records are filtered based on what I’ve typed into that range.
Here in the US Cities worksheet, I can set up criteria for as many fields as I want, and any records meeting the criteria appear filtered in the list.
To get started, I’ve put some blank rows above my field names, so I’m not disrupting the data by adding the series of field names and criteria values. In row 3, I’m typing the field names that I want to filter on. The names must match the field names in the data exactly. I want to filter on states and population levels, so State Abbreviation and Population are entered into 2 adjacent cells. Then, I’ll type the criteria into the cells directly beneath those field names. I’m entering the state, CA, and a Population criteria of >100000.
Now, I need to set up the Advanced Filter to use that criteria range and filter the data. After clicking to activate a cell inside the data – so that Excel knows that I intend to filter that data – I go to the Data tab, click the Advanced Filter button, and a dialog box opens.
In the dialog box, you see that Excel has correctly determined the range of cells containing my data. If it hadn’t, I could select my data – by dragging through the field names and then pressing Ctrl + the Down Arrow to go to the last row in the data.
Now, I need to tell Excel where the Criteria Range is. I click in that field in the dialog box, and then on the worksheet, I drag through the 4 cells – the ones where I set up the field names and criteria.
Note the 2 options – Filter in place, or Copy to Another Location – these refer to where I want the results of the Advanced Filter to appear. I’ll leave it on Filter in Place. If I chose to copy the results to another location, I’d have to specify where by clicking in that field and then clicking in a cell where the copy should appear – in another worksheet.
Once I click OK, see the list is filtered, in place, based on the criteria.
To reuse the criteria range, enter new values in as many fields as desired and then click the Advanced Filter button. It should reflect the settings for the data and criteria range from its prior use, and all you need to do is click OK. Moments later, the list is re-filtered to reflect the latest criteria. When I change the Population to <100000, the list reflects that change.
To remove the filter, click the Clear button on the Data tab, and all of your records re-appear.