There are many different comparison operators you can use in Google Sheets, and these operators are available for use as signs or as functions. The sign corresponding to inequality is a combination of the smaller than and the greater than signs: <>. You can use this sign and its equivalent in function form - NE - to check that two values are not equal.
These can be individual values, ranges of values, or the result of different calculations. The result will be TRUE if the values are not equal and FALSE if they are equal. Checking for inequality is often useful when using other built-in functions to exclude or include specific values. However, depending on the function you want to use, you may be limited to using the <> sign rather than the NE function.
In this article, you will learn about different methods you can use to check whether two values are not equal to each other: the not equal sign (<>) and the not equal function (NE). You will learn how to use both to check values of different types, including numbers, dates, calculations, and text values.
Finally, you have step-by-step instructions on how to use the not equal operator with three different Google Sheets functions: FILTER, QUERY, and SUMIF.
What Is the Not Equal Comparison Operator in Google Sheets?
The not equal operator takes two main forms.in your formulas, place the not equal sign (<>) between two values, cell ranges, or calculations. Alternatively, you can check using the NE function - NE(value1,value2) - which returns TRUE if values are unequal and FALSE if they’re equal.
Additionally, the not equal operator is also available in tools that allow for conditions or criteria, like conditional formatting or filters. In most cases, you can choose which method to use, but sometimes this depends on the type of function you’re using. For example, you’ll have to use the sign when writing clauses for the QUERY function. Regardless of which method you use, the result will be TRUE or FALSE.
How Do I Check if Two Values are Not Equal in Google Sheets?
Below, you have step-by-step instructions on checking whether two values are not equal in Google Sheets. First, using the <> sign; second, the NE function.
Using the <> sign
Follow the steps below to check inequality between two values using the not equal sign.
1. Type Formula
In an empty cell, type the equal sign to start your formula. Type or select the cell with the first value, and add the <> sign followed by the second value or cell reference.
2. TRUE/FALSE Result
Press ‘Enter’ to see the result. The function returns TRUE because the values are not exactly the same.
Use <> with Different Data Types
The not equal sign works with multiple types of data, not just numbers. In the screenshot below, you have examples using dates, calculations, and text values. When used with text values, the not equal sign is not case sensitive.
Using the NE Function
Follow the steps below to check inequality between two values using the not equal function.
1. Add Function
In an empty cell, type the NE function.
2. Select Values
Type or select the cells containing the two values, separated by a comma.
3. TRUE/FALSE Result
Press ‘Enter’ to see the result. The result is the same whether you use the not equal sign or the not equal function
Use NE with Different Data Types
Just like the not equal sign, the NE function works with multiple types of data, not just numbers. In the screenshot below, you have examples using dates, calculations, and text values.
Examples of How to Use Not Equal in Google Sheets
The examples below show how you can use the not equal sign and the not equal function in combination with other built-in functions. The first example is with the FILTER function, which works equally well with the <> sign and the NE function. The second example uses the QUERY function, so I will use the <> sign within the query clause, and the third example uses the SUMIF function.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MOREExample 1. FILTER
Follow the steps below to use the FILTER function together with the <> sign or the NE function.
- 1. In an empty cell, type the FILTER function and select the range of cells you want to filter.
- 2. In this case, the condition is that the values in column E should not equal “Agent1”.
- 3. Press ‘Enter’ to see the result.
- 4. You can obtain the same result using the NE function, as shown below.
Example 2. QUERY
In the example below, I will use the QUERY function with a clause that checks that cell values are not equal to a specific string and returns the count of those that pass the test. Since you have to write the condition as a valid query clause, use the <> sign instead of the NE function.
- 1. Type the QUERY function in an empty cell and select the range of cells you want to query.
- 2. After the comma, open quotation marks to write the query clause. In this example, select the count of cells in column E where the cell does not equal “Agent1”: "select count(E) where E<>'Agent1'".
- 3. Remember to close the parenthesis and press ‘Enter’ to see the result.
If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet
READ MOREExample 3. SUMIF
Follow the instructions below to use the not equal sign with the SUMIF function.
- 1. In an empty cell, type the SUMIF function.
- 2. Select the range that you want to test using the condition of inequality.
- 3. Type the condition between quotation marks, using single quotes for the text value: "<>'Agent1'".
- 4. After the comma, select the range containing the value you want to sum. Close the parenthesis.
- 5. Press ‘Enter’ to see the result.
Conclusion
As you have seen, there are different ways to check for inequality in Google Sheets. You now know how to use the <> sign and the NE function to check that values, ranges, or calculations are not equal. You also know that you can use either method to check data of different types, including numbers, dates, the result of formulas, or text values. Both the not equal sign and the not equal function will return TRUE if the values are not equal and FALSE if they are.
Finally, you know how to use both methods in combination with other built-in functions in Google Sheets. You have step-by-step instructions on how to use the not equal sign and NE function with the FILTER function, how to write query clauses to check for inequality using the QUERY function, and how to use the <> sign with SUMIF.
To learn more about the functions used above, check out these guides on: