Excel AND and OR Functions

Test multiple conditions with Excel's AND and OR functions

An illustration of arrows on solid backgrounds.

jayk7/Getty Images

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

Note: The instructions in this article apply to Excel 2019, 2016, 2013, 2010; and Excel for Mac.

TRUE or FALSE Values

Screenshot of Excel showing AND/OR/IF Functions

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

  • For the OR function (see row 2 above), multiple conditions are tested. If any one of the tested conditions is true, the OR function returns an answer of TRUE. If all conditions are not true, OR gives a FALSE value.
  • For the AND function (see row 3 above), multiple conditions are tested. If all of the conditions are true, the function returns a TRUE response. If not, the function returns FALSE as a value.

Combine with Other Functions

Screenshot of Excel showing IF Statements

TRUE or FALSE values display "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 (shown 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 OR and AND function. 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:

=OR(A2<50,A3<>75,A4>=100)
=AND(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 conditions for the function to return a TRUE response. The first two conditions are met. Because the value in cell A4 is not greater than or equal to 100, the output for the AND function is FALSE.

For the OR function in cell B2, only one condition 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

An image showing 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 the cell reference of the data being checked followed by the condition itself, such as A2 < 50.

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

Enter 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 a dialog box, 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. Open a blank Excel worksheet and enter the following data.

    A screenshot showing example data use to test multiple conditions in Excel
  2. Select cell B2 to make it the active cell. This is where the OR function will be located.

  3. Select Formulas.

  4. Select Logical to open the function drop-down list.

  5. Select OR to open the Function Arguments dialog box. If you use a Mac, the Formula Builder opens.

  6. Place the cursor in the Logical1 text box.

  7. Select cell A2 in the worksheet to enter this cell reference.

  8. In the dialog box, enter <50 after the cell reference.

  9. Place the cursor in the Logical2 text box.

  10. Select cell A3 in the worksheet to enter the second cell reference.

  11. Enter <>75 after the cell reference.

  12. Place the cursor in the Logical3 text box.

  13. Select cell A4 in the worksheet to enter the third cell reference.

  14. Type >=100 after the cell reference.

    A screenshot showing how to create a formula to test multiple conditions in Excel
  15. Select OK. Except in Excel for Mac, where you select Done.

The value TRUE appears in cell B2 because the data in cell A3 meets the condition of not being equal to 75. When you select cell B2, the complete function appears in the formula bar above the worksheet.

Try the AND Function

The steps above can also be used to enter the AND function located in cell B3 in the worksheet image above. The completed AND function is: 

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

A value of FALSE appears in cell B3 because 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.