Excel: The “not equal to” operator explained

“Not equal to” is one of the logical operators available in Excel, which allow you to compare cells and analyze large amounts of data. The operator is formed with two angled brackets pointing away from each other: <>. Keep reading to learn how to get the most out of “not equal to” in Excel.

Contents

What is “not equal to” used for in Excel?
“Not equal to” in Excel – a syntax lesson
Using the Excel “not equal to” operator in complex functions

What is “not equal to” used for in Excel?

In its simplest application, the “not equal to” operator determines whether or not the values in two cells are equal. The resulting output will either be TRUE or FALSE. However, the “does not equal” operator is rarely used on its own. The operator mainly becomes interesting when combined with functions like IF and OR to dictate what should happen when certain conditions aren’t met.

“Not equal to” in Excel – a syntax lesson

The simplest use of the “not equal to” sign is in a function made up of two conditions and the “not equal to” operator:

=(Condition1<>Condition2)

To illustrate this, let’s use cell A1 with the value “2” for Condition1 and cell B1 with the value “3” for Condition2. We can then use the “not equal to” operator to ask whether A1 is not equal to B1. The result is “TRUE”.

Example in Excel - “does not equal” sign — Comparing the values of two cells with the “not equal to” operator <>

You can also use “does not equal” with more than two cells, columns or values. Simply add another “does not equal” sign and another condition:

=(Condition1<>Condition2<>Condition3)

If you want to implement a “not equal to” comparison for several cells or columns, just click on the fill handle (the green square) and pull it down to the rows you want to compare.

Extending the “not equal to” function to other cells

Press Enter to implement the function with absolute reference.

Result of comparison with “not equal to” function — The cells will be filled with the “not equal to” function

Using the Excel “not equal to” operator in complex functions

Now that we’ve been over the basic use of the “does not equal” operator, let’s take a look at how to effectively embed it in other functions. The values that the Excel “not equal to” operator returns can help in building IF, OR, or NOT functions.

Example 1: “Not equal to” + IF

The “not equal to” sign is particularly useful when combined with the IF function. The IF function asks whether certain conditions are fulfilled and in case that they are, initiates a certain result. When implemented with IF, the “not equal to” sign is very similar to the “equals” sign.

First, let’s take a look at how the “equals” operator is used with IF. Combined with the IF function, it could be used to check who the winner of a raffle is by comparing the winning number to the number each person drew.

=IF(B3=104,”Win”,”Lose")

According to this function, if the raffle ticket number in cell B3 is equal to the winning number “104”, then the output value will be “Win”. If it is not equal to “104”, the output value will be “Lose”. You can then use the green fill handle to apply the function to as many cells as you need.

IF function with EQUALS and self-defined output values — The IF function combined with the EQUALS function

With the “does not equal” operator, the function will be almost exactly the same. Simply replace the EQUALS sign with the “does not equal” sign, and change the positions of “Win” and “Lose”:

=IF(B3<>104, "Lose","Win")

This function says that if the raffle ticket number in cell B3 is not equal to the winning number “104”, then the output value will be “Lose”. If it is equal to “104”, the output value will be “Win”.

IF function with “not equal to” and self-defined output values — The IF function combined with “not equal to”

As you can see, you can get similar results with the EQUALS and “not equal to” signs. Thus you can choose whichever operator is better suited to your purposes.

Example 2: “Not equal to” + IF + AND

Once you start combining logical functions and operators, the possibilities are near endless. For example, add the AND function into the mix to make the conditions of the IF function more precise.

Let’s take a look at the syntax for this using the raffle example again.

=IF(AND(A1<>1,B1<>0,C1<>4),"LOSE","WIN")

In this case, all three of the listed numbers have to be a match in order to win. If all three of the numbers are not equal to the winning numbers, then the output is “Lose”. Otherwise it’s “Win”.

Excel: “Not equal to” sign used with IF and AND — “Not equal to” used with IF and AND

Tip

The OR operator works similarly to AND. Try IF and OR combination for another way to make your functions more precise.

Example 3: „Not equal to“ + „SUMIF“ function

The “not equal to” sign is also useful in combination with the mathematical excel function SUMIF. SUMIF returns the sum of all the numbers that fulfill a certain condition. In the following example, SUMIF is used to add the values from cells whose adjacent cells are not empty (whose value is not equal to empty “”).

=SUMIF(C3:C6,"<>"&"",B3:B6)

The function here will look at the values in cells C3-C6 with regard to the criterion “not equal to empty”. The function then adds the values from the cells B3-B6 for the rows that fulfill this criterion (below, rows 3 and 5). The “not equal to” sign must be put in quotations here, and then needs the & sign to combine it with the sign for empty “”.