In Microsoft Excel, there are many logical operators. We basically use these operators to perform a comparison between two or more values. One of them is the “Not Equal To (<>)” operator. In this tutorial, you will learn about the Not Equal To operator in Excel with appropriate examples and proper illustrations. So, read the whole article to enrich your Excel knowledge.
The above screenshot is an overview of the article which represents a simplified application of the Not Equal To operator in Excel. You’ll learn more about the dataset as well as the methods and functions under different criteria in the following sections of this article.
What is ‘Not Equal to’ in Excel?
The Not Equal To is a logical operator that helps compare two values. It is opposite to the Equal To. In simple terms, we use this Not Equal To operator to make sure a cell or value is not the same as another specified value. To express this operator, we use the pair of angle brackets (<>) in Excel. It returns a boolean value TRUE or FALSE.
- TRUE means the two values are not identical or equal.
- FALSE means the two values are the same or equivalent.
How to Use ‘Not Equal to’ Operator in Excel?
Now, the basic syntax of this operator is like the following:
=value1 <> value2
The Values can be anything, it may be text, numerical values or you can use the cell references here.
Look at the following examples:
Here you can see wherever the values are not equal, it returns TRUE. On the other hand, if the values are equivalent, it returns FALSE. So, this is the primary usage of the Not Equal To operator in Excel.
‘Not Equal to’ Operator with Other Functions: 5 Examples
In the following section, we are providing you with five suitable and effective examples that you can implement into your dataset. Make sure you check them all. We recommend you learn and apply all these. It will surely enrich your Excel knowledge.
1. ‘Not Equal to’ in IF Function
Here, we are combining the Not Equal To operator with the IF function in Excel. Now, you can use the Not Equal To operator as a condition in the IF function. Then, it will return the result based on this.
Now, take a look at the following dataset:
Here, we have some students’ names and their grades. Now, our goal is to find the students who failed the exam. If anyone’s grade is not equal to “F”, they are passed.
📌 Steps
- First, type the following formula in Cell D5:
=IF(C5<>"F","Passed","Failed")
- Then, press Enter.
- After that, drag the Fill handle icon over the range of cells D6:D9. It will copy the formula all over these cells.
As you can see, we have successfully used the Not Equal To operator with the IF function in Excel.
Read More: How to Use Comparison Operators in Excel
2. ‘Not Equal To’ with Excel SUMIF Function
In this example, we are combining the Not Equal To operator with the SUMIF function in Excel. The SUMIF function adds the cells specified by a given condition or criteria. Here, we are using the Not Equal To operator as criteria.
Take a look at the following dataset:
Here, you can see some salespersons’ names, their selling products, and the sales amount. Now, our goal is to find total sales without the product TV.
📌 Steps
- First, type the following formula in Cell C12:
=SUMIF(C5:C9,"<>"&C11,D5:D9)
- After that, press Enter.
Here you can see the total sales without the product TV. In the end, we successfully used the Not Equal To operator with the SUMIF function Excel.
3. Excel COUNTIF Function with ‘Not Equal To’ Operator
Now, in this example, we will combine the COUNTIF function with the Not Equal To operator in Excel. The COUNTIF function counts the number of cells within a range that meets the given condition.
Take a look at the following dataset:
Here, we have a dataset of some Fruits. Now, our goal is to count the number of total fruits without including “Apple”.
📌 Steps
- First, type the following formula in Cell D5:
=COUNTIF(B5:B13,"<>"&"Apple")
- After that, press Enter.
As you can see, we have a total of five fruits without “Apple”. In this way, you can use the Not Equal To operator with the COUNTIF function in Excel.
Read More: How to Use Less Than Or Equal to Operator in Excel
4. Combining ‘Not Equal To’ with COUNTIFS Function
This example is similar to the previous one. The COUNTIFS function counts the number of cells in one or more given arrays that maintain one or more specific criteria. As it contains conditions, we can use multiple Not Equal To operators.
Now, take a look at the following dataset:
Here, we have a dataset of some colors. Now, we are going to use the Not Equal To operator to count the number of colors without “Red” and “Blue”.
📌 Steps
- First, type the following formula in Cell D5:
=COUNTIFS(B5:B13,"<>"&"Red",B5:B13,"<>"&"Blue")
- After that, press Enter.
5. AVERAGEIF with ‘Not Equal To’ in Excel
Now, you can also mix it up with the AVERAGEIF function in Excel. The AVERAGEIF function finds the average for the cells specified by a given condition or criteria. We are going to use this with our Not Equal To operator.
Take a look at the following dataset:
In this dataset, we have some data on some persons’ salaries. Now, our goal is to find the average salary of both “Male” and “Female”.
📌 Steps
- First, type the following formula in Cell D12:
=AVERAGEIF(C5:C10,"<>"&"Female",D5:D10)
- After that, press Enter.
- Again, type the following formula in Cell D13:
=AVERAGEIF(C5:C10,"<>"&"Male",D5:D10)
- After that, press Enter.
As you can see, we have calculated the average salary of “Male” and “Female” with the help of the AVERAGEIF function and Not Equal To operator.
💬 Things to Remember
✎ Not Equal To operator requires at least two values to check. It won’t work until you give two values.
✎ Make sure your conditions are correct while using the Not Equal To operator.
✎ Not Equal To operator is case-insensitive. For instance, if you compare “ball” and “BALL” in, it will return FALSE.
Download Practice Workbook
Download this practice workbook.
Not Equal To Operator.xlsx
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the Not Equal To operator in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Keep learning new methods and keep growing!
Further Readings
- Reference Operator in Excel
- How to Perform Greater than and Less than in Excel
- How to Use Greater Than or Equal to Operator in Excel Formula
- What is the Order of Operations in Excel
- Excel Boolean Operators: How to Use Them?
- How to Apply ‘If Greater Than’ Condition In Excel
<< Go Back to Excel Operators | Excel Formulas| Learn Excel
Get FREE Advanced Excel Exercises with Solutions!