Using an Advanced Filter in Excel

Advanced filters in Excel allow you to filter data based on multiple, complex criteria. This feature is especially useful when you need to filter data with specific conditions that go beyond the basic filter options. Here’s how you can set up and use advanced filters step by step.

Step 1: Set Up the Criteria Range

Before using the advanced filter, you’ll need to create a range for your filter criteria. Here’s how:

1. Insert rows above your data: Add at least a few rows above your data to set up your filter criteria.
   • One row will be used for column labels.
   • Another row will be blank to separate the criteria from the data.
   • Enter the column labels (matching your data) in the top row.

For example, if you have columns such as “Location ID” and “Lead”, you will enter these headers in the top row of your criteria range.

Step 2: Enter Filter Criteria

You’ll enter the specific conditions you want to filter by in the rows beneath the labels. Use the following format:

• Equal to: ="=value"
• Not equal to: ="<>value"
• Less than: ="<value"
• Greater than or equal to: =">=value"

For example:

• Equal to “Smith”: ="=Smith"
• Less than 100: ="<100"

These criteria will be applied when you run the advanced filter.

Step 3: Apply the Advanced Filter

1. Select your data: Click any cell within the range of data you want to filter.
2. Go to the Data tab and click Advanced in the Sort & Filter group.
3. In the Advanced Filter dialog:
   • List range: Confirm or select the range of data you want to filter.
   • Criteria range: Select the criteria range by dragging over your criteria labels and values.
   • Copy to: (Optional) If you want to display the filtered data elsewhere, select a cell for the filtered results.
   • Unique records only: Check this box if you want to display only unique records.

Click OK to apply the filter.

Single Criterion, Single Column Filter

To filter data based on one condition in one column:

1. Enter your condition (e.g., ="=2B") below the corresponding column label.
2. Follow the steps above to open the Advanced Filter tool.
3. Choose your List range and Criteria range.
4. Click OK to filter your data.

Multiple Criteria, Single Column Filter

To filter data based on multiple conditions in one column:

1. Enter the conditions in separate rows below the column label (e.g., ="=1B" in row 2 and ="=2B" in row 3).
2. Select the Advanced Filter tool.
3. Adjust the Criteria range to include all the conditions.
4. Click OK to apply the filter.

This will show rows where Location ID is either 1B or 2B.

Multiple Criteria, Multiple Columns Filter (AND or OR)

You can use multiple criteria across several columns, and you can apply both AND and OR logic.

AND Criteria (All Conditions True)

To filter using AND criteria (all conditions must be met):

1. Enter your conditions in the same row below their corresponding labels. For example:
   • ="=1A" below the Location ID label.
   • ="=Jones" below the Lead label.
2. Select the Advanced Filter tool and adjust the Criteria range to cover both conditions in the same row.
3. Click OK to apply the filter.

This will filter data to show rows where Location ID is 1A and Lead is Jones.

OR Criteria (Any Condition True)

To filter using OR criteria (any condition can be met):

1. Enter your conditions in separate rows below the corresponding labels. For example:
   • ="=1B" below the Location ID label in row 2.
   • ="=Jones" below the Lead label in row 3.
2. Select the Advanced Filter tool and adjust the Criteria range to include both rows.
3. Click OK to apply the filter.

This will show rows where Location ID is 1B or Lead is Jones.

Combining AND and OR Criteria

You can combine AND and OR criteria within the same filter. For example, to filter for Location ID = 1A and Lead = Jones, or Sales > 50,000:

1. Enter the following conditions:
   • ="=1A" below Location ID.
   • ="=Jones" below Lead (same row as Location ID).
   • =">50000" below Sales (in a new row).
2. Use the Advanced Filter tool and adjust the Criteria range to include both AND and OR conditions.
3. Click OK to apply the filter.

This will filter for Location ID = 1A and Lead = Jones or Sales greater than 50,000.

Unlock powerful features with a genuine Office 2021 Professional Plus Key – get the best deal at the lowest price today!