How to do Conditional Formatting with Multiple Conditions in Excel

This guide will explain how to set up conditional formatting that checks for multiple conditions in Excel.

We can use custom Excel formulas to indicate what conditions to check for conditional formatting. We’ll need different functions depending on whether we want our cells to follow all conditions or at least one.

Conditional formatting is a useful feature in Excel that enables you to format certain cells that meet certain criteria.

For example, you may want to flag values in a range that fall below some predetermined value. We can add conditional formatting to the range to change the cell’s fill color when the cell’s value is less than some value.

However, you may want to set up conditional formatting that uses more than one criterion. 

Let’s take a look at a simple use case where you need to check multiple conditions when formatting the cell.

Suppose you have a spreadsheet that tracks the number of times each employee is absent from the office. The HR department will use this tracker to determine which employees require a verbal warning. 

You want to highlight employees who have 1 to 3 instances of absence.

We can use conditional formatting to flag employees who meet the following criteria: has been absent at least once and has not been absent more than 3 times. We can create a custom formula to use as a rule for conditional formatting. 

Now that we have an idea of when to add conditional formatting with multiple conditions, let’s learn how to set it up on an actual sample spreadsheet.

 

 

A Real Example of Using Conditional Formatting with Multiple Conditions in Excel

The following section provides several examples of tables with conditional formatting based on multiple conditions. We will also explain the formulas and tools used in these examples.

First, let’s take a look at our sample data. Our table is a monthly breakdown of a clothing store’s revenue.

sample data

 

We want to highlight months where the revenue is between $8000 and $9000.

valuse between 8000 and 9000

 

One way to achieve the conditional formatting above is by using the AND function:

=AND($H2>8000,$H2<9000)

We can also use the OR function if we want a cell to be highlighted if either condition is met.

In the example below, we wanted to highlight all values in column B that are either above 9000 or below 8000.

conditional formatting with multiple conditions in Excel

 

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

Use our sample spreadsheet to take a closer look at how we set up the conditional formatting rules for each of our sample ranges.

If you’re ready to try multiple conditions for conditional formatting, head over to the next section to read our step-by-step breakdown on how to do it!

 

 

How to Do Conditional Formatting with Multiple Conditions in Excel

This section will guide you through each step needed to set up conditional formatting with multiple conditions in Excel.

Follow these steps to start checking for multiple conditions:

  1. First, select the range you want to apply conditional formatting to.
    select range you want to add conditional formatting to
    In this example, we want to apply conditional formatting to the range
    B2:B13. We want to highlight all cells that are above $9000 and below $8000 in revenue.
  2. In the Home tab, click on the Conditional Formatting option. Select the New Rule… option to add a new rule to the selected range.
    create new rule
  3. In the New Formatting Rule dialog box, the user must select a rule type. Since we want to apply formatting to cells that are either below or above a certain range, we’ll need to select the ‘Format only cells that contain’ option.
    add first condition for conditional formatting
    Afterwards, use the provided dropdown boxes and text boxes to edit the rule description. In the example above, we’ll start by describing the rule that indicates we’re looking for values less than 8000.

    Next, click on Format… to set up how you want the cells to be formatted.
  4. Users can format cells in various ways. In this example, we want to highlight cells by changing the cell’s fill background color to red.
    set up how cells are formatted
  5. Once you’ve applied the first condition, repeat the previous steps to enter the second condition.
    create 2nd formatting rule
    In the example above, we’ve added a rule that checks if the cell’s value is greater than 9000.
  6. After adding both rules, the range should now have conditional formatting with multiple conditions.
    conditional formatting with multiple conditions in Excel
  7. Another way to perform the above example is by using the OR function.
    Select the option Use a formula to determine which cells to format. We can use the OR function to list two or more conditions.
    use OR function for formatting rule
    The OR function is useful for cases where we want our highlighted cells to follow any of the conditions. It is not necessary for the cell to follow all conditions to be formatted.
    conditional formatting with multiple conditions in Excel
  8. If we want to apply formatting only if all conditions are met, we can instead use the AND function.
    use AND function
    In this example, we will only highlight cells in column H that have a value of less than 9000 and more than 8000.

    conditional formatting with multiple conditions in Excel
    Since only 8048 meets both conditions, cell H4 is the only cell highlighted in the range.

These are all the steps you need to perform to set up conditional formatting with multiple conditions.

 

 

We’ve shown how to use Excel formulas as the basis for conditional formatting. You should now have a clear understanding of when to use the OR and AND functions for this purpose.

This step-by-step guide should provide you with all the information you need to add conditional formatting to your Excel sheet that checks for multiple conditions.

We’ve shown you how to add multiple formatting rules to the same column. We’ve also explained how to use the AND and OR functions to handle different types of logical rules for your conditional formatting.

Conditional formatting is just one example of the many Excel features you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like