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.
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”.
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.
Press Enter to implement the function with absolute reference.
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.
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”.
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”.
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 “”.
And thus we end up with the sum of the cells in column B whose neighboring cells aren’t empty.
Related articles
IF AND in Excel: Everything you need to know
If you want to evaluate your data with regard to multiple criteria, the combination of the Excel functions IF and AND may be of use to you. Based on whether the conditions you defined are met or not, Excel will return a result of your choosing. Keep reading to find out how and when to use Excel’s IF AND formula.
How to count characters in Excel using the LEN function: step-by-step instructions
In Excel, it can be useful to count the number of characters in each cell if the big picture gets lost in large amounts of data. To quickly and easily get an overview of the number of characters, you can use the Excel LEN function. In our step-by-step instructions, you will learn how to use this function to count characters in Excel.
ChinnapongShutterstock Excel MOD – calculating remaining amounts using the MOD function in Excel
The MOD function in Excel helps to calculate the remainder from a division operation and is useful when you need to manage production data, for example. This handy function makes everyday office tasks much easier for a diverse range of situations. Explore professional tips and examples on how to use Excel’s MOD function.