Logical Operators in Excel:
Equal to, Not equal to, Greater than, Less than (+much more)
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Most of the operators you use in Excel are probably mathematical, like +, -, *, and /.
But theres another set of useful operators: the logical operators.
Statements like greater than, less than or equal to, and not equal to can be very useful when youre analyzing data.
These comparison operators return a TRUE or FALSE value that can be used with logical functions to give you even more options.
Lets take a look at each one of these operators, and then see how you can use them with the IF function.
*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.
Table of Contents
1: A note about logical operators
2: Grab your free exercise file here!
- 3: Equal to (=) and not equal to (<>)
4: Greater than (>) and less than (<)
5: Greater than or equal to (>=) and less than or equal to (<=)
6: Using logical operators with the IF function
7: Wrapping things up
A note about logical operators
Its important to remember that comparison operators, instead of returning a numeric value, return TRUE or FALSE.
Well see how you might use these values in a bit.
Also keep in mind that Excel treats TRUE as a 1, and FALSE as a 0. So if you need to, you can use the results of logical operators in numerical operations!
And, finally, note thatlogical operators compare values. No matter how the value in a cell was created (whether you typed it in directly or it was created with a formula), these operators compare those values. Not the formulas.
Get your FREE exercise file
Using these operators is very simple, but in case you want to follow along, weve created an example workbook that you can use.
Download it for free below!cloud-download
BONUS:Download theLogical OperatorsExercise Workbook File to go along with this post.
Equal to (=) and not equal to (<>)
These operators are simple: they tell you whether two values are equal or not equal to each other.
Heres what you need to know.
The equal to (=) and not equal to (<>) operators
The equal to operator returns TRUE if the two values being compared are identical, and FALSE otherwise.
The not equal to operatorreturns FALSE if the two values are identical, and TRUE otherwise.
Keep in mind that these operators work with text; well see how in just a moment.
First, well try out the equal to operator.
In cell C2, type the following formula:
This operator resolves to TRUE, because 1 and 1 are identical.
Lets try the not equal to operator in the next row. This row contains text, but the operator will work just as well as it would if we were comparing numbers.
Type this formula in cell C3:
Again, we get TRUE, because Blue and Red arent identical.
The next two rows both contain values that werent entered directly, but were created by formulas.
Try using these two operators on those cells.
Spoiler: youll get the same results as before, because these operators compare values, and not formulas.
Kasper Langmann, Co-founder of Spreadsheeto
Pro tip: logical operators can take formulas as arguments.
In our examples, were comparing simple values. But you can also use formulas in these operators.
For example, you could use something like this:
Greater than (>) and less than (<)
These two operators compare the size of two different values and return TRUE or FALSE depending on which is larger.
The greater than (>) and less than (<) operators
The greater than operator returns TRUE if the first value compared is larger than the second.
The less than operatorreturns TRUE if the first value is smaller than the second.
Both operators will return FALSE if the values are equal.
Also, its worth noting that you can use the greater than and less than operators with text, and Excel will compare the value of the first letters in the words.
A is equal to 1, B is equal to 2, and so on. If the first letters are identical, the second letters will be comparedand Excel will continue working down the line.
The next block of values in the example workbook gives you plenty to play with using the greater than and less than operators.
Well look at a couple specific comparisons so you can see how they work.
First, letss compare two values generated by different formulas. In cells A9 and B9, we have =80+1 and =70+8.
Well compare them using the greater than operator. Heres the formula:
This resolves to TRUE, because the value in cell A9 is larger than the value in B9. Excel doesnt look at the formula: only the values.
Lets see what happens when we compare the words Arctos and Azimuth with this operator.
In this case, we get FALSE. When Excel compares the first two letters, theyre the same, so it moves onto the next letter.
Z is later in the alphabet than R, so the second word is considered to be larger. And because of that, we get FALSE.
Greater than or equal to (>=)
and less than or equal to (<=)
By now, you probably have a good idea of what these operators do. Here are the basics:
The greater than or equal to (>=) and less than or equal to (<=) operators
The greater than or equal to operator returns TRUE if the first argument in the statement is larger than the secondor if the two are equal.
Theless than or equal to operator returns TRUE if the first argument is smalleror the two are equal.
Like the greater than and less than operators, these can also be used on text values.
You probably have a very good idea of how to use these logical operators, so I wont bore you with an extended example.
Instead, look at the third section of values in the example workbook and try using both operators on different types of values. See what happens!
Using logical operators
with the IF function
One of the most common places youll see these logical operators used is inside the IF function.
As we pointed out in our guide to logical functions, IF checks a condition, and if its true, returns a specified value.
That condition will often contain a logical function. Lets take a look at an example.
First, click into cell D7.
Then type the following formula:
=IF(A7>B7, The first value is greater, The second value is greater)
After you hitEnter, youll see the text The first value is greater.
The IF function checked to see whether the logical condition (A7>B7) was true (it was), and then displayed thevalue_if_true.
If that condition had been false, it would have displayedvalue_if_false, the second string in the formula.
Try writing your own IF function that finds a false condition and displays thevalue_if_false using the example workbook!
Kasper Langmann, Co-founder of Spreadsheeto
Wrapping things up
Logical operators are easy to useespecially when youve seen how they work with different types of numbers.
And theyre even more useful when combined with the IF function.
If youre not using logical operators yet, you should be!