There are two simple and one complex comparison operators to get the not equal to in the Google Sheets Query function.

You can use any of them to filter columns.

The use of not equal to in Query depends on the content type of the column.

The usage is for the content types text, numbers, and dates.

As I have mentioned above, there are two simple comparison operators that you can use to get the not equal to in Query in Google Sheets. They are <> and !=.

Other than these two, there is one complex string comparison operator that is none other than the Matches regular expression match.

Not Equal to in Query Function in Google Sheets

I have the following sample data for testing the above said all different not equal to comparison operators in Query.

The data contains text, numeric and date columns. So, we can test the operators in these three columns individually.

The type of data in columns A, B, and C are text, number, and date, respectively.

Let’s see how to use not equal to in Query in Google Sheets. Here are the formulas containing the different operators.

1. Using != Operator in Query Function

I am filtering column A that doesn’t match “AB10025YX 2”. This value, i.e., criterion, I have in cell E1.

Formula # 1 (!= in text column):

=QUERY(A1:C,"Select A,B,C WHERE A !='"&E1&"' and A is not null")

Below, I’ve hardcoded the same criterion within the formula.

=QUERY(A1:C,"Select A,B,C WHERE A !='AB10025YX 2' and A is not null")

Formula # 2 (!= in Number/Numeric column):

Here let me show you how to filter column B if the values in column B are not equal to #1.

Use this Query formula when the criterion is in cell E1.

=QUERY(A1:C,"Select A,B,C WHERE B !="&E1&" and A is not null")

Similar to the above example, we can hard code the number criterion within the Query formula with the not equal to operator.

=QUERY(A1:C,"Select A,B,C WHERE B !=1 and A is not null")

Formula # 3 (!= in date column):

I always find the use of the date criterion in Query quite confusing.

So, I have written a detailed tutorial separately on this. Please check that in your leisure time here – Date Criteria in Query Function.

Why it’s the hot topic?

The reason you can’t use the date as it’s in Query. Even if you use it, sometimes the formula may return incorrect results.

The date criterion should be converted to a string in a specific format to use in Query. You can use the long-winded approach for this conversion.

Let’s back to our topic, not equal operator use in Google Sheets Query.

Here is the != comparison operator in date column filtering.

=QUERY(A1:C,"Select A,B,C WHERE C !=date '"&TEXT(E1,"yyyy-mm-dd")&"' and A is not null")

What about the criterion within the formula?

=QUERY(A1:C,"Select A,B,C WHERE C !=date'2018-01-25' and A is not null")

2. Using the <> Operator in Query Function

You can use either of the operators <> or != for not equal to operation in Query.

So I am not going to repeat how to use the <> operator in Query. It’s the same as above.

3. Matches Regular Expression for Not Equal to in Google Sheets Query

How to use Not Matches regular expression to negate criteria in the filter part in Query?

Formula # 1 (Not Matches in text column):

Here is an example of how to not equal to in Query in Google Sheets using Matches in a text column.

=QUERY(A1:C,"Select A,B,C WHERE not A matches '"&E1&"' and A is not null")

The formula when using the hardcoded text criterion.

=QUERY(A1:C,"Select A,B,C WHERE not A matches 'AB10025YX 3' and A is not null")

Formula # 2 (Not Matches in Number column):

Cell E1 contains the number condition.

=QUERY(A1:C,"Select A,B,C WHERE not B matches "&E1&" and A is not null")

Here, the number condition is within the formula.

=QUERY(A1:C,"Select A,B,C WHERE not B matches 1 and A is not null")

Formula # 3 (Not Matches in Date column):

The use of not equal to in Query in Google Sheets in a date Column.

Remember, the date criterion is in cell E1.

=QUERY(A1:C,"Select A,B,C WHERE not C matches date '"&TEXT(E1,"yyyy-mm-dd")&"' and A is not null")

Here, the condition is within the formula.

=QUERY(A1:C,"Select A,B,C WHERE Not C matches date'2018-01-25' and A is not null")

Conclusion

In the last part, i.e., with Matches, I’ve used the NOT logical operator in Query.

We can use that with != and <> operators with one modification.

You may place the NOT logical operator before the criteria column similar to Matches and replace != or <> with =.

I have skipped that to avoid confusion.