Catalog

How to Use Or Function in Excel: Step-by-step Guide

August 21, 2023 474 views

We use the Excel OR function to perform logical tests involving multiple conditions. It allows us to check if at least one of the conditions is true, making it useful for scenarios where we want to determine if any of the given conditions hold.

The primary benefit of using the OR function is that it simplifies complex logical evaluations, especially when working with large datasets and multiple criteria. Instead of writing lengthy nested IF statements, the OR function allows us to create more concise and readable formulas.

For example, if you have a list of items with different attributes, you might use the OR function to check if any items meet specific criteria. Additionally, it can be helpful when creating data validation rules, conditional formatting rules, or filtering data based on multiple conditions.

Overall, the OR function enhances the efficiency and flexibility of spreadsheet calculations and enables users to make informed decisions based on evaluating multiple conditions simultaneously.

In this article, we will introduce what the OR function is and how to use it. Moreover, we will recommend the perfect software for you.

Part 1. What is OR Function in Excel?

In Excel, the OR function is a logical function that allows you to check multiple conditions simultaneously and returns TRUE if at least one of the conditions is true; otherwise, it returns FALSE. The syntax for the OR function is quite straightforward:

=OR(condition1, [condition2], [condition3], ...)

You can include up to 255 conditions within the OR function. Simply list the conditions separated by commas within the parentheses.

Part 2. How to Use OR Function in Excel?

Let's go through a step-by-step tutorial on how to use the OR function in Excel with three practical examples.

Example 1: Checking if a Cell Contains Specific Text Suppose you have a list of fruits in column A, and you want to check if any cell in that column contains either "Apple" or "Orange." Here's how you can do it using the OR function:

In an empty cell, let's say cell B1, type the following formula:

=OR(A1="Red", A1="Blue")

Press Enter to apply the formula. The result will be either TRUE or FALSE. If cell A1 contains "Red" or "Blue," the formula will return TRUE; otherwise, it will return FALSE.

Drag the formula to apply it to the rest of the cells in column B to check the entire list.

Example 2: Data Validation with OR Function Suppose you have a list of scores in column A, and you want to validate if any score is either greater than 85 or less than 55. Here's how to use the Excel OR function for data validation:

Step 1: Select the range of cells you want to apply the data validation to (e.g., A1:A10).

Step 2: Go to the "Data" tab in the Excel ribbon.

Step 3: Click on "Data Validation."

Step 4: In the Data Validation dialog box, choose "Custom" from the "Allow" dropdown.

Step 5: In the "Formula" input box, enter the following formula:

=OR(A1>85, A1<55)<>

Click "OK" to apply the data validation. Now, any cell in the selected range that meets the condition (score > 85 or score < 55) will be considered valid.

Example 3: Conditional Formatting with OR Function Suppose you have a table of students' test scores, and you want to highlight cells with a score greater than 75 or less than 50 for better visibility. Here's how you can use the OR function with conditional formatting:

Select the range of cells you want to format (e.g., C2:F11).

Go to the "Home" tab in the Excel ribbon, click on "Conditional Formatting," and choose "New Rule."

In the New Formatting Rule dialog box, select "Use a formula to determine which cells to format."

Enter the following formula in the "Format values where this formula is true" field:

=OR(C2>75, C2<50)<>

Click "Format" to set the formatting style you desire for cells meeting the condition (Let's suppose we chose orange color for our selected cells).

Click "OK" to apply the conditional formatting.

Now, any cell within the selected range of 50 to 75 will be without color according to our chosen format.

These examples demonstrate the versatility of the OR function excel with text/numbers, whether used within a cell formula, data validation, or conditional formatting. It simplifies the evaluation of multiple conditions, making data analysis and decision-making more efficient.

Trustpilot

stars

WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User

avator

Algirdas Jasaitis
logo

Part 3: Alternative Choice - WPS Office

It's worth noting that WPS Office is the best alternative choice of Microsoft due to its updates directly in the software and availability in free versions. In WPS Office, you can directly use the Excel OR function, which works just like the one in Microsoft. The syntax and usage are the same:

=OR(condition1, [condition2], [condition3], ...)

You can use the OR function in WPS Office Excel to check multiple conditions and get a TRUE or FALSE result based on whether at least one is true.

For example:

Formula

=OR(A1>40, A1<15)<>

Copy the above-mentioned formula and paste it into B2 Cell as mentioned in the Screenshot.

This formula will return TRUE if the value in cell A1 is either greater than 40 or less than 15; otherwise, it will return FALSE.

FAQs

1. Can I use the "OR" function with other logical functions in Excel?

Yes, you can use the OR function with other logical functions in Excel, such as IF, AND, NOT, and others. By combining different logical functions, you can create more complex and dynamic formulas to analyze data in various ways. For example, you could use the OR function inside an IF statement to test multiple conditions and return different results based on the outcome.

Similarly, you could use the OR function in combination with the AND function to test for more complex conditions that require multiple criteria to be met. The possibilities are endless, and Excel offers a wide range of logical functions that you can use to create powerful formulas.

2. Can I use the OR function to combine text values?

No, you cannot use the OR function to combine text values in Excel. The OR function is a logical function that evaluates whether any of the conditions provided to it are TRUE or FALSE. It does not perform any text concatenation or manipulation.

3. Are there any alternatives to the OR function in Excel?

Yes, there are several alternatives to the OR function in Excel, depending on the specific needs. Some commonly used alternatives include the AND function, NOT function, IF function, and SUMPRODUCT function. The AND function returns TRUE only if all the conditions are true, while the NOT function returns the opposite of the logical value of a given condition.

The IF function can test multiple conditions and return values based on the result. In contrast, the SUMPRODUCT function can be used to test multiple conditions and return the numerical result of the calculation. The choice of function depends on the logic and requirements of the specific case.

Excel OR Function - Wrap Up

As we have discussed in our article with examples, the Excel OR function is a logical function that allows users to check multiple conditions simultaneously. Furthermore, you have observed that the OR function serves the same purpose in both Applications (i.e., Microsoft and WPS Office).

According to our deep comparison, WPS Office is a cost-effective alternative to Microsoft Office, as it provides a suite of productivity tools (including WPS Excel) at a more affordable price or even for free. Therefore, users can confidently utilize the OR function in WPS Office with the same ease and effectiveness as in Microsoft Excel for their spreadsheet needs.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.