When you work in Excel, sometimes you may want to calculate data only if a particular condition is met. You can achieve this by using logical operators (also known as the comparison operators). Excel provides six of them. This tutorial will provide you with a lot of information on how to use comparison operators inside Excel formulas.

Contents

## Logical operators overview

As I wrote at the beginning, the logical operators are also called comparison operators. But there is also another name for them – they are called Boolean operators because the result of the comparison always returns **TRUE** or **FALSE**.

They work not only with numbers but also with text and dates.

The following table shows the six operators used in Excel and describes how it works.

Name |
Operator |
Example |
Description |

equal to | = | A1=B1 | Returns TRUE only if A1 is equal to A2 |

greater than | > | A1>B1 | Returns TRUE only if A1 is greater than A2 |

less than | < | A1<B1 | Returns TRUE only if A1 is less than A2 |

greater than or equal to | >= | A1>=B1 | Returns TRUE only if A1 is greater or equal to A2 |

less than or equal to | <= | A1<=B1 | Returns TRUE only if A1 is less or equal to A2 |

not equal to | <> | A1<>B1 | Returns TRUE only if A1 is different than A2 |

Here are a few examples showing comparison operators in action.

## Comparing Text

As I wrote in the overview, comparison operators also work with text. What you have to remember, is that Excel treats the text as case-insensitive. If you want to treat uppercase and lowercase characters as different one, you can use the EXACT function with the following.

Here, are some examples.

In cell **A10** there is number one formatted as text and then compared with string. in cell **A11** there is number formatted as a number and then compared to a string. In both cases, each comparison operator returns the same result.

Excel compares text strings in the way that the characters later in the alphabet are considered larger than those that are earlier. So **a < t**, **g < n**, etc.

Let’s take a look at how the text comparison works. In the example below, there are three comparisons.

**C2:** Excel checks the first letter in cell **A2** and compares it to the first letter in cell **B2**. The letter **a < k**, so the formula (**A2>B2**) returns **FALSE**.

**C3:** The first letter in **A3** is the same as the first letter in cell **B3**. The second letter in cell **A3** is “bigger” than the second letter in **B3**, so the formula returns **TRUE**.

**C4:** The first and the second letter are the same in both cells. Excel compares the third letter, which is bigger in cell **B4**, that’s why Excel returns **FALSE**.

There is also a different way to compare strings. You can choose to compare them by the number of characters.

If you want to compare the length of two text strings, you can use the following formula.

=LEN(A2)=LEN(B2)

## Comparing Dates

Besides text and numbers, you can also use logical operators with dates. Let’s take a look at the following table.

The result is probably not what you’ve expected. Why the value in cell **C2** is **FALSE**, and why the values in cells **C3** and **C5** are **TRUE**?

You have to remember that a date in Excel is not saved as date, but rather a number starting from 1^{st} of January 1900 12:00 AM. You can read more about storing dates and times in Excel.

To better illustrate this, press **Ctrl + ~** in the upper-left corner of the keyboard, just below the ESC button.

Now, you can see why the cells in the C column return those values.

**C2**: value **A2** is a number and **B2** is text.

**C3**: Both values are equal, just formatted differently.

**C5**: The same values. The value in **B5** formatted as a date with time. The time is 12:00 AM, which is midnight.

If you want to be sure that the string will be converted to a date (number), use the DATEVALUE function.

=DATEVALUE("1/24/1900")

The following examples illustrate how this function works.

**B2**: The formula compares date in cell **A2** (number 24) to the text string (“1/24/1900”).

**C2**: This example compares **A2** to the mathematical calculation (1 divided by 24, and divided by 1900).

**D2**: This **DATEVALUE** function returns 24, which is the same as the value inside **A2**.

## Comparing numbers to booleans

In some programming languages instead of using **TRUE** and **FALSE**, boolean values are represented by 1 and 0.

In Excel, you can also modify boolean operators to be treated as 0 (FALSE) and 1 (TRUE) by adding two minutes (-) operators.

Here’s how you can do it.

Adding one minus (-) sign to TRUE will create a negative value. In order to negate negative value (to create positive), you have to add the second minus sign.

When it comes to **FALSE**, you can add only one minus because there is no negative 0. But I think it’s a good standard to add also two minutes, so you won’t make a mistake.

## The NOT function with logical operators

The NOT function will negate every result. The function will return TRUE if the result is **FALSE** and **FALSE** if the result is **TRUE**.

Notice, that **=NOT(A2=B2)** works the same way as **=A2<>B2** and **=NOT(A2>B2)** returns the same result as **=A2<=B2**.