Excel not equal

If you're familiar with logical functions in Excel, you've probably used statements to execute different actions based on variable input criteria. In the majority of these scenarios, it's likely that you've used Excel's "=" logical operator to determine whether two values in your formula are equivalent to each other.

But there are some situations in which you may need to figure out whether two values are not equal to one another. This is possible using the  function, but there's an even easier way: use the "does not equal" operator to determine whether two statements are not equal. Read on to find out how.

This tutorial will assume that you know how to use Excel's and boolean functions. If you're not familiar with those, stop by our and tutorials before proceeding.

The "does not equal" operator

Excel's "does not equal" operator is simple: a pair of brackets pointing away from each other, like so: "<>". Whenever Excel sees this symbol in your formulas, it will assess whether the two statements on opposite sides of these brackets are equal to one another. If they are not equal, it will output , and if they are equal, it will output . This is the exact opposite functionality of the equals sign (=), which will output if the values on either side of it are equal and  if they are not.

Let's take a look at the "does not equal" operator in action to see how we can use it in a simple formula:

The above formula outputs , because does not equal . Let's take a look at another simple example using integers:

This formula outputs , because is equal to .

Of course, "<>" doesn't have to be used on numbers. It can also be used on strings of text. Can you tell why the following formulas output the given results?

Hint: For the last example above, you'll have to read up on how the function works if you don't already know it!

Combining <> with IF statements

The "does not equal" operator is useful on its own, but it becomes most powerful when combined with an function. Let's take a look at a practical example to see how this works.

The following example uses the function. If you haven't used statements yet, check out our statement tutorial first.

The spreadsheet above shows a list of SnackWorld's office locations around the country. The company's headquarters is in New York, and all of the other offices are local. A SnackWorld manager wants to add a column to the spreadsheet that dynamically outputs whether a given office is the company headquarters or a local office.

To do so, we could use the following formula:

Note that this formula outputs for all the offices names that do not equal ; but, it outputs when it sees that the office name is equal to .

Note that the above formula could be rewritten as follows, using the equals operator (=) but switching the order of the statement's and arguments:

Is there any advantage to using the "<>" operator instead of the equals sign? Definitely. When you're using statements, you can swap around the order of arguments and generally use either "=" or "<>" in your formulas. But when working with more advanced conditional formulas — in particular, and — you'll likely bump into scenarios in which only "<>" is sufficient (for example, if you want to sum up sales for all offices for which the office name is not "New York").

Other logical operators

If you found this article useful, consider taking a look at our full article on logical operators. We'll teach you how to use the full range of logical operators, including "greater than" and "less than", in your formulas.

Comments

Save an hour of work a day with these 5 Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.

The behavior of the IF function can be easily extended by adding logical functions like AND, and OR, to the logical test. If you want to reverse existing logic, you can use the  NOT function.

In the example shown, we want to "flag" records where the color is NOT red OR green. In other words, we want to check the colors in column B, and take a specific action if the color is any value other than "red" or "green". In D6, the formula were using is this:

In this formula, the logical test is this bit:

Working from the inside out, we first use the OR function to test for "red" or "green":

OR will return TRUE if B6 is "red" or "green", and FALSE if B6 contains any other value.

The NOT function simply reverses this result. Adding NOT means the test will return TRUE if B6 is NOT "red" or "green", and FALSE otherwise.

Since we want to flag items that pass our test, we need to take an action when the result of the test is TRUE. In this case, we do that by adding an "x" to column D. If the test is FALSE,  we simply add an empty string (""). This causes an "x" to appear in column D when the value in column B is either "red" or "green" and nothing to appear if not.*

You can extend the OR function to check additional conditions as needed.

*If we didn't add the empty string when FALSE, the formula would actually display FALSE whenever the color is not red.

Increase price if color NOT red or green

You can extend the formula to perform a calculation instead of just returning a fixed value.

For example, say you want to increase all colors except red and green by 15%. In that case, you could use this formula in column E to calculate a new price:

The test is the same as before, the action to take if TRUE is new. 

If the result is TRUE, we multiply the original price by 1.15 (to increase by 15%). If the result of the test is FALSE, we simply output the original price.

In Excel, the operator for not equal is "<>". For example:

The COUNTIF function counts the number of cells in a range that meet supplied criteria. To use the not equal to operator in COUNTIF, it must be enclosed in double quotes like this:

In example shown, we want to count cells not equal to "complete", so we use "<>complete" for criteria like this:

Note: COUNTIF is not case-sensitive. The word "complete" can appear in any combination of uppercase / lowercase letters.

Not equal to another cell

To use a value in another cell as part of the criteria, use the ampersand (&) character to concatenate like this:

For example, if the value in cell A1 is "100", the criteria will be "<>100" after concatenation, and COUNTIF will count cells not equal to 100:

“Not Equal to” in Excel Formula

Using “Not Equal to” argument in excel is the most important area that must be properly explored as this is not known to most of the people that how we can insert the expression of “Not Equal to” in the logical functions. If “Not Equal to” expression is to be used in case of logical functions then we just simply need to use the expression of “<>” and these two brackets that are posing away from each other will make excel realize that we meant “Not Equal to” and hence we can then make the excel understand that what needs to be done.

How to Use “Not Equal” to Operator in Excel Formula?

Below are the Methods of using Not Equal to Operator.

You can download this Not Equal to Excel Template here – Not Equal to Excel Template

#1 – Using  “Not Equal To” to Test the Numeric Values and Text Values

In the case of testing, the numeric values begin typing with the equal to sign and insert the first cell and then sign of “<>” and then the second cell.

=B3<>A3

This will make excel validate if the value that is in the second cell does not match the value of the second cell.

In this case, Excel will give a result as “TRUE” if the condition is meet or “False” if the condition is not meet. This is the easiest way to check if two cells are not equal to each other. Not equal to in excel formulas will give results in only “True” or “false” format and will not tell us the magnitude.

Drag the Formula to get the rest of the answers.

If we need to know the magnitude, then we must use any of the below Not Equal to in Excel Formulas.

Not equal to for Text values.

#2 – Using “Not Equal To” in Excel IF Formula

In the case of using “Not equal to” in the IF formula in excel, we need to first understand what result we want if the condition meets and what result we need if it fails.

=IF(B2<>”A”,”Don’t call”,”Please Call”)

Now the value of cell B2 is not equal to “A,” then the result will be “Don’t Call.”

If the value of cell B2 is “A,” then we will get “Call” as a result.

By using the IF function, we can make excel displays a different result for a matched condition and also for the unmatched conditions.

#3 – Using “Not Equal to” in Excel CountIF Formula

If we want to count items except for some of the items, use this COUNTIF function with “Not Equal to,”

=COUNTIF($A$2:$A$17,”<>Apples”)

In this case, Excel will calculate the count of all items that are not Apples.

Here we have mentioned that “do not count Apples.”

#4 – Using “Not Equal to” in Excel SumIF Formula

In case we need to sum up items except for some of the items, we can use this SUMIF function in excelThe SUMIF Function is a conditional sum function that calculates the sum of given numbers based on a condition. It only uses one condition, and the syntax for using this function is =SUMIF (Range, Criteria, Sum range).read more with the “Not equal to “formula.

=SUMIF($A$2:$A$17,”<>Apples”,B2:B17)

Here the sum will be taken for all items except for the “Apples.”

Things to Remember

• While using the “Not Equal to” in this should be remembered that the “True” result means that cells are not equal. A “false” result would mean that the values of the cells are equal. The interpretation of True and False as a result is different, as done in the case of using the “Equal to” condition.
• This should be remembered that “A” is not equal to” a” for this function.
• In the case of using “Not Equal to” in the IF function, we can consider “A” as “a,” and both are equal.

Recommended Articles

This has been a guide to Not Equal to Excel Formula. Here we discuss how to use “Not Equal to” Operator in Excel to test the numeric values and Text Values using IF, SUMIF, and COUNTIF along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

Many tasks you perform in Excel involve comparing data in different cells. For this, Microsoft Excel provides six logical operators, which are also called comparison operators. This tutorial aims to help you understand the insight of Excel logical operators and write the most efficient formulas for your data analysis.

Excel logical operators - overview

A logical operator is used in Excel to compare two values. Logical operators are sometimes called Boolean operators because the result of the comparison in any given case can only be either TRUE or FALSE.

Six logical operators are available in Excel. The following table explains what each of them does and illustrates the theory with formula examples.

The screenshot below demonstrates the results returned by Equal to, Not equal to, Greater than and Less than logical operators:

It may seem that the above table covers it all and there's nothing more to talk about. But in fact, each logical operator has its own specificities and knowing them can help you harness the real power of Excel formulas.

Using "Equal to" logical operator in Excel

The Equal to logical operator (=) can be used to compare all data types - numbers, dates, text values, Booleans, as well as the results returned by other Excel formulas. For example:

Example 1. Using the "Equal to" operator with dates

You might be surprised to know that the Equal to logical operator cannot compare dates as easily as numbers. For example, if the cells A1 and A2 contain the date "12/1/2014", the formula will return TRUE exactly as it should.

However, if you try either or you will get FALSE as the result. A bit unexpected, eh?

The point is that Excel stores dates as numbers beginning with 1-Jan-1900, which is stored as 1. The date 12/1/2014 is stored as 41974. In the above formulas, Microsoft Excel interprets "12/1/2014" as a usual text string, and since "12/1/2014" is not equal to 41974, it returns FALSE.

To get the correct result, you must always wrap a date in the DATEVALUE function, like this

The same approach should be applied when you use Excel's equal to operator in the logical test of the IF function. You can find more info as well as a few formula examples in this tutorial: Using Excel IF function with dates.

Example 2. Using the "Equal to" operator with text values

Using Excel's Equal to operator with text values does not require any extra twists. The only thing you should keep in mind is that the Equal to logical operator in Excel is case-insensitive, meaning that case differences are ignored when comparing text values.

For example, if cell A1 contains the word "oranges" and cell B1 contains "Oranges", the formula will return TRUE.

If you want to compare text values taking in to account their case differences, you should use the EXACT function instead of the Equal to operator. The syntax of the EXACT function is as simple as:

Where text 1 and text2 are the values you want to compare. If the values are exactly the same, including case, Excel returns TRUE; otherwise, it returns FALSE. You can also use the EXACT function in IF formulas when you need a case-sensitive comparison of text values, as shown in the below screenshot:

Example 3. Comparing Boolean values and numbers

There is a widespread opinion that in Microsoft Excel the Boolean value of TRUE always equates to 1 and FALSE to 0. However, this is only partially true, and the key word here is "always" or more precisely "not always" : )

When writing an 'equal to' logical expression that compares a Boolean value and a number, you need to specifically point out for Excel that a non-numeric Boolean value should be treated as a number. You can do this by adding the double minus sign in front of a Boolean value or a cell reference, e. g. or .

The 1^st minus sign, which is technically called the unary operator, coerces TRUE/FALSE to -1/0, respectively, and the second unary negates the values turning them into +1 and 0. This will probably be easier to understand looking at the following screenshot:

When using logical operators in complex formulas, you might also need to add the double unary before each logical expression that returns TRUE or FALSE as the result. Here's an example of such a formula: SUMPRODUCT and SUMIFS in Excel.

Using "Not equal to" logical operator in Excel

You use Excel's Not equal to operator (<>) when you want to make sure that a cell's value is not equal to a specified value. The use of the Not equal to operator is very similar to the use of Equal to that we discussed a moment ago.

The results returned by the Not equal to operator are analogous to the results produced by the Excel NOT function that reverses the value of its argument. The following table provides a few formula examples.

Greater than, Less than, Greater than or equal to, Less than or equal to

You use these logical operators in Excel to check how one number compares to another. Microsoft Excel provides 4 comparison operates whose names are self-explanatory:

• Greater than (>)
• Greater than or equal to (>=)
• Less than (<)
• Less than or equal to (<=)

Most often, Excel comparison operators are used with numbers, date and time values. For example:

Using Excel comparison operators with text values

In theory, you can also use the greater than, greater than or equal to operators as well as their less than counterparts with text values. For example, if cell A1 contains "apples" and B1 contains "bananas", guess what the formula will return? Congratulations to those who've staked on FALSE : )

When comparing text values, Microsoft Excel ignores their case and compares the values symbol by symbol, "a" being considered the lowest text value and "z" - the highest text value.

So, when comparing the values of "apples" (A1) and "bananas" (B1), Excel starts with their first letters "a" and "b", respectively, and since "b" is greater than "a", the formula returns FALSE.

If the first letters are the same, then the 2^nd letters are compared, if they happen to be identical too, then Excel gets to the 3^rd, 4^th letters and so on. For example, if A1 contained "apples" and B1 contained "agave", the formula would return TRUE because "p" is greater than "g".

At first sight, the use of comparison operators with text values seems to have very little practical sense, but you never know what you might need in the future, so probably this knowledge will prove helpful to someone.

Common uses of logical operators in Excel

In real work, Excel logical operators are rarely used on their own. Agree, the Boolean values TRUE and FALSE they return, though very true (excuse the pun), are not very meaningful. To get more sensible results, you can use logical operators as part of Excel functions or conditional formatting rules, as demonstrated in the below examples.

1. Using logical operators in arguments of Excel functions

When it comes to logical operators, Excel is very permissive and allows using them in parameters of many functions. One of the most common uses is found in Excel IF function where the comparison operators can help to construct a logical test, and the IF formula will return an appropriate result depending on whether the test evaluates to TRUE or FALSE. For example:

This simple IF formula returns OK if a value in cell A1 is greater than or equal to a value in cell B1, "Not OK" otherwise.

And here's another example:

The formula compares the values in cells A1 and B1, and if A1 is not equal to B1, the sum of values in cells A1:C1 is returned, an empty string otherwise.

Excel logical operators are also widely used in special IF functions such as SUMIF, COUNTIF, AVERAGEIF and their plural counterparts that return a result based on a certain condition or multiple conditions.

You can find a wealth of formula examples in the following tutorials:

2. Using Excel logical operators in mathematical calculations

Of course, Excel functions are very powerful, but you don't always have to use them to achieve the desired result. For example, the results returned by the following two formulas are identical:

IF function:

Formula with logical operators:

I guess the IF formula is easier to interpret, right? It tells Excel to multiply a value in cell B2 by 10 if B2 is greater than C2, otherwise the value in B1 is multiplied by 5.

Now, let's analyze what the 2^nd formula with the greater than and less than or equal to logical operators does. It helps to know that in mathematical calculations Excel does equate the Boolean value TRUE to 1, and FALSE to 0. Keeping this in mind, let's see what each of the logical expressions actually returns.

If a value in cell B2 is greater than a value in C2, then the expression B2>C2 is TRUE, and consequently equal to 1. On the other hand, B2<=C2 is FALSE and equal to 0. So, given that B2>C2, our formula undergoes the following transformation:

Since any number multiplied by zero gives zero, we can cast away the second part of the formula after the plus sign. And because any number multiplied by 1 is that number, our complex formula turns into a simple =B2*10 that returns the product of multiplying B2 by 10, which is exactly what the above IF formula does : )

Obviously, if a value in cell B2 is less than in C2, then the expression B2>C2 evaluates to FALSE (0) and B2<=C2 to TRUE (1), meaning that the reverse of the described above will occur.

3. Logical operators in Excel conditional formatting

Another common use of logical operators is found in Excel Conditional Formatting that lets you quickly highlight the most important information in a spreadsheet.

For example, the following simple rules highlight selected cells or entire rows in your worksheet depending on a value in column A:

Less than (orange):

Greater than (green):

For the detailed-step-by-step instructions and rule examples, please see the following articles:

As you see, the use of logical operators in Excel is intuitive and easy. In the next article, we are going to learn the nuts and bolts of Excel logical functions that allow performing more than one comparison in a formula. Please stay tuned and thank you for reading!

You may also be interested in

Excel Not Equal To (Table of Contents)

Not Equal To in Excel

Not Equal To generally is represented by striking equal sign when the values are not equal to each other. But in Excel, it is represented by greater than and less than operator sign “<>” between the values which we want to compare. If the values are equal, then it used the operator will return as TRUE, else we will get FALSE. We can use the Not Equal operator along with other conditional functions as IF, SUMIF, COUNTIF function to get some other kind of meaning of results.

How to Put ‘ Not Equal To ‘ in Excel?

Not Equal To in Excel is very simple and easy to use. Let’s understand the working of Not Equal To Operator in Excel by some examples.

Example #1 – Using ‘ Not Equal To Excel ‘ Operator

In this example, we are going to see how to use the Not Equal To logical operation <> in excel.

Consider the below example, which has values in both the columns now; we are going to check the Brand MRP of Amazon and Flipkart.

Now we are going to check that Amazon MRP is not equal to Flipkart MRP by following the below steps.

• Apply the formula in excel as shown below.

• So as we can see in the above screenshot, we applied the formula as =B3<>C3 here, we can see that in the  B3 column Amazon MRP is 1400, and Flipkart MRP is 1400, so the MRP matches exactly.
• Excel will check if B3 values are not equal to C3, then it returns TRUE or else it will return FALSE.
• Here in the above screenshot, we can see that Amazon MRP is equal to Flipkart MRP, so we will get the output as FALSE, which is shown in the below screenshot.

• Drag down the formula for the next cell. So the output will be as below:

• We can see that the formula =B4<>C4; in this case, Amazon MRP is not equal to Flipkart MRP. So excel will return the output as TRUE, as shown below.

Example #2 – Using String

In this excel example, we are going to see how not equal to excel operator works in strings. Consider the below example, which shows two different titles named for Amazon and Flipkart.

Here we will check that Amazon’s title name matches the Flipkart title name by following the below steps.

• First, create a new column called Output.

• Apply the formula as =A2<>B2.

• So the above formula will check for A2 title name is not equal to B2 title name if it is not equal, it will return FALSE or else it will return TRUE as we can see that both the title names are the same and it will return the output as FALSE which is shown in the below screenshot.

• Drag down the same formula for the next cell. So the output will be as below:

• As =A3<>B3, where we can see the A3 title is not equal to the B3 title, so we will get the output as TRUE which is shown as the output in the below screenshot.

Example #3 – Using IF Statement

In this excel example, we are going to see how to use the if statement in the Not Equal To operator.

Consider the below example, where we have title names of both Flipkart and Paytm, as shown below.

Now we are going to apply the Not Equal To Excel operator inside the if statement to check both the title names are equal or not equal by following the below steps.

• Create a new column as Output.

• Now apply the if condition statement as follows =IF(A2<>B2, “Title Not Matching”, “Title Matching”)

• Here in the if condition, we used not equal to Operator to check whether the title is equal to or not equal.
• Moreover, we have mentioned in the if condition in double-quotes as “Title Not Matching”, i.e., if it is not equal to it, it will return as “Title Not Matching”, or else it will return “Title Matching”, as shown in the below screenshot.
• As we can see that both the title names are different, and it will return the output as Title Not Matching which is shown in the below screenshot.

• Drag down the same formula for the next cell.

• In this example, we can see that the A3 title is equal to the B3 title; hence we will get the output as “Title Matching “, which is shown as the output in the below screenshot.

Example #4 – Using the COUNTIF Function

In this excel example, we are going to see how the COUNTIF Function works in the Not Equal To operator.

Consider the below example, which shows student’s subject marks along with the grade.

Here we are going to count how many students have taken the marks in it equal to 94 by following the below steps.

• Create a new row named as Marks Not Equal To 50.

• Now apply the COUNTIF formula as =COUNTIF(C2:C7,”<>50″)

• As we can see in the above screenshot, we have applied the COUNTIF function to find out Student marks not equal to 50. We have selected the cells C2:C7, and in the double quotes, we have used <> not equal to Operator and mentioned the number 50.
• The above formula counts the student’s marks which is not equal to 50, and return the output as 5, as shown in the below result.
• In the below screenshot, we can see that marks not equal to 50 are 5, i.e. Five students scored marks more than 50.

• Now we will use a string to check the student’s grade stating how many students are not equal to the grade “Very Good”, which is shown in the below screenshot.

• For this, we can apply the formula as =COUNTIF(D2:D7,”<>”& A9).

• So this COUNTIF function will find the student’s grade from the range we have specified D2:D7 using the Not Equal To Excel OPERATOR. The grade variable “VERY GOOD” has been concatenated by the operator “&” by specifying A9. Which will give us the result of 3, i.e. 3 students grade are not equal to “Very Good” which is shown in the below output.

Things to Remember About Not Equal To in Excel

• In Microsoft excel, logical operators mostly used in conditional formatting, which will give us the perfect result.
• Not Equal To operator always requires at least two values to check either it is “TRUE” or “FALSE”.
• Make sure that you are giving the correct condition statement while using the Not Equal To the operator, or else we will get an invalid result.

Recommended Articles

This has been a guide to Not Equal To in Excel. Here we discuss how to put Not Equal To in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. Formatting Text in Excel
2. Add Rows in Excel Shortcut
3. COUNTIF Excel Function
4. SUMIF Function in Excel

In Excel, <> means not equal to. The <> operator in Excel checks if two values are not equal to each other. Let's take a look at a few examples.

1. The formula in cell C1 below returns TRUE because the text value in cell A1 is not equal to the text value in cell B1.

2. The formula in cell C1 below returns FALSE because the value in cell A1 is equal to the value in cell B1.

3. The IF function below calculates the progress between a start and end value if the end value is not equal to an empty string (two double quotes with nothing in between), else it displays an empty string (see row 5).

Note: visit our page about the IF function for more information about this Excel function.

4. The COUNTIF function below counts the number of cells in the range A1:A5 that are not equal to "red".

Note: visit our page about the COUNTIF function for more information about this Excel function.

5. The COUNTIF function below produces the exact same result. The & operator joins the 'not equal to' operator and the text value in cell C1.

6. The COUNTIFS function below counts the number of cells in the range A1:A5 that are not equal to "red" and not equal to "blue".

Explanation: the COUNTIFS function in Excel counts cells based on two or more criteria. This COUNTIFS function has 2 range/criteria pairs.

7. The AVERAGEIF function below calculates the average of the values in the range A1:A5 that are not equal to 0.

Note: in other words, the AVERAGEIF function above calculates the average excluding zeros.