Table of Contents
In a Google Sheets query, the “not equal” operator is used to filter out rows that are not equal to a specific value. This can be done by using the “<>” operator in the query, followed by a literal value or cell reference. For example, “Select * from Sheet1 where Column1 <> ‘Value'” would return all rows from Sheet1 where Column1 does not have the literal value “Value”.
You can use the following methods to use a “not equal” operator in a Google Sheets query:
Method 1: Not Equal to One Specific Value
=query(A1:C11, "select * where A != 'Value1'")
Method 2: Not Equal to One of Several Values
=query(A1:C11, "select * where A != 'Value1' and B != 'Value2'")
Note that != is the “not equal” operator in Google Sheets.
The following examples show how to use each formula in practice with the following dataset in Google Sheets:
Example 1: Query Rows Not Equal to One Specific Value
We can use the following formula to select all rows where the Position column is not equal to ‘Guard’:
=query(A1:C11, "select * where A != 'Guard'")
The following screenshot shows how to use this formula in practice:
Notice that only the rows where the Position is not equal to ‘Guard’ are returned.
Example 2: Query Rows Not Equal to One of Several Values
We can use the following formula to select all rows where the Position column is not equal to ‘Guard’ and the Team column is not equal to ‘Warriors’:
=query(A1:C11, "select * where A != 'Guard' and B != 'Warriors'")
Notice that only the rows where the Position is not equal to ‘Guard’ and the Team is not equal to ‘Warriors’ are returned.
Note: The query() function is case-sensitive. This means that if you type A != ‘guard’ then the query will still return rows where the Position is Guard because the two values don’t have the same case.
The following tutorials explain how to perform other common tasks with Google Sheets queries: