Excel AND and OR Functions

Test multiple conditions with Excel's AND and OR functions

Arrow Signs
Allow Excel to make decisions with the OR and AND Functions. jayk7 / Getty Images

The AND and OR functions are two of Excel's better known logical functions; they test to see whether the output from two or more target cells meets conditions that you specify. Excel decision making can be further enhanced using the IF function when the need to meet multiple criteria arise.

TRUE or FALSE Values

Screenshot of Excel showing AND/OR/IF Functions

One feature of both OR and AND functions is that they will only return or display one of two results or Boolean values in the cell where they are located: TRUE or FALSE.

  • For the OR function (row 2 above): Multiple conditions are tested and if any one of the tested conditions is true, then the OR function returns an answer of TRUE. Only if all conditions are not true will OR give you a FALSE value.
  • For the AND Function (row 3 above): Multiple conditions are tested and only if all of the conditions are true will the function return a TRUE response. If not, the function returns FALSE as a value.

Combining with Other Functions

Screenshot of Excel showing IF Statements

TRUE or FALSE values can be displayed "as is" in the cells where the functions are located. The functions can also be combined with other Excel functions, such as the IF function, in rows 4 and 5 above to give a variety of results or carry out a number of calculations.

How Each Function Works

Screenshot of Excel showing OR and AND Functions

In the image above, cells B2 and B3 contain an AND and OR function respectively. Both use a number of comparison operators to test a variety of conditions for the data in cells A2, A3, and A4 of the worksheet.

The two functions are as follows:

= AND(A2<50,A3<>75,A4>=100)
= OR(A2<50,A3<>75,A4>=100)

The conditions they test are:

  • If the data in cell A2 is less than 50 (< is the symbol for less than).
  • If the data in cell A3 is not equal to 75 (<> is the symbol for not equal to).
  • If the data in cell A4 is greater than or equal to 100 (>= is the symbol for greater than or equal to).

For the AND function in cell B3, the data in cells (A2 to A4) must match all three of the conditions above for the function to return a TRUE response. As it stands, the first two conditions are met, but since the value in cell A4 is not greater than or equal to 100, the output for the AND function is FALSE.

In the case of the OR function in cell B2, only one of the conditions above needs to be met by the data in cells A2, A3, or A4 for the function to return a TRUE response. In this example, the data in cells A2 and A3 both meet the required condition so the output for the OR function is TRUE.

Function Syntax and Arguments

OR and AND Function Syntax

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. The syntax for the OR function is:

= OR( Logical1 , Logical2 , ... Logical255 )

The syntax for the AND function is:

= AND ( Logical1 , Logical2 , ... Logical255 )

Logical1 (required): Refers to the condition being tested. The form of the condition is normally the cell reference of the data being checked followed by the condition itself, such as A2 < 50.

Logical2, Logical3, ... Logical255 (optional): Additional conditions that can be tested up to a maximum of 255.

Entering the OR Function

Screenshot of Excel showing the OR Function

The steps below cover how to enter the OR function located in cell B2 in the image above. The same steps can be used for entering the AND function located in cell B3.

=OR(A2<50,A3<>75,A4>=100)

Although it is possible to type the entire formula manually into a worksheet cell, another option is to use the Formula Builder, as outlined in the steps below, to enter the function and its arguments into a cell such as B2. Advantages include Excel taking care of separating each argument with a comma and it encloses all arguments in parenthesis.

  1. Click on cell B2 to make it the active cell — this is where the AND function will be located.
  2. Click on the Formulas tab of the ribbon.
  3. Click on the Logical icon to open the function drop down.
  4. Click on OR in the list to open the Formula Builder.
  5. Click on Logical1 line.
  6. Click on cell A2 in the worksheet to enter this cell reference.
  7. Type < 50 after the cell reference.
  8. Click on Logical2 line.
  9. Click on cell A3 in the worksheet to enter the second cell reference.
  10. Type <> 75 after the cell reference.
  11. Click on Logical3 line.
  12. Click on cell A4 in the spreadsheet to enter the third cell reference.
  13. Type >=100 after the cell reference.
  14. Click Done to complete the function.

The value TRUE should appear in cell B2 because the data in cell A3 does meet the condition of not being equal to 75. When you click on cell B2, the complete function appears in the formula bar above the worksheet.

Trying the AND Function

Screenshot of Excel showing the AND Function

As mentioned, the steps above can also be used for entering the AND function located in cell B3 in the worksheet image above. The completed AND function would be as follows: 

=AND(A2<50,A3<>75,A4>=100)

A value of FALSE should be present in cell B3 since only one of the conditions being tested needs to be false for the AND function to return a FALSE value. In this example two of the conditions are false:

  • The data in cell A2 is not less than 50.
  • The data in cell A4 is not greater than or equal to 100.