Table of Contents
The “Does Not Contain” function in Excel Advanced Filter is used to filter out any row of data that does not contain the specific text or value you specify in the criteria. This is especially helpful when you are trying to identify which rows of data do not contain a certain value or text. For example, if you are trying to create a list of items that do not contain the word “dog”, you can use the “Does Not Contain” function to filter out any rows that have the word “dog” in them.
You can use the following syntax to filter for rows that do not contain specific text in an Excel Advanced Filter:
<>*sometext*
The following examples show how to use this function in two different scenarios:
- Filter for rows that do not contain one specific text
- Filter for rows that do not contain one of multiple text
Example 1: Filter for Rows that Do Not Contain One Specific Text
Suppose we have the following dataset that shows the total sales of certain products in certain regions for a company:
Now suppose we’d like to filter for rows where the Region does not contain “East.”
To do so, we can define a criteria range:
Next, we can click the Data tab and then click the Advanced Filter button:
We’ll choose A1:C17 as the list range and F1:F2 as the criteria range:
Once we click OK, the dataset will be filtered to only show rows where the Region does not contain “East“:
Example 2: Filter for Rows that Do Not Contain One of Multiple Text
Now suppose we’d like to filter for rows where the Region does not contain “East” or “West.”
To do so, we can define a criteria range:
Next, we can click the Data tab and then click the Advanced Filter button.
We’ll choose A1:C17 as the list range and F1:G2 as the criteria range:
Once we click OK, the dataset will be filtered to only show rows where the Region does not contain “East” or “West“:
