Logical Comparison Operators in Excel - How to Compare Things

Add to Favorites
Author: | Edits: don

(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.

List of Logical Comparison 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)

How to Use Logical Operators

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):

22bf1a7449c96a498c6a95e0e34cb7ed.png

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.

Logical Operators within Functions

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:

83bfbc1ec083381f6b2168e97ad19f51.png

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.

Helpful Links and More Useful Examples

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

Notes

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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course