Don’t forget to share this post

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.

Google Sheets Not Equal Complete Guide Examples Type Formula
Google Sheets Not Equal: Complete Guide & Examples - Type <> Formula

2. TRUE/FALSE Result

Press ‘Enter’ to see the result. The function returns TRUE because the values are not exactly the same.

Google Sheets Not Equal Complete Guide Examples TRUE or FALSE Result
Google Sheets Not Equal: Complete Guide & Examples - TRUE or FALSE Result

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.

Google Sheets Not Equal Complete Guide Examples with Different Data Types
Google Sheets Not Equal: Complete Guide & Examples - <> with Different Data Types

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.

Google Sheets Not Equal Complete Guide Examples NE Function
Google Sheets Not Equal: Complete Guide & Examples - NE Function

2. Select Values

Type or select the cells containing the two values, separated by a comma.

Google Sheets Not Equal Complete Guide Examples Add Values
Google Sheets Not Equal: Complete Guide & Examples - Add Values

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

Google Sheets Not Equal Complete Guide Examples TRUE or FALSE Result 2
Google Sheets Not Equal: Complete Guide & Examples - TRUE or FALSE Result

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.

Google Sheets Not Equal Complete Guide Examples NE with Different Data Types
Google Sheets Not Equal: Complete Guide & Examples - NE with Different Data Types

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.

How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

Example 1. FILTER

Follow the steps below to use the FILTER function together with the <> sign or the NE function.

  1. 1. In an empty cell, type the FILTER function and select the range of cells you want to filter.
Google Sheets Not Equal Complete Guide Examples FILTER Function Range
Google Sheets Not Equal: Complete Guide & Examples - FILTER Function & Range
  1. 2. In this case, the condition is that the values in column E should not equal “Agent1”.
Google Sheets Not Equal Complete Guide Examples Type Condition
Google Sheets Not Equal: Complete Guide & Examples - Type <> Condition
  1. 3. Press ‘Enter’ to see the result.
Google Sheets Not Equal Complete Guide Examples Filtered Results
Google Sheets Not Equal: Complete Guide & Examples - Filtered Results
  1. 4. You can obtain the same result using the NE function, as shown below.
Google Sheets Not Equal Complete Guide Examples Using NE Function
Google Sheets Not Equal: Complete Guide & Examples - Using NE Function

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. 1. Type the QUERY function in an empty cell and select the range of cells you want to query.
Google Sheets Not Equal Complete Guide Examples QUERY Function Range
Google Sheets Not Equal: Complete Guide & Examples - QUERY Function & Range
  1. 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'".
Google Sheets Not Equal Complete Guide Examples Query Clause with
Google Sheets Not Equal: Complete Guide & Examples - Query Clause with <>
  1. 3. Remember to close the parenthesis and press ‘Enter’ to see the result.
Google Sheets Not Equal Complete Guide Examples QUERY Result
Google Sheets Not Equal: Complete Guide & Examples - QUERY Result
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

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 MORE

Example 3. SUMIF

Follow the instructions below to use the not equal sign with the SUMIF function.

  1. 1. In an empty cell, type the SUMIF function.
Google Sheets Not Equal Complete Guide Examples SUMIF Function
Google Sheets Not Equal: Complete Guide & Examples - SUMIF Function
  1. 2. Select the range that you want to test using the condition of inequality.
Google Sheets Not Equal Complete Guide Examples Select Range to Test
Google Sheets Not Equal: Complete Guide & Examples - Select Range to Test
  1. 3. Type the condition between quotation marks, using single quotes for the text value: "<>'Agent1'".
Google Sheets Not Equal Complete Guide Examples Add Condition
Google Sheets Not Equal: Complete Guide & Examples - Add Condition
  1. 4. After the comma, select the range containing the value you want to sum. Close the parenthesis.
Google Sheets Not Equal Complete Guide Examples Add Sum Range
Google Sheets Not Equal: Complete Guide & Examples - Add Sum Range
  1. 5. Press ‘Enter’ to see the result.
Google Sheets Not Equal Complete Guide Examples SUMIF Result
Google Sheets Not Equal: Complete Guide & Examples - SUMIF 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:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 27 2023, Updated Jun 26 2023