Many tasks in Excel require comparing data in different cells. To do this, Excel offers six logical operators, also known as comparison operators. This tutorial will help you understand how these operators work and how to write efficient formulas for data analysis.
Table of Contents
Excel logical operators - overview
In Excel, a logical operator is used to compare two values. These operators are also called Boolean operators because the result is always either TRUE or FALSE.
Excel has six logical operators. The table below explains what each one does and gives examples of formulas to show how they work.
Condition | Operator | Formula Example | Description |
Equal to | = | =A1=B1 | The formula returns TRUE if a value in cell A1 is equal to the values in cell B1; FALSE otherwise. |
Not equal to | <> | =A1<>B1 | The formula returns TRUE if a value in cell A1 is not equal to the value in cell B1; FALSE otherwise. |
Greater than | > | =A1>B1 | The formula returns TRUE if a value in cell A1 is greater than a value in cell B1; otherwise it returns FALSE. |
Less than | < | =A1<B1 | The formula returns TRUE if a value in cell A1 is less than in cell B1; FALSE otherwise. |
Greater than or equal to | >= | =A1>=B1 | The formula returns TRUE if a value in cell A1 is greater than or equal to the values in cell B1; FALSE otherwise. |
Less than or equal to | <= | =A1<=B1 | The formula returns TRUE if a value in cell A1 is less than or equal to the values in cell B1; FALSE otherwise. |
The screenshot below demonstrates the results returned by Equal to, Not equal to, Greater than and Less than logical operators:
It might seem like the table explains everything, but each logical operator has its own details. Understanding these can help you get the most out of Excel formulas
Using "Equal to" logical operator in Excel
The ‘Equal to’ logical operator (=) can be used to compare any type of data—numbers, dates, text, Booleans, and even the results from other Excel formulas. For example:
=A1=B1 | Returns TRUE if the values in cells A1 and B1 are the same, FALSE otherwise. |
=A1=”oranges” | Returns TRUE if cells A1 contain the word “oranges”, FALSE otherwise. |
=A1=TRUE | Returns TRUE if cells A1 contain the Boolean value TRUE, otherwise it returns FALSE. |
=A1=(B1/2) | Returns TRUE if a number in cell A1 is equal to the quotient of the division of B1 by 2, FALSE otherwise. |
Example 1. Using the "Equal to" operator with dates
You might be surprised to learn that the ‘Equal to’ operator doesn’t compare dates as easily as numbers. For example, if A1 and A2 both have the date ’12/1/2014′, the formula =A1=A2 will return TRUE, which is correct.
But if you try =A1=12/1/2024 or =A1=”12/1/2024″, it will return FALSE. Strange, right?
This happens because Excel stores dates as numbers starting from 1-Jan-1900, which is stored as 1. The date 12/1/2014 is stored as 45627. Excel treats “12/1/2014” as text, and since text isn’t equal to 45627, it returns FALSE.
To get the correct result, you need to use the DATEVALUE function, like this: =A1=DATEVALUE(“12/1/2014”).
Note. The DATEVALUE function needs to be used with other logical operator as well, as demonstrated in the examples that follow.
Example 2. Using the "Equal to" operator with text values
Using Excel’s ‘Equal to’ operator with text is simple. Just remember that it’s case-insensitive, meaning it ignores uppercase and lowercase differences when comparing text.
For example, if A1 has ‘oranges’ and B1 has ‘Oranges’, the formula =A1=B1 will return TRUE.
If you want to compare text and consider case differences, use the EXACT function instead of the ‘Equal to’ operator. The EXACT function is simple to use, and the syntax looks like this
EXACT(text1, text2)
Text1 and Text2 are the values you want to compare. If they are exactly the same, including uppercase and lowercase, Excel returns TRUE. If not, it returns FALSE. You can also use the EXACT function in IF formulas when you need to compare text with case sensitivity, as shown in the screenshot below
Note. If you want to compare the length of two text values, you can use the LEN function instead, for example =LEN(A2)=LEN(B2) or =LEN(A2)>=LEN(B2).
Example 3. Comparing Boolean values and numbers
Many people think that in Excel, TRUE always equals 1 and FALSE equals 0. But this isn’t always true. The key word here is ‘always,’ or actually, ‘not always’ 🙂
When you compare a Boolean value (TRUE or FALSE) with a number, you need to tell Excel to treat the Boolean as a number. You can do this by adding a double minus sign in front of the Boolean value or cell reference, like this: =A2=–TRUE or =A2=–B2.
The first minus sign changes TRUE/FALSE to -1/0, and the second minus sign turns them into +1 and 0. This will make more sense when you see the screenshot below:
Note – You should add the double unary operator before a Boolean when using other logical operators such as not equal to, greater than or less than to correctly compare a numeric and Boolean values.
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 check that a cell’s value is different from a specific value. It works in a similar way to the ‘Equal to’ operator we talked about earlier.
The results from the ‘Not equal to’ operator are like those from the Excel NOT function, which reverses the value of its argument. The table below gives some examples of formulas.
Not equal to operator | NOT function | Description |
=A1<>B1 | =NOT(A1=B1) | Returns TRUE if the values in cells A1 and B1 are not the same, FALSE otherwise. |
=A1<>”oranges” | =NOT(A1=”oranges”) | Returns TRUE if cell A1 contains any value other than “oranges”, FALSE if it contains “oranges” or “ORANGES” or “Oranges”, etc. |
=A1<>TRUE | =NOT(A1=TRUE) | Returns TRUE if cell A1 contains any value other than TRUE, FALSE otherwise. |
=A1<>(B1/2) | =NOT(A1=B1/2) | Returns TRUE if a number in cell A1 is not equal to the quotient of the division of B1 by 2, FALSE otherwise. |
=A1<>DATEVALUE(“12/1/2014”) | =NOT(A1=DATEVALUE(“12/1/2014”)) | Returns TRUE if A1 contains any value other than the date of 1-Dec-2014, regardless of the date format, FALSE otherwise. |
Greater than, less than, greater than or equal to, less than or equal to
You use these logical operators in Excel to compare two numbers. Excel has 4 comparison operators, which are easy to understand:
- Greater than (>)
- Greater than or equal to (>=)
- Less than (<)
- Less than or equal to (<=)
These operators are mostly used with numbers, dates, and times. For example
=A1>20 | Returns TRUE if a number in cell A1 is greater than 20, FALSE otherwise. |
=A1>=(B1/2) | Returns TRUE if a number in cell A1 is greater than or equal to the quotient of the division of B1 by 2, FALSE otherwise. |
=A1<DATEVALUE(“12/1/2014”) | Returns TRUE if a date in cell A1 is less than 1-Dec-2014, FALSE otherwise. |
=A1<=SUM(B1:D1) | Returns TRUE if a number in cell A1 is less than or equal to the sum of values in cells B1:D1, FALSE otherwise. |
Using Excel comparison operators with text values
In theory, you can use the ‘greater than’ and ‘less than’ operators with text values too. For example, if A1 has ‘apples’ and B1 has ‘bananas’, what do you think =A1>B1 will return? The answer is FALSE 🙂
When comparing text, Excel ignores uppercase and lowercase. It compares letter by letter, with ‘a’ being the smallest and ‘z’ the largest.
In the ‘apples’ vs. ‘bananas’ example, Excel compares ‘a’ and ‘b’ first. Since ‘b’ is greater than ‘a’, the formula =A1>B1 returns FALSE.
If the first letters are the same, Excel compares the next letters. For example, if A1 had ‘apples’ and B1 had ‘agave’, =A1>B1 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 not often used by themselves. While the TRUE and FALSE values they give are correct, they aren’t very helpful on their own. To get more useful results, you can use logical operators inside Excel functions or with conditional formatting rules, like in the examples below.
• Using logical operators in arguments of Excel functions
Excel is very flexible when it comes to logical operators, letting you use them in many different functions. One of the most common uses is in the IF function, where comparison operators help create a logical test. The IF formula then gives a result depending on whether the test is TRUE or FALSE.
For example: =IF(A1>=B1, “OK”, “Not OK”)
This formula returns “OK” if the value in cell A1 is greater than or equal to the value in B1, and “Not OK” if it isn’t.
Here’s another example: =IF(A1<>B1, SUM(A1), “”)
This formula checks if the value in A1 is not equal to the value in B1. If they are not equal, it adds up the values in cells A1 to C1. If they are equal, it returns an empty string.
Logical operators are also often used in special IF functions like SUMIF, COUNTIF, AVERAGEIF, and their plural versions, which give results based on one or more conditions
• 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: =IF(B2>C2, B2*10, B2*5)
Formula with logical operators: =(B2>C2)*(B2*10)+(B2<=C2)*(B2*5)
The IF formula is probably easier to understand, right? It tells Excel to multiply the value in cell B2 by 10 if B2 is bigger than C2. If not, it multiplies the value in B1 by 5.
Now, let’s look at the second formula with the greater than (>) and less than or equal to (<=) operators. It’s helpful to know that in Excel, TRUE equals 1, and FALSE equals 0 in calculations. Keeping that in mind, let’s break down what the logical expressions actually do.
If the value in B2 is greater than the value in C2, the expression B2>C2 is TRUE, which means it’s equal to 1. On the other hand, B2<=C2 is FALSE, which is equal to 0. So, if B2 is greater than C2, the formula changes in this way
Since any number multiplied by zero equals zero, we can ignore the second part of the formula after the plus sign. And because any number multiplied by 1 stays the same, our complex formula becomes a simple =B2*10, which just multiplies B2 by 10—exactly what the IF formula does. 🙂
If the value in B2 is less than the value in C2, the expression B2>C2 becomes FALSE (0), and B2<=C2 becomes TRUE (1), so the opposite will happen
• Logical operators in Excel conditional formatting
Another common use of logical operators is in Excel’s Conditional Formatting, which helps you easily highlight important information in a spreadsheet.
For example, these simple rules highlight cells or entire rows based on the value in column A:
- Less than 5 (orange): =A1<5
Greater than 20 (green): =A1>20
As you can see, using logical operators in Excel is simple and easy to understand. In the next article, we’ll dive into the details of Excel’s logical functions, which let you make more than one comparison in a formula. Stay tuned, and thanks for reading!
