What are logical functions in Excel?
Logical functions in Excel help perform a diverse set of logical operations, from setting most basic conditions and linking them with different outcomes to performing complicated scenario analysis. Logical functions significantly expand the functionality of other functions in Excel, helping a user to build sophisticated models and leverage massive sets of input data.
Which logical functions in Excel are most widely used?
While all logical functions are extremely helpful in different situations, the following 4 are the most frequently used:
 IF – to set different outcomes depending on whether a given condition is true or false
 AND – to check whether several conditions are true at the same time
 OR – to check whether at least one of several conditions is true
 NOT – to return the value opposite to the value of a given condition (return FALSE if condition if TRUE and vice versa)
IF function is clearly the major one in this category while most others are usually used in conjunction with IF.
Sample task
Let’s say you’re looking for a new apartment. You got a long list of potential options from your agent and need to decide which apartments to visit (see Figure 1). Your maximum budget is $1500, you don’t want the apartment to be smaller than 900 square feet, and you prefer not to live on the first floor. However, at a bargain price you are ready to consider the first floor as an option.
Let’s start building those conditions in a formula one by one.
How to use IF function
First, let’s set a condition that target apartment rent shouldn’t exceed our $1500 budget:
 Open IF function by typing =IF( in your target cell (cell G3 in our example above)
 Define the condition to test
 Select or type in a cell with the rent amount: D3
 Type in the condition for that cell: <=1500

Define the value to return if the condition is true
 Type a comma to move to the next argument of IF function
 Type “yes”
 Define the value to return if the condition is false
 Type a comma to move to the next argument of IF function
 Type “no”
 Close IF function by typing right bracket
 Press Enter and copy the formula to other target cells (e.g., by pressing Ctrl + C, selecting target cells, and pressing Enter)
=IF(D3<=1500,”yes”,”no”) – the final formula in cell G3
As you can see in Figure 2, the formula filtered out item 3 in our list, because the price of $1600 is above our $1500 budget.
Alternatively for steps 1 – 5 above, you can also use the Insert Function command (Figure 3):
 Click on Insert Function link (f_{x} sign to the left of the formula bar)
 Fill in the parameters of IF functions in appropriate fields
 Click OK
How to use AND function
Now, let’s update our formula a little to include minimum apartment size of 900 square feet (Figure 4):
 Select cell G3 and click on the formula bar to edit it
 Substitute our current condition with a more sophisticated formula including AND function:
 Open AND function before our existing D3<=1500 condition by typing AND(
 Add the second parameter to the AND function
 Type a comma to move to the next parameter of AND function
 Select a cell with square footage by clicking on it or by typing E3
 Type the square footage condition: >=900
 Close the AND function by typing right bracket
 Press Enter and copy the formula to other target cells (e.g., by pressing Ctrl + C, selecting target cells, and pressing Enter)
=IF(AND(D3<=1500,E3>=900),”yes”,”no”) – updated formula in cell G3 (updates bolded)
How to use NOT function
The next step would be to specify that we don’t regularly consider apartments on the first floor (Figure 5):
 Select cell G3 and click on the formula bar to edit it
 Substitute our current condition by adding the third parameter to our existing AND function:
 Type a comma to move to the next parameter of AND function
 Open NOT function by typing NOT(
 Select a cell with the floor number by clicking on it or by typing F3
 Type in the floor number condition: =1
 Close NOT function by typing right parenthesis
 Press Enter and copy the formula to other target cells (e.g., by pressing Ctrl + C, selecting target cells, and pressing Enter)
=IF(AND(D3<=1500,E3>=900,NOT(F3=1)),”yes”,”no”) – updated formula in cell G3 (updates bolded)
How to use OR function
Finally, let’s clarify that we are ready to consider firstfloor apartments when the price is low enough (Figure 6):
 Select cell G3 and click on the formula bar to edit it
 Substitute our last parameter of the AND function with a more sophisticated condition using OR:
 Open OR function before our existing NOT(F3=1) condition by typing OR(
 Add the second parameter to our new OR function
 Type a comma to move to the next parameter of the OR function
 Select a cell with monthly rent by clicking on it or by typing D3
 Type in the maximum rent condition: <=1300
 Close OR function by typing right parenthesis
 Press Enter and copy the formula to other target cells (e.g., by pressing Ctrl + C, selecting target cells, and pressing Enter)
=IF(AND(D3<=1500,E3>=900,OR(NOT(F3=1),D3<=1300)),”yes”,”no”) – updated formula in cell G3 (updates bolded)
Logical Functions Quiz
Take a quick quiz to understand how well you know logical functions in Excel: