Excel Advanced Filter: Apply “Does Not Contain” (2 Methods)

You can use the Does Not Contain feature of Advance Filter to filter out the rows that do not contain a specific text or multiple texts. In this tutorial, I will show you how to use Excel Advanced filter does not contain feature to hide the rows that do not contain specific text.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


2 Easy Ways to Use Does Not Contain Feature in Advanced Filter in Excel

Let’s assume a scenario where we have an Excel worksheet that has information about the computer accessories a shop sold to the customers. The Excel worksheet has the Product Name, Product Category, and the shipping Address to deliver the product. Now we will use the worksheet to show you how to use Excel Advanced Filters does not contain feature to hide the rows that do not contain specific text. The image below shows the worksheet after we have applied the Does Not Contain filter to it.

Excel Advanced Filter Does Not Contain

The specific syntax to use Does Not Contain filter is like below.

<>*Text That We Do Not Want To Contain*

Method 1: Filter Rows that Does Not Contain Single Specific Text in Excel

We can use the Does Not Contain feature in Advance Filter to filter out the rows that contain a single specific text. For example, we will filter out the rows that contain Hardware as Category. Let’s see how we can do that.

Step 1:

  • First, we will write the words Category in cell F4 and <>*Hardware* in cell F5.

Excel Advanced Filter Does Not Contain

Step 2:

  • Now, we will click on Advanced from the Sort and Filter under the Data.

Excel Advanced Filter Does Not Contain

  • A new window titled Advanced Filter will appear.
  • Now, we will insert $B$4:$D$20 in the List range input box.
  • Then we will enter $F$4:$G$5 in the Criteria range input box.
  • Finally, we will click on OK.

Excel Advanced Filter Does Not Contain

  • Now, we will see that the Advanced Filter has filtered out the rows that contain Hardware as Category.

Excel Advanced Filter Does Not Contain

Read More: How to Use Advanced Filter If Criteria Range Contains Text in Excel


Similar Readings


Method 2: Filter Rows that Does Not Contain Multiple Texts in Excel

We can also use the Does Not Contain feature to filter out rows that do not contain multiple texts. For example, we will filter out the rows that contain Hardware as Category and New York as Address. We have to do the following.

Step 1:

  • First, we will write the words Category in cell F4 and Address in cell G4.
  • Then, we will write <>*Hardware* in cell F5 and <>*New York* in cell G5.

Filter Rows that Does Not Contain Multiple Texts

Step 2:

  • Now, we will click on Advanced from the Sort and Filter under the Data.

  • A new window titled Advanced Filter will appear.
  • Now, we will insert $B$4:$D$20 in the List range input box.
  • Then we will enter $F$4:$G$5 in the Criteria range input box.
  • Finally, we will click on OK.

Filter Rows that Does Not Contain Multiple Texts

  • Now, we will see that the Advanced Filter has filtered out the rows that contain Hardware as Category and New York as Address.

Filter Rows that Does Not Contain Multiple Texts

Read More: Apply Advanced Filter Based on Multiple Criteria in One Column in Excel


Quick Notes

🎯 You have to put <> in front of the text that you do not want the rows to contain.

🎯 You also have to insert asterisk (*) symbol before and after the text. Otherwise, the Does Not Contain filter will not work.


Conclusion

In this article, we have learned how to use Excel Advanced Filters does not contain feature. I hope from now on you can use Excel Advanced Filter does not contain feature very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo