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'")
The following screenshot shows how to use this formula in practice:
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.
Additional Resources
The following tutorials explain how to perform other common tasks with Google Sheets queries:
Google Sheets Query: How to Query From Another Sheet
Google Sheets Query: Select Rows that Contain String
Google Sheets Query: How to Use Group By
Google Sheets Query: How to Use Order By