Fix Multiple Does Not Contain Criteria in Excel Advanced Filter
Question details
The user needs to exclude email addresses containing several private email-provider domains by using multiple does-not-contain conditions in Excel Advanced Filter.
- Product
- Microsoft Excel Advanced Filter
- Device & OS
- not provided
- Scenario
- Filtering a large email-address list while excluding addresses that contain domains such as Gmail, Yahoo, or other private providers.
- Observed behavior
- When multiple does-not-contain criteria are placed vertically under one criteria header, Excel returns incorrect results because the conditions are evaluated with OR logic.
Make sure the criteria headers exactly match the email-address column header in the source list. Put each excluded domain pattern in its own criteria cell, such as <>*@gmail.* or <>*@yahoo.*.
Place multiple does-not-contain criteria horizontally for AND logic
Use this method when you want Advanced Filter to keep only email addresses that do not contain any of several specified domains.
Advanced Filter treats criteria on separate rows as OR conditions. To require every does-not-contain condition to be true, repeat the same source column header across adjacent columns and place all criteria on one row.
In blank cells above or beside the data, enter the email source header in two or more adjacent cells. For example, if the source header is Email, enter Email in D1, E1, and F1.
On the same row below the repeated headers, enter criteria such as <>*@gmail.* in D2, <>*@yahoo.* in E2, and <>*@outlook.* in F2.
Select any cell in the source list, then go to Data > Advanced Filter.
Set List range to the full source table, set Criteria range to the repeated-header criteria cells such as D1:F2, choose Filter the list, in-place or Copy to another location, and select OK.
Use a formula to flag email addresses containing excluded domains
Use this option when you want a helper-column result instead of an Advanced Filter, especially when the list of blocked domains may change.
Exclude Multiple Email Domains with WPS Spreadsheets Advanced Filter
WPS Spreadsheets lets you build an Advanced Filter criteria range with repeated headers and same-row conditions, so you can exclude several email-provider domains using AND logic. You can also use SEARCH-based helper formulas when a reviewable filtering workflow is preferred.
- 1. Open the email list in WPS Spreadsheets: Open the workbook in WPS Office and confirm that the email column has a single, clear header such as Email.
- 2. Build the criteria range: In empty cells, repeat the Email header across adjacent columns and enter one does-not-contain pattern below each header on the same row.
- 3. Start Advanced Filter: Select a cell in the data range, then use the Data tab and choose Advanced Filter.
- 4. Select the list and criteria ranges: Set the email table as the list range and select the repeated-header criteria block as the criteria range.
- 5. Apply and review the filtered list: Choose to filter in place or copy results to another location, then apply the filter to display only addresses that meet every exclusion rule.
Frequently Asked Questions
Why do vertical does-not-contain criteria return Gmail and Yahoo addresses?
Criteria entered vertically under one Advanced Filter header are evaluated with OR logic. A Gmail address does not match the Yahoo pattern, so it can still satisfy one condition and remain in the results.
How do I make Excel Advanced Filter use AND logic for one column?
Repeat the same source column header across adjacent criteria columns and place each criterion on the same row beneath its repeated header. Same-row criteria use AND logic.
Do the criteria headers need to match the source data header exactly?
Yes. Each criteria header must match the corresponding source-list header exactly, including spaces and punctuation, for Advanced Filter to apply the condition to that column.
Can I exclude more than two email providers with Advanced Filter?
Yes. Add another adjacent criteria column, repeat the Email header, and enter another condition such as <>*@outlook.* on the same criteria row.
What formula can identify emails containing any domain in a blocked list?
With the email in A2 and blocked terms in D2:D4, use =IF(SUM(N(ISNUMBER(SEARCH($D$2:$D$4,A2)))),"",A2). It returns blank when any blocked term is found.
