How to Use Logical Operators in Excel to Compare Data

Logical operators in Excel play a crucial role in performing data comparisons and evaluating conditions. They might seem like unassuming math symbols, but they come in quite handy on more than one occasion.

When used correctly, and when paired with other functions, they hold the power to help you get insight into your spreadsheet data and drive conclusions in a few clicks. Read along to learn how to use each logical operator and incorporate them into your data analysis alongside other formulas.

What Are Logical Operators in Microsoft Excel?

In Microsoft Excel, logical operators are used to compare two values stored in the cells of your spreadsheet. They are also known as comparison operators. These values can be numeric or text.

Microsoft Excel provides six logical operators: equal to, not equal to, greater than, greater than equal to, less than, and less than equal to.

These operators return either a TRUE or FALSE output based on the result of the comparison. If the comparison matches the criteria, then the result is TRUE, otherwise, it is FALSE.

Here is an overview of the six types of logical operators, along with a simple example for each.

These logical operators can also be used in combination with other Excel functions to perform more complex calculations or to create conditional formulas. We will go through the use cases of these logical operators in the following sections.

Using Equal to (=) Logical Operator in Excel

To verify if two values are equal to each other, we use the = logical operator, with the following syntax:

 =value1=value2  

If value1 is indeed equal to value2, then the cell's output will be automatically updated to TRUE. If the two values are different, it will be updated to FALSE.

Note that all logical operator functions start with an equal sign. Do not confuse it with the logical operator. The first equal sign is added to indicate to Excel that it needs to process that operation. It does not affect the result of the comparison whatsoever. If you do not add it, Excel will treat your input as any other string of characters and will not yield any result.

Let's take these values as an example and compare each set of two values to each other.

1. Build your data set.

   

2. Add an = sign to your output cell and select your first value either by clicking on its cell or typing its reference (in this case A2), followed by the logical operator =, then add your second value (in this case B1).

   

3. Hit Enter on your keyboard to display the result.

   

4. In case of multiple sets of values to compare, drag down the corner of the cell with your mouse to apply the same logical operator to the other sets.

   

As you can see, the result will be TRUE if the two values are equal, and FALSE if they are different.

Using Not Equal to (<>) in Excel

To verify if two values are not equal to each other, use the <> logical operator, with the following syntax:

 =value1<>value2 

If value1 is different from value2, then the cell's output will be automatically updated to TRUE. If the two values are equal, it will be updated to FALSE.

We will take the same set of values as before to demonstrate how this logical operator work.

1. Build your data set.

   

2. Add an = sign to your output cell and select your first value either by clicking on its cell or typing its reference (in this case A2), followed by the logical operator <>, then add your second value (in this case B1).

   

3. Hit Enter on your keyboard to display the result.

   

4. In case of multiple sets of values to compare, drag down the corner of the cell with your mouse to apply the same logical operator to the other sets.

   

All results are now displayed on your table.

Using Greater Than (>) and Less Than (<) Logical Operators in Excel

To verify if one value is greater than another, use the > logical operator, with the following syntax:

 =value1>value2 

To verify if one value is greater than or equal to another, use the >= logical operator, with the following syntax:

 =value1>=value2 

To verify if one value is less than another, use the < logical operator, with the following syntax:

 =value1<value2 

To verify if one value is less than or equal to another, use the <= logical operator, with the following syntax:

 =value1<=value2 

Remember: For both >= and <=, the = sign always comes on the right of either the > or < sign. Switching their positions would yield an error. Since these operators follow the same logic, let's try them on a couple of value sets using >=:

1. Build your data set.

   

2. Add an = sign to your output cell and select your first value either by clicking on its cell or typing its reference (in this case A2), followed by the logical operator >=, then add your second value (in this case B1).

   

3. Hit Enter on your keyboard to display the result.

   

4. In case of multiple sets of values to compare, drag down the corner of the cell with your mouse to apply the same logical operator to the other sets.

   

These operations work the same way for >, <, and <=.

Logical Operator Specifics to Remember

Microsoft Excel handles data in a specific way, so it is important to keep the following in mind when using logical operators:

Comparing Numeric Values

• The result of the comparison will not be affected by the format of the numeric value. Whether it is a regular number, a percentage, or in currency format, Excel will compare the original numbers in the values, stripped from any format transformation.
• We can compare two values directly inside a cell, simply by typing =number1=number2, =number1<>number2, or using any other logical operator.

  

Comparing Text Values

• The operators are not case-sensitive. They will yield a TRUE value if the same text is displayed in different cases.
• You can compare two values directly inside a cell. You can achieve this by adding quotes around the values.

  

There are also several other ways to check if two values are equal in Excel. Now, to take our Excel skills further, let's review how to use logical operations with formulas.

Using Logical Operators in Excel With Formulas

Let's say we want to display different messages depending on the result of the comparison. This is where mixing logical operators with other functions comes in handy.

The most notable and used formula in this case is the IF function, to perform conditional operations, with the following syntax:

 =IF(logical_test, [value_if_true], [value_if_false]) 

Let's take this example, where we want to display the result of the comparison to be more reader-friendly.

1. Build your data set.

   

2. Write your IF function by selecting the two values to compare, then the text to display in case of TRUE, then the text to display in case of FALSE. Both should be between brackets.

   

3. Hit Enter to display the result. When the comparison result is TRUE, the first text will show.

   

   Whereas it displays this other message if the comparison output if FALSE.

   

Remember to put your text between quotation marks, otherwise you would get an error message. If you ever come across one, there are some common IF function troubleshooting methods to try.

The Scope of Logical Operators Is Quite Revealing

Logical operators in Microsoft Excel might look basic at first, but they can be powerful in helping you make sense of your data, make your sheets more readable, and translate your findings into insights when paired with the IF function.

They also offer a glimpse on the way Excel handles data comparison, one of the most critical aspects of data transformation, when dealing with different types of data sets.