Use logical operators in Excel to compare values. The output is true or false, depending on conditions that match the criteria or not.
Today’s guide will be on logical operators (comparison operators). First, you will learn how to compare two or more values and how to analyze the output. Then, we will take a closer look at all Excel logical operators through easy-to-understand examples.
Table of contents:
- List of logical Operators in Excel
- How to use logical operators in Excel to compare numerical values
- How to use logical operators in Excel Formulas
List of logical operators and their functions in Excel
Excel uses six logical operators to compare values.
| Condition | Logical Operator | Formula Example | The Formula returns |
|---|---|---|---|
| Equal | = | =A1 = A2 | TRUE if a value in cell A1 is equal to the values in cell A2. If not, the result is FALSE. |
| Not equal to | <> | =A1 <> A2 | TRUE if a value in cell A1 is not equal to the value in cell A2. If not, the result is FALSE. |
| Greater than | > | =A1 > A2 | TRUE if a value in cell A1 is greater than a value in cell A2. If not, the result is FALSE. |
| Less than | < | =A1 < A2 | TRUE if a value in cell A1 is less than equal to a value in cell A2. If not, the result is FALSE. |
| Greater than equal to | >= | =A1 >= A2 | TRUE if a value in cell A1 is greater than equal to a value in cell A2. If not, the result is FALSE. |
| Less than equal to | <= | =A1 <= A2 | TRUE if a value in cell A1 is less than equal to a value in cell A2. If not, the result is FALSE. |
How to use Logical Operators in Excel to Compare Numerical Values
After the quick summary of logical operators, let us see the details.
Equal (=) operator
Let us see the most used operator, the equal sign. You can use the equal sign to compare two or more cells. In the example, compare the cell values from cell B3 to C6. To test whether the value in column B is equal to the column C value (criteria), follow the steps below.
Good to know: In Excel, you can only compare cells containing text and numbers using the “Equal to” logical operator. All other logical operators can compare values.
1. Type a formula with starting an equal sign: =B3=C3
2. Press Enter, then copy the formula down.
If the numbers are equal, Excel gets TRUE as a result. In any other case, the result is FALSE.
Not Equal (<>) operator
If you combine the greater than (>) and the less than (<) logical operators in one formula, you can test the not equal (<>) operator. The not equal operator sign is the reverse of the equal sign.
Differences between the equal and not equal logical operators: The equal sign logical test returns with TRUE if the compared values are equal. The not equal test returns TRUE if the compared values are not equal and gets FALSE if the values are equal.
Use the same data set as above but change the formula to =B3<>C3.
It is not surprising that the result is:
Because only the B5 and C5 cell values are equal (met the criteria), the formula will return FALSE in this case. In the other cases, Excel gets the TRUE as a result of the formula.
Greater Than (>) operator
You can only use the greater than (>) logical operator to compare numerical values. In the example, you will test the values in range B2:B6. Then, in column C, use criteria (=80) to check that these values are greater than 80.
As a result of the formula, two values are greater than 80. The greater than (>) test gets TRUE where values are greater than 80. In the case of cells B3 and B6, you will get FALSE due to the test.
What if you change the value in cell B6 and use 80 for comparison? The result is FALSE.
Greater Than or Equal To (>=) operator
You have learned that the greater than logical operator returned FALSE for only values greater than the criteria value.
Let us see what will happen if you are using the >= symbol.
Enter the formula in cell D3; press Enter, then copy the formula down. The formula returns TRUE as a result of the comparison.
Learn more about the greater than or equal to operator.
Less than (<) operator
Now, you will use the less than (<) logical operator to test numerical values.
For example, the following formula will use two numerical values. You want to test whether the given value is less than 80.
As you see, the result is the opposite of the greater than criteria. The formula returns TRUE if value1 < value2.
Less Than or Equal To (<=) operator
The logic of the less than equal to (<=) operator is the same as the >= sign but the opposite. Let us see a formula that uses the criteria:
=B3 <= C3 where C3 = 80.
Take a closer look at cell D6. The result is TRUE.
How to use logical operators in Excel Formulas
Use logical operators as an argument of an Excel function. Try to combine the IF function and the equal to (=) operator in the following example:
The IF function uses a logical test and returns one value if TRUE and another value if FALSE.
For the sake of simplicity, here is an example:
- =IF(1=1,”Equal”, ”Not equal”) = “Equal”
- =IF(B3=C3, ”Equal”, ”Not equal”)
Copy the formula down and check the result.
Excel logical operators and conditional formatting formulas
Conditional formatting helps you to highlight the cells that meet the given criteria. You can use various formulas that contain functions and logical operators.
In the example, create a new formatting rule that contains the greater than (>) operator.
The formula looks like this:
=B3 > 150
This rule will apply the format to range B3:G6. If the given value is greater than the criteria (150), Excel highlights the cells using green fill and white font color.
Wrapping Things Up
- The Excel logical operator gets TRUE or FALSE results only.
- Only the equal to operator works with text values
- All logical operators work with Formulas
- Use logical operators to highlight cells using conditional formatting
