Microsoft Excel provides Comparison Operators to compare the values of two operands. You can give the values directly or you can give the Cell references when you use these operators in Excel formulas.
Comparison Operators
Excel’s comparison operators are useful to compare the values in two operands. You can compare two values or the values in cell references. After the comparison, if there is no ERROR, always these operators return the logical value TRUE or FALSE.
Unlike Arithmetic Operators, you can not apply these operators on multiple operands; if you do so, it always returns the negation of the actual result. If you want to compare multiple values at a time, then you must use Logical Functions. I will try to explain them in another article.
Equality Operators
Equality operators are useful to verify the equality of the operands. Excel provides “= Operator – Equal sign” and “<> Operator – Not Equal sign” to check the equality of the operands. The equality operator verifies whether two values are equal; whereas the Not equality operator verifies whether two values are NOT equal. When dealing with these operators; you need to remember the below:
- When comparing strings or text, it is always considered case-insensitive. That means Upper and Lowercase letters are considered as same. For example, the texts “APPLE” and “apple” are the same.
- Unlike Arithmetic operators, we can use Comparison Operators on any type of data.
- Data can not be converted to numbers when comparing text values. For example, “65” = 65 always returns FALSE; because “65” is the string and 65 is the numeric data type.
- When you apply these operators on multiple values; always returns the negation of the result. For example, =5=5=5 always returns the negation of TRUE which is FALSE. In the same way, =5<>5<>5 always returns, the negation of FALSE which is TRUE. It evaluates like this; from =5=5=5; first, it verifies, =5=5 which returns TRUE then, =TRUE=5 which returns FALSE.
- Also “TRUE” is a string or text; which is different than the Boolean value TRUE.
See the below table for more examples:
| Comparison Operators | ||||
| Data | Operator | Example | Cell Reference | Result |
| 5 | equal sign (=) | =5=5 | =A3=A4 | TRUE |
| 5 | =5=6.5 | =A3=A6 | FALSE | |
| 65 | =”A”=65 | =A10=A9 | FALSE | |
| 6.5 | =”65″=65 | =A9=A5 | FALSE | |
| “APPLE” | =”APPLE”=”apple” | =A7=A8 | TRUE | |
| “apple” | =”A”=”B” | =A10=A11 | FALSE | |
| “65” | =”10/9/2018″=”10/10/2018″ | =A12=A13 | FALSE | |
| “A” | =TRUE=FALSE | =A14=A15 | FALSE | |
| “B” | =5=5=5 | =A3=A4=A3 | FALSE | |
| 43382 | =”TRUE”=TRUE | =A16=A14 | FALSE | |
| 43383 | ||||
| =TRUE | ||||
| =FALSE | ||||
| =”TRUE” | not equal sign (<>) | =5<>5 | =A3<>A4 | FALSE |
| =5<>6.5 | =A3<>A6 | TRUE | ||
| =”A”<>65 | =A10<>A9 | TRUE | ||
| =”65″<>65 | =A9<>A5 | TRUE | ||
| =”APPLE”<>”apple” | =A7<>A8 | FALSE | ||
| =”A”<>”B” | =A10<>A11 | TRUE | ||
| =”10/9/2018″<>”10/10/2018″ | =A12<>A13 | TRUE | ||
| =TRUE<>FALSE | =A14<>A15 | TRUE | ||
| =5<>5<>5 | =A3<>A4<>A3 | TRUE | ||
| =”TRUE”<>TRUE | =A16<>A14 | TRUE | ||
Note that, always the formula in Excel starts with the “=” sign. And also to assign a value to the Cell; we can use the “=” sign. For example, =78, which means, it assigns the value 78 to the Cell.
Greater than Operators
In Excel, we have “Greater than (>)” and “Greater than or equal to (>=)” operators to compare the two values and return whether the first value is Greater or Greater than equals to the second value.
When you use the “Greater than (>)” operator, it verifies whether the first value is greater than the second value. But the “Greater than or equal to (>=)” operator, compares two values; whether the first value is greater than or equal to the second value. Notice that, “>=” verifies also the equality of the two values.
See the below table for more examples:
| Comparison Operators | ||||
| Data | Operator | Example | Cell Reference | Result |
| 5 | greater than sign (>) | =5>5 | =A3>A4 | FALSE |
| 5 | =5>6.5 | =A3>A6 | FALSE | |
| 65 | =”A”>65 | =A10>A9 | TRUE | |
| 6.5 | =”65″>65 | =A9>A5 | TRUE | |
| “APPLE” | =”APPLE”>”apple” | =A7>A8 | FALSE | |
| “apple” | =”A”>”B” | =A10>A11 | FALSE | |
| “65” | =”10/9/2018″>”10/10/2018″ | =A12>A13 | FALSE | |
| “A” | =TRUE>FALSE | =A14>A15 | TRUE | |
| “B” | =5>5>5 | =A3>A4>A3 | TRUE | |
| 43382 | =”TRUE”>TRUE | =A16>A14 | FALSE | |
| 43383 | ||||
| TRUE | ||||
| FALSE | ||||
| “TRUE” | greater than or equal to sign (>=) | =5>=5 | =A3>=A4 | TRUE |
| =5>=6.5 | =A3>=A6 | FALSE | ||
| =”A”>=65 | =A10>=A9 | TRUE | ||
| =”65″>=65 | =A9>=A5 | TRUE | ||
| =”APPLE”>=”apple” | =A7>=A8 | TRUE | ||
| =”A”>=”B” | =A10>=A11 | FALSE | ||
| =”10/9/2018″>=”10/10/2018″ | =A12>=A13 | FALSE | ||
| =TRUE>=FALSE | =A14>=A15 | TRUE | ||
| =5>=5>=5 | =A3>=A4>=A3 | TRUE | ||
| =”TRUE”>=TRUE | =A16>=A14 | FALSE | ||
Always these operators return the result either TRUE or FALSE.
Less than Operators
Like greater than operators, Excel provides Less than Operators; “Less than (<)” and “Less than or equal to (<=)” operators to compare the two values; whether the first value is lesser than or lesser than equal to the second value.
Here are the examples:
| Comparison Operators | ||||
| Data | Operator | Example | Cell Reference | Result |
| 5 | less than sign (>) | =5<5 | =A3<A4 | FALSE |
| 5 | =5<6.5 | =A3<A6 | TRUE | |
| 65 | =”A”<65 | =A10<A9 | FALSE | |
| 6.5 | =”65″<65 | =A9<A5 | FALSE | |
| “APPLE” | =”APPLE”<“apple” | =A7<A8 | FALSE | |
| “apple” | =”A”<“B” | =A10<A11 | TRUE | |
| “65” | =”10/9/2018″<“10/10/2018” | =A12<A13 | TRUE | |
| “A” | =TRUE<FALSE | =A14<A15 | FALSE | |
| “B” | =5<5<5 | =A3<A4<A3 | FALSE | |
| 43382 | =”TRUE”<TRUE | =A16<A14 | TRUE | |
| 43383 | ||||
| TRUE | ||||
| FALSE | ||||
| “TRUE” | less than or equal to sign (>=) | =5<=5 | =A3<=A4 | TRUE |
| =5<=6.5 | =A3<=A6 | TRUE | ||
| =”A”<=65 | =A10<=A9 | FALSE | ||
| =”65″<=65 | =A9<=A5 | FALSE | ||
| =”APPLE”<=”apple” | =A7<=A8 | TRUE | ||
| =”A”<=”B” | =A10<=A11 | TRUE | ||
| =”10/9/2018″<=”10/10/2018″ | =A12<=A13 | TRUE | ||
| =TRUE<=FALSE | =A14<=A15 | FALSE | ||
| =5<=5<=5 | =A3<=A4<=A3 | FALSE | ||
| =”TRUE”<=TRUE | =A16<=A14 | TRUE | ||
Observe that, when you compare text and numeric data; the text value considers higher than the numeric value. When you compare text and numeric values; it is good practice to convert them to either text or numeric data using VALUE or TEXT functions. I will try to explain these functions in the next Articles.
We will discuss more Excel functions/operators as we go.
🙂 Sahida