Filter Entries if it Does Not Contain Value in Google Sheets (2 Easy Ways)

The most common and default approach to filtering is to look for a certain value in a data range. But what if we wanted to filter entries if it does not contain a value in Google Sheets?

That is easy enough to do although the methods may not seem that obvious. And of course, it can be done using both the FILTER function and the default filter of Google Sheets.

The gist of it is to take the “contains value” formula and generate something of the opposite logic.

Learn More: Google Sheets: Filter Data if it Contains Value (A Comprehensive Guide)

It is also good to know that these methods only work for text values, as any other kind can be easily filtered by using the default options.

Let’s see how it’s done.

2 Ways to Filter Entries if it Does Not Contain Value in Google Sheets

1. Using the FILTER function for the “Does not Contain” Filter in Google Sheets

Using the FILTER function is the simplest approach we can show how to filter in Google Sheets and its customizability.

FILTER(range, condition1, [condition2, ...])

filter function syntax for does not contain condition in google sheets

And as for matching text values as a condition for the FILTER function, we will use regular expressions through the use of the REGEXMATCH function.

REGEXMATCH(text, regular_expression)

Consider the following worksheet for example:

sample worksheet to apply a filter to

We want to filter all entries that do not contain Bricks from the data range.

Step 1: Open the FILTER function and input the data range. The data range must include the condition column, which in this case is Product.

selecting the data range for the FILTER function

Step 2: Input the condition. We are looking for the text “Bricks” in the data range which is in column A. So, using the REGEXMATCH function the formula will be:


However, this is for the direct match for Bricks. For the “does not contain” condition for this text, we must set this REGEXMATCH condition to FALSE.


applying the does not contain condition for the filter function in google sheets

Step 3: Close parentheses and press ENTER.


filter entries for filter does not contain condition in google sheets using the filter function

Alternatively, you can also use a cell reference to dynamically point to the text condition for the filter. Just make sure to lock this reference with absolutes ($).


using cell reference to set the filter condition

For Partial Text Matches

A great part about using the FILTER and REGEXMATCH combination is that we can also filter for partial text matches, and by extension, “does not contain” filter conditions in Google Sheets.

For example, in the following worksheet, we want to filter all students that do not have the string “Jean” in them.

sample worksheet with student names and respective scores

The formula is similar to the one we have seen before:


filter entries that does not contain jean partial text in google sheets

Note that even though this formula works great with partial string filters, the match condition is still case-sensitive.

For Multiple Text Conditions

If you recall the syntax of the FILTER function, you will realize that we have multiple condition fields in the function. This means that we can filter for multiple “does not contain value” conditions using the FILTER function in Google Sheets.

For example, let’s say we want to filter all entries except Bricks and other Products that have a Not-Delivered Status:

sample worksheet to filter for multiple conditions

The formula for the first condition is:


The formula for the second condition is:


Whole FILTER formula:


filter multiple does not contain value in google sheets in the and logic

What we’ve just done is filter for multiple criteria using the AND logic and also for multiple column conditions in Google Sheets.

The OR logical filter is also possible, and so too for values in a single column. However, unlike AND, OR logic can be only applied in a single condition field of the FILTER function and by taking advantage of regular expressions.

In this case, let us filter for entries except for Bricks and Planks. The formula will be:


Read More: Filter with the AND Condition in Google Sheets (An Easy Guide)

Similar Readings

2. Using the Default Filter Feature of Google Sheets for the “Does not Contain” Filter

Next, we will look at how to apply the “does not contain value” filter in the default filter of Google Sheets.

Step 1: Apply a filter over the source data. Simply select a cell of the table and click on the filter icon.

applying a filter to a data range

Step 2: Navigate to the Filter by condition section and open the drop-down menu. Here, you’ll find the “Text does not contain” option. Select it.

the text does not contain option in the filter menu

Step 3: Input the string that you don’t want the table to have.

inputting the text condition

Step 4: Click OK to filter the entries that do not contain the given text in Google Sheets.

using the default filter for the des not contain condition in google sheets

While this method does give us the results that we want, filtering in this way comes with a few limitations. The most important of which is that we cannot input multiple filter conditions for a single column for the filter in this way.

For that, we have to resort to applying custom formulas to the filter.

For Multiple Text Conditions in a Single Column

As mentioned before, we can opt to filter for multiple conditions in the default filter of Google Sheets by using a custom formula.

We will once again take advantage of the REGEXMATCH function for this.

Step 1: Navigate to the “Custom formula is” option from the Filter by condition section in the filter menu.

Filter > Filter by condition > Custom formula is

Step 2: Enter the following formula:


applying a custom formula in the filter menu of google sheets

Step 3: Click OK to apply the filter.

filter for multiple does not contain conditions in google sheets using custom formula

Read More: Filter Values that Contains Multiple Text Criteria in Google Sheets (2 Easy Ways)

Final Words

That concludes the two approaches that you can use to filter entries if it does not contain a specific text value in Google Sheets. For a more customizable result for either the same or a different worksheet, we suggest the FILTER function method.

Feel free to leave any queries or advice you might have in the comments section below.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply