As you probably know, the equal sign (=) in Excel is used to designate that two numbers are equivalent. But what if they’re not? What if one of them is larger than the other? In this blog post, we’ll explore how to use an “not equal” operation in Excel and discuss some examples of when it’s appropriate to use.
The Not Equal To operator is very useful for finding values that are not the same as the one you input. This can be handy in many situations such as if you want to find all of the cells where your data is different from a certain value, or if you need to know which items were sold but not returned. In this blog post we will cover 11 ways that Not Equal To can be used in Excel!
In this chapter, we will learn how you can use not equal to operator i.e. <> in your daily excel life with various examples below. As the name suggests, Not equal to in excel is if you want to find out if a particular cell is equal or not equal to other selected cells and it returns boolean values i.e. TRUE or FALSE.
Previously In Excel:- Count Characters In Cells in 4 Simple Ways
In this chapter, we will use various logical functions like IF Function, COUNTIF Function, and AVERAGEIF Function with not equal to an operator to cover basic to advance understanding of the same. you can download the practice sheet using the below google drive link;
Here we have the sample worksheet to work with as follows;
Example 1:- Not Equal to Basics
In the basics, we will cover the example for beginners, therefore in above example, we want to know if student’s name of class 1 is not equal to that of class 2, to conclude that in cell K3 , we will input the basic expression of not equal to;
here we get the answer and apply to all values;
Example 2:- Not Equal to In Excel Using IF Function
As you can see it returns boolean values i.e. TRUE or FALSE. Now, if you want to wrap these values in your custom format i.e. Blank in place of TRUE Value and “Error” in place of FALSE, Then you can use the IF function like below;
=IF(A3<>F3, “”, “ERROR”)
Now if we apply the same to all, we can see there are two values with ERROR and we need to fix it in order to proceed;
Now as we can see there is a blank cell value in class 2 with student’s name in maths as below;
Now suppose if you want to compare maths score of class 2 with class 1 with the help of simple subtraction to find the difference between the both, but it will be unfair for lobe because he wasnt present in the maths exam. therefore if we just find the difference first this is what we get ;
Kate got 65 in maths and lobe was absent hence the difference of -65. Now to make sure when we apply our formula to the whole range, we want excel to ignore the blank values and move forward with the rest, Therefore, to do the same we need if function to wrap this up.
Therefore, we will apply if criteria as below;
Explanation of above formula will be
logical_test = G3<>””, If Cell G3 is not equal to blank i.e. “”, then;
value_if_true = G3-B3, Subtract the marks of class 2 with class 1, otherwise;
value_if_false = “”, Leave it blank.
Hope, you get the point how we can use the IF function in Not Equal to calculations.
Example 3:- Not Equal to In Excel Using COUNTIF Function
I have a question for you. Is there a difference between “not equal to” and “less than”? No, right? I’m glad you agree. Now what about in Excel? If we want to count only records that are not equal to some value, can we use the COUNTIF function or do we need something else? Let’s find out!
Now in the sample example sheet, we need to count all student’s name in class 2 except Lena. Therefore, we will use the following formula;
Here we have selected range i.e. F3 to F8 which are the students name and in criteria, we want to count all except Lena, than we have the expression “<>Lena”.
Therefore, we got the number 5 as there are 5 numbers of students there excluding lena.
Alternatively, if you want to specify cell number not the value then we need to add “&” in between not equal to operator and cell number of Lena i.e. F7 as follows;
Example 4:- Not Equal to In Excel Using COUNTIFs Function
Similarly to COUNTIF, if you have multiple criterias then you can use COUNTIFs function for the same. Taking same example as above, in this example, we will count all values of class 1 and class 2 excluding “Mathews” using the COUNTIFs function.
Therefore, our formula will be ;
=COUNTIFS(F3:F8, “<>”&F4, A3:A8, “<>”&A4);
Range1= F3:F8, Range of criteria 1 which class 2 from F3 to F8;
Criteria1= “<>”&F4, Value should not be equal to F4 i.e. Mathews;
Range2=A3:A7, Range of criteria 2 which class 1 from A3 to A8;
Criteria2=”<>”&A4, Value should not be equal to A4 i.e. Mathews;
Here we get the same answer i.e. 5 as there are 5 students in each class excluding Mathews.
You must be wondering why answer is not 10? since 5 in each class i.e. 2 should be equal to 10, its because COUNTIFs function will return the collective value from each range selected, but if you want to add all the counts you can use COUNTIF again with “+” operator as below;
And here we get the answer i.e. 10
Example 5:- Not Equal to In Excel Using AVERAGEIF Function
AVERAGEIF function is a formula that will calculate the average of all cells in a range, but only if those cells meet certain conditions. It’s used to compare two things and see which one is larger. In this example, we will use AVERAGEIF with not equal to operator.
Here we want to find out average of class 2 students excluding those who were absent in the respective subjects which has the blank value above i.e. Green, Lobe and Joe.
Therefore, we will use AVERAGEIF function in formula as below;
Range = G3,I13, Range for which you want average;
Criteria = “<>”””, Criteria should ne not equal to blanks;
Here as you can see above, excel has ignored the blank value i.e. H3 and give us the average of two cells i.e. 45 and 54 divided by 2 only not 3.
Now drag and apply to all ;
Hope you get to know how to use not equal to operator with different kinds of logical functions.