How to Use Logical Operators in Excel (11 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Logical Operators in Excel


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.

Logical Operators in Excel


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.

Logical Operators in Excel

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

=C5=D5

Equal to

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Equal to

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.

Equal to

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

=B5= “apple”

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)

Equal to

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Equal to

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

Logical Operators in Excel


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.

Logical Operators in Excel

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

=C5<>D5

Not Equal to

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Not Equal to

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

Not Equal to

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

=B5<> “apple”

Not Equal to

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Not Equal to

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

Logical Operators in Excel


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

Logical Operators in Excel

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

=C5>D5

Greater than

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Greater than

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

Greater than

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

=B5> “apple”

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

Greater than

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

Logical Operators in Excel

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.

Logical Operators in Excel

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

=C5<D5

Less than

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Less than

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

Less than

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

=B5< “apple”

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

Less than

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

Logical Operators in Excel

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.

Logical Operators in Excel

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

=C5>=D5

Greater than or Equal to

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Greater than or Equal to

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

Greater than or Equal to

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

=B5>= “apple”

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

Greater than or Equal to

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

Logical Operators in Excel

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.

Logical Operators in Excel

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

=C5<=D5

Less than or Equal to

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Less than or Equal to

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

Less than or Equal to

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

=B5<= “apple”

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

Less than or Equal to

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

Logical Operators in Excel


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.

Logical Operators in Excel

Steps:
➤ Type the following formula in the cell D5

=B5=--C5

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.

Numbers and Boolean values

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Numbers and Boolean values

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

Numbers and Boolean values

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.

Logical Operators in Excel

Steps:
➤ Type the following formula in the cell D5

=C5=TODAY()

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

Dates

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Dates

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

Dates


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.

Logical Operators in Excel

Steps:
➤ Type the following formula in the cell E5

=IF(C5>=D5,C5-D5,D5-C5)

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.

IF function

➤ Press ENTER
➤ Drag down the Fill Handle Tool

IF function

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

IF function


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.

Logical Operators in Excel

Steps:
➤ Type the following formula in the cell E5

=(C5>=D5)*(C5-D5)+(C5<D5)*(D5-C5)

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

  • (C5>=D5) → FALSE
  • (C5-D5) → -1855
  • (C5>=D5)*(C5-D5) becomes
     FALSE *(-1855)
    Output → 0
  • (C5<D5) → TRUE
  • (D5-C5) → 1855
  • (C5<D5)*(D5-C5) becomes
    TRUE *(1855)
    Output → 1855
  • (C5>=D5)*(C5-D5)+(C5<D5)*(D5-C5) becomes
    0+1855 1855

Formula

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Formula

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

Logical Operators in Excel


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.

Logical Operators in Excel

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

conditional formatting

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

=$C5 >= $D5

conditional formatting

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

conditional formatting

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

conditional formatting

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.

Logical Operators in Excel


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.

practice


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


<< Go Back to Excel Operators | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo