What is OR Function in Excel?

It returns either of the following outcomes:

  • True, if any of the conditions is true or all the conditions are trueFalse, if all the conditions are false

For example, the formula “=OR(2>4,8>5)” (ignore the double quotation marks) returns “true.” The first condition (2>4) evaluates to false, while the second condition (8>5) evaluates to true. Since one of the conditions is true, the final output is “true.”

A condition is supplied as an argument to the OR function. An argument can consist of numbers, text strings, cell references, formulas or Boolean values (true and false). The purpose of using the OR function is to assess whether any of the various conditions is met or not. It also facilitates multiple comparisons of data values.

Syntax of the OR Function of Excel

The syntax of the OR function in excel is given in the following image:

The OR function accepts the following arguments:

  • Logical1: This is the first condition to be tested.Logical2: This is the second condition to be tested.

“Logical1” is required, while “logical2” and the subsequent “logical” arguments are optional.

The OR function can be used either alone or in combination with the IF function of Excel. The benefits of using the OR function are expanded when it is combined with the AND and IF functions of ExcelIF Functions Of ExcelIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria. read more. For such combinations, the OR and/or AND functionsAND FunctionsThe AND function in Excel is classified as a logical function; it returns TRUE if the specified conditions are met, otherwise it returns FALSE.read more can be nested within the IF function.

Let us consider some examples to understand the working of the OR function of Excel. To know more about the nested OR function, refer to example #4 of this article.

Note 1: Each argument (condition) of the OR function must necessarily evaluate to either “true” or “false.” Moreover, each condition should consist of the relevant logical (or comparison) operator. There are six logical operators in ExcelLogical Operators In ExcelLogical operators in excel are also known as the comparison operators and they are used to compare two or more values, the return output given by these operators are either true or false, we get true value when the conditions match the criteria and false as a result when the conditions do not match the criteria.read more. These are “equal to” (=), “not equal to” (<>), “greater than” (>), “greater than or equal to” (>=), “less than” (<), and “less than or equal to” (<=).

Note 2: A maximum of 255 conditions can be tested in Excel 2007 and the newer versions. In the earlier versions of Excel, a total of 30 conditions can be tested.

Example #1–Test Two Conditions Containing Numeric Values

The succeeding image shows a number and the name of an unavailable website in cells A1 and A2 respectively. With the help of the OR function of Excel, evaluate the following conditions together:

  • The value of cell A1 is less than 10The value of cell A1 is equal to 40

The steps to test the given conditions by using the OR function in excel are listed as follows:

Step 1: Insert the required logical operators in the given conditions. So, enter the following formula in cell C1.

“=OR(A1<10,A1=40)”

Here, the first condition is A1<10 and the second condition is A1=40.

Step 2: Press the “Enter” key. The output in cell C1 is “false.” This is because both the given conditions are false. Neither the value of cell A1 is less than 10, nor is it equal to 40.

The formula and the output are shown in the following image.

Example #2–Test Two Conditions Containing a mix of Numerical and Textual Values

Working on the dataset of example #1, we want to evaluate the following conditions together:

  • The value of cell A1 is equal to 45The value of cell A2 is “Google.”

Use the OR function of Excel.

The steps to test the given conditions with the help of the OR function are listed as follows:

Step 1: Enter the following formula in cell C1.

“=OR(A1=45,A2=“Google”)”

We have inserted the appropriate logical operators in the given conditions. So, the first condition is A1=45 and the second condition is A2=“Google.”

Step 2: Press the “Enter” key. The output appears as “true” in cell C1.

In this example, the first condition is true as the value of cell A1 is equal to 45. The second condition is false as the value of cell A2 is not “Google.” Since one of the two conditions evaluate to true, the final output is “true.”

Example #3–Test Three Conditions Containing a mix of Numerical and Textual Values

Working on the dataset of example #1, evaluate the following three conditions together:

  • The value of cell A1 is greater than or equal to 5The value of cell A1 is equal to 35The value of cell A2 is “e2esols.com.”

Step 1: Insert the logical operators that fit the given conditions. So, enter the following formula in cell C1.

“=OR(A1>=5,A1=35,A2=“e2esols.com”)”

Here, the first, second, and third conditions are “A1>=5,” “A1=35,” and A2=“e2esols.com” respectively.

Step 2: Press the “Enter” key. The output in cell C1 is “true.”

The first condition evaluates to true because 45 (value of cell A1) is certainly greater than 5. The second condition is false as 45 is not equal to 35. The third condition is true as the value of cell A2 is “e2esols.com.”

So, two out of three conditions are true. Consequently, the final output is also “true.” Even if one of the three conditions had been true, the final output would still have been “true.”

The formula (entered in step 1) and the output are shown in the following image.

Example #4–Test Three Conditions With a Nested OR or Nested AND Within the IF Function

The succeeding table shows some random numbers in column A. In column B, the following three conditions are tested:

  • The value of cell A2 is less than 50The value of cell A3 is not equal to 75The value of cell A4 is greater than or equal to 100

The preceding conditions have been tested by using the following functions (in the given sequence):

  • OR functionAND functionIF and OR functionsIF and AND functionsIF And AND FunctionsThe IF AND excel formula is the combination of two different logical functions often nested together that enables the user to evaluate multiple conditions using AND functions. Based on the output of the AND function, the IF function returns either the “true” or “false” value, respectively.
  • read more

Column C shows the outputs obtained by each formula of column B. The row numbers are displayed under the label “Sr. No,” which stands for serial number.

Consider each column and row of the table as the respective column and row of an Excel worksheet.

The conditions tested in column B and their outcomes are listed as follows:

  • A2<50: It evaluates to false as the value of cell A2 (50) is not less than 50.A3<>75: It evaluates to true because the value of cell A3 (25) is not equal to 75.A4>=100: It evaluates to false as the value of cell A4 (99) is neither greater than nor equal to 100.

The rows of column B are explained as follows:

Row 2: In this row, the three preceding conditions are tested (in cell B2) using the OR excel function. Since one of the three conditions is true, the final output of the OR function (in cell C2) is “true.”

Row 3: In this row, the three preceding conditions are tested (in cell B3) using the AND function. Since two out of three conditions are false, the final output of the AND function (in cell C3) is “false.”

Row 4: In this row, the given conditions are evaluated (in cell B4) using the nested OR function. Since the output of the OR function is “true” (as explained for row 2), the IF function considers that the logical test (condition) has been met. Consequently, the IF function returns the string defined as the “value_if_true” argument, which is “data error” (in cell C4).

Row 5: In this row, the three stated conditions are evaluated (in cell B5) using the nested AND function. The AND function returns the output “false” (as explained for row 3). So, the IF function realizes that the logical test has not been met. As a result, the IF function returns the string “data correct” (in cell C5), which is the “value_if_false” argument.

Note 1: The AND function returns “true” if all the tested conditions evaluate to true. It returns “false” if any of the conditions evaluate to false. Like the OR function, the AND function is also a logical function of Excel.

Note 2: In nesting functions, one function is placed inside another function. The function that is inside is known as a nested function. Excel calculates the nested function first, followed by the outer function. The output of the nested function is used as an argument of the outer function.

Frequently Asked Questions

This has been a guide to the OR function of Excel. Here we discuss how to use the OR formula in excel along step by step examples. You may also look at these useful functions in Excel–

Yes, Excel does have an OR function. The OR function is available in all versions of Excel. However, there are certain limitations related to the number of conditions that can be tested with the OR function.Multiple conditions can be tested simultaneously by using the OR function of Excel. It is a logical function of Excel, which returns either of the two Boolean values (true and false) as the outcome. The OR function returns “true” if any or all conditions are true. It returns “false” if all conditions are false.Note: For the meaning of condition and the number of conditions that can be tested, refer to the introduction and syntax discussed in this article.

The steps to use the OR function with the conditional formatting feature are listed as follows:a. Select the range on which conditional formatting needs to be applied.b. From the Home tab, click the “conditional formatting” drop-down from the “styles” group. Select “new rule.”c. The “new formatting rule” window opens. Choose the option “use a formula to determine which cells to format” under “select a rule type.”d. Under “format values where this formula is true,” enter the OR formula. For instance, in the range A2:A10, if the numbers greater than 30 or equal to 25 need to be formatted, enter the formula “=OR(A2>30,A2=25)” (without the beginning and ending double quotation marks).e. Click “format” and select a color from the “fill” tab. Click “Ok” to proceed.f. Click “Ok” again in the “new formatting rule” window.The cells of the selected range, for which either of the two conditions (A2>30,A2=25) is true, are colored. The cells, for which both conditions are false, are not colored.Note 1: When a conditional formatting rule is applied using the OR function, every cell is checked for multiple conditions entered in step “d.” Each cell evaluates to either “true” or “false” and accordingly, formatting is applied. No cell can evaluate to both “true” and “false” at the same time.Remember that with the OR function, a cell evaluates to “true” if any of the conditions is true. A cell evaluates to “false” if all conditions are false.Note 2: One can enter more than two conditions in step “d.” Ensure that each condition is separated with commas, as shown by the syntax of the OR function.

To use the OR function with the IF function, insert the former (containing the conditions) within the latter. The text string of each condition must be enclosed within double quotation marks.For instance, the ranges A2:A11 and B2:B11 contain the names of flowers and trees respectively. The formula “=IF(OR(A2=”rose”,B2=”maple”),”Yes”,”No”)” returns the following outcomes:a. Yes, if either the first condition (A2=”rose”) or the second condition (B2=”maple”) is true.b. No, if both conditions are false.Hence, by combining the OR and IF functions, one can evaluate multiple conditions simultaneously and obtain customized responses for each row. These responses depend on the fulfillment or non-fulfillment of the stated conditions.Note: Enter the IF and OR formula in Excel by excluding the beginning and ending double quotation marks. Press the “Enter” key after entering the given formula. Drag the formula if the outputs for the entire range are required.

  • OR Function in VBAOR Function In VBAOr is a logical function in programming languages, and we have an OR function in VBA. The result given by this function is either true or false. It is used for two or many conditions together and provides true result when either of the conditions is returned true.read moreTrue FunctionTrue FunctionIn Excel, the TRUE function is a logical function that is used by other conditional functions such as the IF function. If the condition is met, the output is true; if the conditions are not met, the output is false.read moreNOT Excel Function | ExamplesNOT Excel Function | ExamplesNOT Excel function is a logical function in Excel that is also known as a negation function and it negates the value returned by a function or the value returned by another logical function.read moreSearch Function in ExcelSearch Function In ExcelSearch function gives the position of a substring in a given string when we give a parameter of the position to search from. As a result, this formula requires three arguments. The first is the substring, the second is the string itself, and the last is the position to start the search.read moreYEARFRAC in ExcelYEARFRAC In ExcelYEARFRAC Excel is a built-in Excel function that is used to get the year difference between two date infractions. This function returns the difference between two dates infractions like 1.5 Years, 1.25 Years, 1.75 Years, etc. So using this function we can find the year difference between two dates accurately.read more