OR Function in Excel

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

What is OR Function in Excel?

The OR function of Excel is used to test multiple conditions at the same time. A condition is an expression that evaluates to either “true” or “false” but not to both simultaneously. The OR function is categorized as a Logical function of Excel.

It returns either of the following outcomes:

True, if any of the conditions is true or all the conditions are true
False, 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.

What is OR Function in Excel?

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 Excel. For such combinations, the OR and/or AND functions 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 Excel. 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 10
The 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 45
The 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.”

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

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 5
The value of cell A1 is equal to 35
The value of cell A2 is “e2esols.com.”

Use the OR function of Excel.

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

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 50
The value of cell A3 is not equal to 75
The 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 function
AND function
IF and OR functions
IF and AND functions

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.

Sr. No	A	B	C
1	DATA	FORMULAS APPLIED	RESULTS
2	50	=OR(A2<50,A3<>75,A4>=100)	TRUE
3	25	=AND(A2<50,A3<>75,A4>=100)	FALSE
4	99	=IF(OR(A2<50,A3<>75,A4>=100),"Data Error","Data Correct")	Data Error
5		=IF(AND(A2<50,A3<>75,A4>=100),"Data Error","Data Correct")	Data Correct

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

1. State whether Excel has an OR function or not. If yes, define it.

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.

2. How is the OR function of Excel used with the conditional formatting feature of Excel?

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.

3. How is the OR function used on text strings with the IF function of Excel?

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.