367

Excel not equal to operator

You have performed several basic and advanced operations on Excel data, such as add, subtract to manipulate and analyze the Excel data. Comparison is one more operation that can be performed on Excel data. You can compare two or more values stored in an Excel sheet with each other.

Comparing data is an important and useful operation of Excel, for which Excel provides six logical operators. Not Equal to operator is one of them. It is represented by the <> symbol. In this chapter, we will brief Not Equal to operator (<>) to see how and where we can use it.

We will also provide a brief summary of these six logical operators at the end of this chapter.

Not Equal to operator

Not Equal (<>) is a logical operator. Excel users can use this operator to compare two or more values and determine whether they are the same or different.

If the compared values are not same, it returns TRUE. If the compared values are same, it will return FALSE. The resultant value will be one at a time: either TRUE or FALSE depending on the comparison result.

Use this operator between two conditions (value) by placing an equal sign in the beginning to start the Excel formula. E.g., =”Hello”<>”World”

Note: To write a formula in Excel, you must put = (equal sign) to start the formula.

This operator becomes more interesting when combined with other functions like IF and OR to achieve certain results.

Not Equal formula

Not Equal operator is used between two conditions (values) or cell reference containing data. You can use it as –

Syntax

We will try to explain this operator with the help of several examples by comparing different values. Thus, it will help you to learn it better.

Return value

This operator returns TRUE if the compared values are not the same. On finding the values same, it will return FALSE.

Note: It returns just opposition result to Equal (=) operator.

When this operator is used?

You can use these values when you want to make sure two specific values are not equal to each other.

Additionally, you can also use it inside other functions to make the result more specific. Not Equal to operator is just opposite to Equal to operator.

Examples

We will show you different examples to describe the usage and working of the Not Equal To (<>) operator. We will take different examples for number, text, and string data comparison.

Look at our first example below:

Example 1: Compare numbers

We will take an example to compare numeric values to discuss the Not Equal To operator in an Excel worksheet.

When numbers are not same – returns TRUE

1. We have two numeric values (two different numbers) stored in Excel cells on which we will we “not equal to” comparison.
   
2. Write the following comparison formula with <> operator in a cell (where you place the result) and compare the values stored in A2 and B2 cells.
   =A2<>B2
   
3. Hit the Enter key of your keyboard and get the result of either both values are the same or not.
   
   It has returned TRUE because both values are not the same.
   When numbers are same – returns FALSE
4. We have one more numeric data (two same numbers) stored in an Excel worksheet for comparison using “not equal to” operator.
   
   This time both values are same.
5. Write the following comparison formula with <> operator in a cell (where you place the result) and compare the values stored in A2 and B2 cells.
   =A2<>B2
   
   “Formula for comparison will always be same except the cell reference (reference of the cell that contain data).”
6. Hit the Enter key of your keyboard and get the result whether values are same or not.
   
   You have seen that – this time, Excel has returned FALSE because both values are equal.

Example 2: Compare Strings

Now, we have another example to compare the string type of data stored in an Excel sheet. We will take a string comparison example to compare the text data to explore the working of the Not Equal To operator in an Excel worksheet.

When strings are not same – returns TRUE

1. We have string type of data (two different strings) stored in Excel cells on which we will we “not equal to” comparison.
   
2. Write the formula to compare the string values stored inside A6 and B6 cells in an Excel sheet.
   =A6<>B6
   

Note: You can also provide the string data directly in this formula inside double quotes like (=”Hello”<>”Hiii”). It will work the same.

3. It will return TRUE as both string values are different. See the result by hitting the Enter key.
   
   When strings are same – returns FALSE
   We have two same strings stored in A7 and B7 cells to be compared with each other using “not equal to” operator.
4. Now, write the following formula to string comparison with new cell reference where data is stored.
   =A7<>B7
   
5. You will get the comparison result by hitting the Enter key, i.e., FALSE, as both strings are different.
   

Note: Not Equal to (<>) operator is not case-sensitive when used with text data. So, if two same text string in different CASES is compared using not equal to operator, it will return FALSE.

For example, Hello and HELLO both strings are same in spellings, but CASE is different. Excel Not Equal to (<>) operator ignore the case and treat them same, and return FALSE as its result.

More on String data

Let’s see some more operations on string data.

Formula (=condition1<> condition2)	Result	Summary
=”Hello”<>”Hii”	TRUE	Both text strings are different. So, it returned TRUE.
=”Hello”<>”Hello”	FALSE	Both text strings are the same. So, it returned FALSE.
=”BYE”<>”bye”	FALSE	It ignored the case and treated both strings same. So, it returned TRUE.
=”HELLO”<>”HELLO”	FALSE	It also returned FALSE because both strings are completely same.

You can also better learn by yourself by inputting different values in the comparison formula. You do not need to provide numeric values in double quotes to compare them as a string. You can put them directly, e.g., 23<>15.

USE <> operation inside a function

Now, you will see that we will use <> inside another function rather using them directly to compare Excel data. For example, inside IF function.

Example

We have some numeric data (marks of five subjects) stored in a column. We will use AVERAGEIF() function and <> operator inside it to compare the average of the values by excluding a specific number.

1. Write the following formula for AVERAGEIF() with <> operator.
   =AVERAGEIF(B2:B6, “<>42”)
   
2. Now, see the calculated average result with comparison operator that how it works with normal AVERAGE() function.
   
   It has been calculated the average of values that is not equal to 42. So, according to this, above calculated result is an average of Math (38), Chemistry (45), and Hindi (37).
   This formula goes something like this way: (38+45+37)/3 = 40
3. If you simply calculate the average of the same marks, you will see the result will be different, which is different than the above result.
   

Logical operators

As we told you, Excel provides six logical operators to compare the data stored in an Excel sheet. These logical operators are equal, not equal, greater than, less than, greater than equal to, less than equal to.

Operator Name	Operator	Description	Formula (For TRUE)
Equal to	=	Compares two values are equal, if True	=A2=B2 (=51=51)
Not Equal to	<>	Determines two values are not equal	=A2<>B2 (=48<>51)
Greater than	>	Compare one value is greater to another value.	=A2>B2 (=13>17)
Less than	<	Compare one value is less than from another value.	=A2
Greater than equal to	>=	Compare one value is greater than equal to another value.	=A2>=B2 (=7>=4)
Less than equal to	<=	Compare one value is less than or equal to another value.	=A2<=B2 (=16<=16)

All the operators return TRUE if the condition satisfies for the given values. Otherwise, they return FALSE.

You can learn in more detail about these operators in our Excel logical operator chapter. Learn from here, it will contain two or more examples for each operator.

---