(Video tutorial's page: Compare Values in Excel - Beginner to Advanced)
Logical comparison operators allow you to compare values in Excel to see if they are equal, not equal, greater than, or less than. The result of one of these comparisons will either be TRUE or FALSE. These operators allow you to make powerful spreadsheets in Excel and, here, I will tell you how to use these operators.
Condition | Operator | Description | Simple Usage Example |
---|---|---|---|
Equal to |
= |
Checks if a value is equal to another value. |
=1=5 (checks if 1 is equal to 5 - result will be FALSE) |
Not equal to | <> |
Check if a value is NOT equal to another value. |
=1<>5 (checks if 1 is NOT equal to 5 - result will be TRUE) |
Greater than |
> |
Check if a value is greater than another value. |
=1>5 (checks if 1 is greater than 5 - result will be FALSE) |
Less than |
< |
Check if a value is less than another value. |
=1<5 (checks if 1 is less than 5 - result will be TRUE) |
Greater than or equal to |
>= |
Check if a value is greater than OR equal to another value. |
=1>=5 (checks if 1 is greater than or equal to 5 - result will be FALSE) |
Less than or equal to |
<= |
Check if a value is less than OR equal to another value. |
=1<=5 (checks if 1 is less than or equal to 5 - result will be TRUE) |
You use logical operators within formulas and functions in order to make comparisons.
Here is a list of the most basic formulas using logical operators and their results (examples are from the table above):
Here, we are simply comparing 1 to 5.
As you can see, the output of any comparison made with logical operators will always be either TRUE or FALSE. That output is often useless on its own but functions can use that output to perform complex operations in Excel.
The function where you will most often use logical operators is the IF statement. This statement helps you make decisions in Excel and directly adds logic to the spreadsheet.
To use a logical operator in a function, you simply type the comparison anywhere in the function where it is required.
Here, we will compare the values from cell D1 and E1 and output a result based on that comparison:
The formula is in cell D5 but is listed in cell D3. Looking to cell D3, you can see that we used the logical operator greater than within the IF statement. Using this logical operator, greater than, any time the value in cell D1 is greater than the value in cell E1, the IF statement will output "End of the World".
As far as logical operators in functions and formulas goes, that's all there is to it. You simply use the desired comparison operators to type a comparison anywhere you need something to evaluate to either TRUE or FALSE.
As you can see, logical operators help you made decisions within Excel and, though this is a basic example, you can see just how important this functionality is in the spreadsheet. This is what allows you to create powerful spreadsheets, and it all starts with these simple logical operators.
Note: When you use logical operators to make comparisons within a function you do not need to put an equal sign in front of the logical comparison. The equal sign in front of the logical comparisons made at the top of this tutorial where there just because you need an equals sign to start a formula or function in any cell, but the following formulas/functions/logical comparisons within the same cell do not need their own equals sign to start it.
The below links will show you more complex ways that you can use logical operators to make comparisons in Excel.
How to Use the IF Function in Excel
AND Function - Check if All Arguments are True
OR Function - Check if Any Argument is True
Logical operators are a small piece of the puzzle that is Excel but they will help you to make immensely more useful and powerful spreadsheets. Learn the comparison operators, and just keep them in the back of your mind for when you might need to use them.
Make sure to download the accompanying spreadsheet to see all of the logical operators at work in Excel.