Logical operators in Excel
The basic use of logical operators is to compare data. In MS Excel, logical operators are used to comparing data stored in Excel cells. You have performed several types of basic and advanced operations on Excel data to manipulate the data. Comparison is one of the operations that you can perform on Excel data.
Excel provides logical operators for comparing the data. You can compare two or more values stored in an Excel sheet with each other. These values can be numeric, text, or strings.
Comparing data is an important and useful operation of Excel, for which Excel provides six logical operators. These logical operators are equal, not equal, greater than, less than, greater than equal to, less than equal to.
In this chapter, we will brief all six logical operators with their examples to see how and where you can use them.
Types of Logical operators
As we told you, Excel provides six logical operators to compare the data stored in an Excel sheet. These logical operators are equal, not equal, greater than, less than, greater than equal to, less than equal to.
These operators work to compare the two values differently but provide either TRUE or FALSE as a result.
| Operator Name | Operator | Description | Formula (For TRUE) |
|---|---|---|---|
| Equal to | = | Compares two values are equal, if True | =A2=B2(=51=51) |
| Not Equal to | <> | Determines two values are not equal | =A2<>B2 (=48<>51) |
| Greater than | > | Compare one value is greater to another value. | =A2>B2 (=13>17) |
| Less than | < | Compare one value is less than from another value. | =A2 |
| Greater than equal to | >= | Compare first value is greater than equal to another value. | =A2>=B2 (=7>=4) |
| Less than equal to | <= | Compare one value is less than or equal to another value. | =A2<=B2 (=16<=16) |
All these operators are discussed with examples in detail. Here, equal to and not equal to are case-insensitive operators.
Note: All the operators return TRUE if the condition satisfies for the given values. Otherwise, they return FALSE.
Equal to operator (=)
Equal to is a logical operator that is represented by = sign. It is used to compare two values whether they are the same or not.
Syntax
It returns TRUE if the condition satisfies. Otherwise, it returns FALSE.
Example
In this example, we will show you both string and number comparisons. We have the following dataset of strings and numbers in an Excel worksheet.
Now, do the comparison using the equal (=) operator to check whether the values are same or not. Write the comparison formula for the row 11 values –
Hit the Enter key and get the comparison result.
By just changing the cell reference in the above formula, perform more comparisons for the other row’s data (in the given data) and get results for them. See all results.
You can verify their result by just seeing the equal values. As we told you that Equal to (=) is case insensitive. Here, cells A13 (Elon) and B13 (ELON) have the same string but in different cases; that is why it returned TRUE.
It ignored the case of strings data.
Provide the values directly in formula
- For String data comparison, write the formula in this way: = “Rhea”=”RHEA”
- For numeric data comparison, write the formula in this way: =23=76
- You do not need to provide numeric value in double-quotes to compare them like string. You can put them directly, e.g., =15=15.
Similarly, you can do with more operators.
Not Equal to operator (<>)
Equal to is a logical operator that is represented by <> sign. It is just opposite to the Equal (=) operator.
Syntax
It returns TRUE if the condition satisfies. Otherwise, it returns FALSE.
Example
In this example, we will use the same dataset used in the above example to make comparisons using the Not Equal to (<>) operator. We have the following dataset of strings and numbers in an Excel worksheet.
Now, write the formula firstly for the row 11 values (column A, column B). Use the <> operator between both conditions (values).
Hit the Enter key and get the comparison result.
By just changing the cell reference in the above formula, perform more comparisons for the other row’s data (in the given data) and get results for them. See all results.
You can verify their result by analyzing their results in your mind. We told you that Not Equal to (<>) is case insensitive. Thus, analysis carefully:
Notice that – A13 (Elon) and B13 (ELON) cells contain the same string but in different cases. Thus, it ignored the case of strings and treated them same, and returned FALSE.
Difference between = and <>
Equal and Not equal are the just opposite operators to each other and provide opposite results. Remember that – all logical operators are case-insensitive. It does not differentiate between uppercase and lowercase letters. For example, the string Hello and HELLO both strings will treat same although they are in different cases.
We will show you some string and number comparison examples for equal (=) and not equal to (<>) operator. You will see both will return just opposite values to each other. Let’s see a list of examples –
See that both operators have provided just opposite results to each other for the same data. Now, let’s move to the next operator.
Greater than operator (>)
Greater than is a logical operator that is represented by > sign. It is used to compare two values whether the first value is greater than the second one. This operator is usually used for numeric value comparison, but you can also use it to check that the first string is greater than the second string.
Syntax
It returns TRUE if the condition satisfies. Otherwise, it returns FALSE.
Example 1
It is easy to identify small values that which one is greater. Thus, we will use large numeric values for comparison.
- We have the following numeric dataset as given below.
- Firstly, write the formula to compare the row11 values to find A11 is greater than B11 or not.
=A11>B11
- Hit the Enter key and get the comparison result. It returned TRUE because 82134 is greater than 40452.
Note: It can also compare strings and text.
String comparison
We can also compare the text data to find which data string is greater. But the string comparison goes differently. It is very important to know how string comparison is made in Excel.
For example, if we compare two texts strings, “Hello” and “Hiii”. Microsoft Excel ignores the case of the string, and comparison will be like:
Firstly, the first letter of both strings (“H” and “H”) will be compared, and if they are same (identical) as here, comparison will move to the next letter. Now, “e” of Hello and “i” of Hiii will be compared. They are different, so check either “e” or “i” is greater.
Note: In MS Excel, “a/A” is considered as lowest value and “z/Z” is highest text value.
Here, “i” in Hiii string is greater than “e” in Hello. Hence, Hiii is the greater string than Hello. Let’s see some greater than string comparisons.
Example 2
- We have added some string values to the previously used dataset. Now, find which string is greater.
- By putting the formula =condition1>condition2, we get the result for all strings. See them below.
We have circled the greater string here and also written their formulas.
Less than operator (<)
Less than is also a logical operator that is represented by < sign. It is used to compare two values whether the first value is less than to the second one.
Syntax
It returns TRUE if the condition satisfies. Otherwise, it returns FALSE.
Example
In this example, we will take the same dataset with their results that used in the above example. Thus, you can perform less than comparison on data to get the result and compare with greater than results.
- See the data for comparison.
- Write the following comparison formula for A11 and B11 cell data and then hit the Enter key to get the result.
=A11
This time operator is different, i.e., < (less than operator). - See the result for the first comparison, i.e., FALSE. It means 82134 (A2) is not less than 40452 (B2).
Also, compare both comparison results that > comparison returned TRUE whereas < comparison returned FALSE. - Now, perform the less than operator comparisons on remaining data and see the result for them as given below:
You can also see the result for both operators. They have provided just opposite results to each other except the last row data (A16 and B16). Because these are the same rather than greater or less.
Solution
In this type case, Excel provide >= and <= operators. Except the greater than and less than operation, they also check the equal values. Both the >= and <= operators work in the similar way of < and > operator.
Greater than equal to operator (>=)
Greater than equal to operator is an advanced version of greater than operator. It is represented by >= sign. This operator is used to check whether the first value is greater or equal to the second one.
It is an advanced operator of greater than (>) operator that comes with one extra functionality. It allows the users to check greater as well as equal value between two values.
It will provide the same result as greater than operator if the first value is greater or not to the second. If both the compared values are same, it will return TRUE.
Syntax
It returns TRUE if the condition satisfies. Otherwise, it returns FALSE.
- If the first compared value is either greater or equal to the second value, condition satisfies and returns TRUE. Otherwise, it will return FALSE.
Example 1
We will now use this operator to find that the first value is either greater than or equal to the second value or not.
- We have this numeric and string data for the comparison using the greater than equal to (>=) operator.
- Write the formula in an adjacent cell for comparing the values and find first value is greater or equal to the second value or not. Firstly, for row2 data.
=A2>=B2
- It has returned FALSE because the first A1 cell value is neither greater nor equal to the second B2 cell value.
- Now, perform comparison on the remaining data and get the result for them by using the >= operator.
You have seen that using this operator, we get the result TRUE when the first comparison value is greater than the second.
Additionally, we have got the advantage of = operator with > sign. When the first value is not greater nor less, it is equal to the second; it returned TRUE.
Less than equal to operator (<=)
You can say that the less than equal to operator (<=) is an advanced version of less than operator (<). It is represented by <= sign. This operator is used to check whether the first value is less or equal to the second one.
It is an advanced operator of less than (<) operator that comes with one extra functionality. It allows the users to check less than as well as equal value between two values.
It will provide the same result as less than operator if the first compared value is less or not than the second. If both the compared values are same, it will return TRUE.
Syntax
It returns TRUE if the condition satisfies. Otherwise, it returns FALSE.
Example
In this example, we will take the same dataset with their results which were used in greater than equal operator example. Thus, you can perform comparison on data using less than equal to (<=) operator to get the result and compare with greater than equal to (>=) results.
- See the previously used data for comparison here.
- Write the following comparison formula for A2 and B2 cell data and then hit the Enter key to get the result.
=A2<=B2
This time operator is different, i.e., <= (less than equal to operator). - See the result for the first comparison, i.e., FALSE. It means 3564 (A2) is less than 6835 (B2).
Also, compare both comparison results that >= comparison returned FALSE in the the previous example whereas <= comparison returned TRUE now. - Now, perform the less than equal to operator comparisons on remaining data and see the result for them as given below:
You can also see the result for both operators. They have provided just opposite results to each other.
Analyze the result for the comparison using both operators. They have returned the just opposite results to each other except same the same string. They both returned TRUE when the strings are same.
