How to Use Logical Operators in Excel (11 Examples)

If you are looking for some of the easiest ways to use logical operators in Excel easily, then you are in the right place. There are 6 types of logical operators Equal to, Not Equal to, Greater than, Less than, Greater than or Equal to, Less than or Equal to, etc. These operators will return TRUE or FALSE depending on the conditions are met or not.

How to Use Logical Operators in Excel: 11 Ways

We have demonstrated the uses of different logical operators here by using the following data table for most of the cases. For this purpose, we have used Microsoft Excel 365 version, you can use any other versions according to your convenience.

Method-1: Using Equal to Logical Operators in Excel with Numeric Values and Texts

Let’s start with the Equal to (=) operator to compare the numeric values of the Cost Price column and the Selling Price column. In addition to this, we will also compare the text values of the Product column with the text “apple”.

One thing to remember is that the logical operators are case-insensitive (so, you don’t have to worry about the cases of the texts for comparison).
For having the results, we have added two columns; Numeric and Text.

Step-01:
Firstly, we will compare the numeric values with Equal to operator
➤ Type the following formula in the cell E5

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Now, we can see that when the Cost Prices and the Selling Prices are equal to each other, then it will give the result as TRUE otherwise FALSE.

Step-02:
Here, we will compare the text values with Equal to operator
➤ Type the following formula in the cell F5

As the logical operators are case-insensitive you can write the text using lower-case or upper-case letters as per your wish. So, we have used “apple” (you can use “Apple” or “APPLE” instead of it)

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Here, we have got TRUE when there is Apple in the Product column.

Method-2: Using Not Equal to Logical Operators in Excel with Numeric Values and Texts

In this section, we will use the  Not Equal to (<>) operator for comparing the numeric values and text values.

Step-01:
Firstly, we will compare the numeric values with Not Equal to the operator
➤ Type the following formula in the cell E5

➤ Press ENTER
➤ Drag down the Fill Handle Tool

We can see that when the Cost Prices and the Selling Prices are not equal to each other then we are getting TRUE otherwise FALSE

Step-02:
Now, it is the turn to compare the text values with Not Equal to operator
➤ Type the following formula in the cell F5

➤ Press ENTER
➤ Drag down the Fill Handle Tool

In this way, you will get TRUE when there is no Apple in the Product column.

Method-3: Using Greater Than Logical Operators in Excel with Numeric Values and Texts

Here, we will use the Greater than (>) operator for comparing the numeric values of the Cost Price column and the Selling Price column and the text values of the Product column with the text “apple”.

Step-01:
Firstly, we will compare the numeric values with Greater than operator
➤ Type the following formula in the cell E5

➤ Press ENTER
➤ Drag down the Fill Handle Tool

After that, you will get TRUE while the Cost Prices are greater than the Selling Prices otherwise FALSE.

Step-02:
Here, we will compare the text values with Greater than operator
➤ Enter the following formula in the cell F5

Logical operators consider the letter A as the lowest value and Z as the highest value while comparing.

With this Greater than operator, it will compare if the values of the texts of the Product column are higher than the text “apple” or not.

So, while comparing Avocado with apple, as the first letter is A for both cases then it will compare the next letter which is v for Avocado and p for apple and here v>p (according to the values serially) and so Avocado will be greater than apple. For this reason, the result will be TRUE

➤ Drag down the Fill Handle Tool

Similarly, for Orange, Strawberry, Banana, Kiwi, Tomato the first letters are greater than A and so it is returning TRUE for these products. For Almond, the second letter l is not greater than p so it is giving FALSE.

Read More: How to Apply ‘If Greater Than’ Condition In Excel

Method-4: Using Less Than Logical Operators in Excel with Numeric Values and Texts

For comparing the numeric values and the texts, here we will use the Less than (<) operator.

Step-01:
Firstly, we will compare the numeric values with Less than operator
➤ Type the following formula in the cell E5

➤ Press ENTER
➤ Drag down the Fill Handle Tool

As a result, we will get TRUE when the Cost Prices are less than the Selling Prices otherwise FALSE

Step-02:
Now, it is the turn to compare the text values with Less than operator
➤ Enter the following formula in the cell F5

With this Less than operator, it will compare if the values of the texts of the Product column are less than the text “apple” or not.

So, while comparing Avocado with apple, as the first letter is A for both cases then it will compare the next letter which is v for Avocado and p for apple and here v>p (according to the values serially) and so Avocado will not be less than apple. For this reason, the result will be FALSE

➤ Drag down the Fill Handle Tool

In the same way, for Orange, Strawberry, Banana, Kiwi, Tomato the first letters are greater than A and so it is returning FALSE for these products. For Almond, the second letter l is less than p so it is TRUE.

Read More: How to Perform Greater than and Less than in Excel

Method-5: Using Greater Than or Equal to Logical Operators in Excel with Numeric Values and Texts

In this section, we will use the Greater than or Equal to (>=) operator for comparing the numeric values and text values.

Step-01:
Firstly, we will compare the numeric values Greater than or Equal to the operator
➤ Type the following formula in the cell E5

➤ Press ENTER
➤ Drag down the Fill Handle Tool

After that, you will get TRUE while the Cost Prices are greater than or equal to the Selling Prices otherwise FALSE.

Step-02:
Now, we will compare the text values with Greater than or Equal to operator
➤ Enter the following formula in the cell F5

With this Greater than or Equal to operator, it will compare if the values of the texts of the Product column are higher than or equal to the text “apple” or not.

So, while comparing Avocado with apple, as the first letter is A for both cases then it will compare the next letter which is v for Avocado and p for apple and here v>p (according to the values serially) and so Avocado will be greater than apple. For this reason, the result will be TRUE

➤ Drag down the Fill Handle Tool

Similarly, for Orange, Strawberry, Banana, Kiwi, Tomato the first letters are greater than A and so it is returning TRUE for these products, and for Apple, it is equal to the text so it is also returning TRUE. For Almond, the second letter l is not greater than p so it is giving FALSE.

Read More: How to Use Greater Than or Equal to Operator in Excel Formula

Method-6: Using Less Than or Equal to Logical Operators in Excel with Numeric Values and Texts

For comparing the numeric values and the texts, here we will use the Less than or Equal to (<=) operator.

Step-01:
We will start with the Less than or Equal to operator to compare the numeric values
➤ Type the following formula in the cell E5

➤ Press ENTER
➤ Drag down the Fill Handle Tool

As a result, we will get TRUE when the Cost Prices are less than or equal to the Selling Prices otherwise FALSE

Step-02:
Now, it is the turn to compare the text values with Less than or Equal to the operator
➤ Enter the following formula in the cell F5

With this Less than or Equal to operator, it will compare if the values of the texts of the Product column are less than or equal to the text “apple” or not.

So, while comparing Avocado with apple, as the first letter is A for both cases then it will compare the next letter which is v for Avocado and p for apple and here v>p (according to the values serially) and so Avocado will not be less than or equal to apple. For this reason, the result will be FALSE

➤ Drag down the Fill Handle Tool

In the same way, for Orange, Strawberry, Banana, Kiwi, Tomato the first letters are not less than or equal to A and so it is returning FALSE for these products. For Almond, the second letter l is less than p so it is TRUE and for Apple, it is equal to the text so it is also returning TRUE.

Method-7: Using Equal to Operator to Compare Boolean Values and Numbers

Here, we have taken some random numbers and two boolean values to show the comparison procedure between these two types of data with the help of the Equal to Operator.

Steps:
➤ Type the following formula in the cell D5

Here, B5 is the numeric value and C5 is the boolean value. By using double negation before it we will convert TRUE into 1 and FALSE into 0.

So, for 1, TRUE and 0, FALSE it will return TRUE otherwise FALSE.

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Result:
So, we can see that, for 1, TRUE and 0, FALSE it will return TRUE otherwise FALSE.

Read More: Excel Boolean Operators: How to Use Them?

Method-8: Using Equal to Operator for Dates

Here, we will compare the dates of the Order Date column with today’s date by using the TODAY function and Equal to the operator.

Steps:
➤ Type the following formula in the cell D5

TODAY() will return today’s date (1/27/2022) and then it will be compared with the dates of the Order Date column.

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Result:
In this way, you will get TRUE for the dates which are equal to today’s date 1/27/2022

Method-9: Using Logical Operators with IF Function

For calculating profit by subtracting the Selling Price from the Cost Price or loss by subtracting the Cost Price from the Selling Price, you can use the Greater than or Equal to operator and the IF function.

Steps:
➤ Type the following formula in the cell E5

Here, C5 is the Cost Price and D5 is the Selling Price, when the Cost Price is greater than or equal to Selling Price then IF will subtract Cost Price from Selling Price otherwise it will subtract Selling Price from Cost Price.

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Result:
In this way, you will get the Profit or Loss for the products in the Profit/Loss column.

Method-10: Using Logical Operators in Formula

Unlike the previous method here, we will do the same task by using only logical operators without the IF function.

Steps:
➤ Type the following formula in the cell E5

Here, C5 is the Cost Price and D5 is the Selling Price

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Result:
Afterward, you will get the Profit or Loss for the products in the Profit/Loss column.

Method-11: Using Logical Operators with Conditional Formatting

In this section, we will use the Greater than or Equal to operator with Conditional Formatting to highlight the cells when the Cost Price is greater than or equal to the Selling Price.

Steps:
➤ Select the cell range on which you want to apply the Conditional Formatting
➤ Go to Home Tab>>Conditional Formatting Dropdown>>New Rule Option.

Then, the New Formatting Rule Wizard will appear.
➤ Select Use a formula to determine which cells to format option.
➤ Type the following formula in the Format values where this formula is true box and click on Format

After that, the Format Cells Dialog Box will open up.
➤ Select Fill Option
➤ Choose any Background Color and click on OK.

After that, the Preview Option will be shown as below.
➤ Press OK

Result:
In this way, you will be able to highlight the cells where the Cost Prices are greater than or equal to the Selling Prices.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, we tried to cover some of the ways of using logical operators in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Further Readings

• Reference Operator in Excel
• How to Use Comparison Operators in Excel
• What is the Order of Operations in Excel