Learn how to utilize Excel's Advanced Filter tool, a powerful feature for applying multiple filters to your data based on specific criteria. The tool, located on the Data tab, provides the ability to adjust and customize your data analysis to suit your individual or company needs.
Key Insights
- Excel's Advanced Filter tool, found on the Data tab, allows users to create a variety of criteria to filter data based on specific fields and their values.
- In order to use the Advanced Filter, you need to create a criteria range. The field names must match the field names in the data you wish to filter.
- The Advanced Filter tool can accommodate as many fields and criteria as required, making it a versatile tool for data analysis.
- Once the criteria range is set up, use the Advanced Filter button on the Data tab to filter the data. Excel automatically identifies the range of cells containing data.
- The results of the Advanced Filter can either be filtered in place or copied to another location. This functionality provides flexibility in how you want to view and use the filtered data.
- The Advanced Filter tool also allows for easy reuse and adjustment of the criteria range for new values, making it an efficient tool for continuous data analysis.
Advanced Filter Workflow
-
Set Up Criteria Range
Headers in row 1, criteria below — same column names as data.
-
Data > Advanced
Pick list range, criteria range, output location.
-
Unique Records Option
Toggle to extract distinct values from a column.
-
Copy or Filter In Place
Copy to new location preserves original; filter in place hides rows.
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 >10,000.
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 <10,000, the list reflects that change.
To remove the filter, click the Clear button on the Data tab, and all of your records re-appear.
Video Transcription:
Full Video Transcript
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 the range.
Setting Up the Criteria 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 will 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 two adjacent cells. I'm entering the state CA for California and a population criteria of greater than one hundred thousand.
Configuring the Advanced Filter Dialog
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 and 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, as I'm demonstrating here, I could select my data by dragging through the field names and then pressing Control plus the Down Arrow to go to the last row of 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, and then drag through the four cells—the ones where I set up the field names and criteria.
Filter Output Options
Note the two 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, I see the list is filtered in place based on my criteria.
Reusing and Modifying the Criteria
To reuse the criteria range, enter new values below the 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, so all you need to do is click OK. Seconds later, the list is refiltered to reflect that latest criteria.
When I change population to less than 100,000, the list reflects the change. To remove the filter, click the Clear button on the Data tab and all of your records reappear.
