Using Conditional Formatting with OR Criteria in Excel

by

Conditional Formatting in Excel provides a great way to highlight certain cells that may be more important than others.

When combined with the OR criterion, you get added flexibility to your formatting condition options.

In this tutorial, we will see how to use conditional formatting, specifically with the OR criteria.

What is Conditional Formatting in Excel?

Conditional formatting allows you to apply particular formatting to only those cells that satisfy the given criteria.

Most often it is to apply color-based formatting to highlight, emphasize, or differentiate among data and information stored in a spreadsheet.

The great thing about conditional formatting is that it lets you specify the condition or criteria for formatting in a multitude of ways. You can use it to format:

  • Cells that contain duplicate or unique values
  • Cells that contain a particular value
  • Cells that contain a particular range of values
  • Cells that return true for a particular formula

…and more.

In this tutorial, we will be applying conditional formatting using a formula. This means that Excel will decide which cells to format based on the result of a formula.

Also read: Copy Conditional Formatting in Excel

What are Logical Functions in Excel?

Logical functions like OR, AND and NOT let you carry out more than one comparison, or test multiple conditions. The function returns a logical value based on the set of conditions, which can be either TRUE or FALSE.

The OR function returns a TRUE if any of the conditions operated on is TRUE. So, you can have an OR function with the following syntax:

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

where condition1, condition2, etc. are conditions with comparison operators like =, <, <=, >, or <=. The OR function will return TRUE even if one of the conditions is true.

Example to Explain How to Use Conditional Formatting with OR Criteria

Now let us see how to combine the two concepts discussed – Conditional Formatting with OR Criteria. To explain this, we will use a sample problem.

Using Conditional Formatting with OR Criteria

When you include an OR function in a conditional formatting rule, you can highlight cells in the table that satisfy at least one of the conditions defined in the OR function.

Let us say you have a set of Employee Names, Departments, and Sales values as shown in the screenshot below:

Dataset for Conditional Formatting

If you want to highlight all the rows where an employee is from department B or has Sales of more than $5000, you can use Conditional Formatting with OR criteria as follows:

  1. Select all the cells where you want to apply cell formatting. In our case, we select the range A2:C7.
  2. Under the Home tab, click on the Conditional Formatting button (in the Styles group).Click on Home and Then on Conditional formatting
  3. This will display a drop-down menu with different conditional formatting options. Select ‘New Rule’.Click on New Rule
  4. This will open the ‘New Formatting Rule’ dialog box.
  5. From the options under ‘Select a Rule Type’, click on the option ‘Use a formula to determine which cells to format’.Click on Use formula to determine which cells to format
  6. This will open a new section below to edit the rule description. In the input box below ‘Format values where this formula is true’, type the formula: =OR($B2=”B”,$C2>5000)Enter the OR formula
  7. Next, you need to specify what formatting you want to apply to the row if its values return TRUE for the above formula. Click on the Format button.Click on Format
  8. This will open the ‘Format Cells’ dialog box from where you can apply whatever formatting you want to apply to the selected rows.
  9. We just want to highlight the rows with a yellow fill color, so we can select the ‘Fill’ tab and select the Yellow color from the Background color options shown.Select the color for formatting
  10. Click OK to close the Format Cells dialog box.Click on OK
  11. Click OK again to close the New Formatting Rule dialog box.Click on OK again
  12. All the rows that satisfy the condition (have either the department as B or Sales more than $5000) will be highlighted with a yellow background color.Rows highlighted based on the OR formula

You can apply this method with as many conditions and as many types of conditions as you need to. You can also follow the same technique with other comparison functions like AND and NOT.

In this tutorial, I showed you step by step how to use conditional formatting with OR criteria to highlight data in a table if at least one of the given conditions is met.

I hope you can use the method explained in this tutorial for your own data and applications, to get your required formatting results.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment