How to Do Conditional Formatting for Multiple Conditions (8 Ways)

If you are looking for some of the easiest ways to do Conditional Formatting for multiple conditions in Excel, then you will find this article useful.

Sometimes it becomes necessary to highlight a row for multiple conditions while working with a large dataset in Excel. This article will help you to know the ways of doing Conditional Formatting based on another cell range.

Download Workbook

8 Ways to Do Conditional Formatting for Multiple Conditions

Here, I have the two data tables to demonstrate the ways of Conditional Formatting for multiple conditions in Excel. The first table has the sales record for different items of a company

excel conditional formatting multiple conditions

and the second one contains Order Date, Delivery Date and Sales for some items of another company.

excel conditional formatting multiple conditions

For creating the article, I have used Microsoft Excel 365 version, you can use any other versions according to your convenience.

Method-1: Conditional Formatting for Multiple Conditions for One Column

Here, we will attempt to highlight the cells of a single column based on multiple conditions on the Sales column. By using Conditional Formatting we will highlight the cells of the Sales column containing values less than $2000.00 and more than $5000.00.

excel conditional formatting multiple conditions

Step-01:
➤Select the cell range on which you want to apply the Conditional Formatting
➤Go to Home Tab>>Conditional Formatting Dropdown>>New Rule Option.

multiple conditions for one column

Then the New Formatting Rule Wizard will appear.
➤Select the Format only cells that contain option.

multiple conditions for one column

Step-02:
➤Choose the following in the Format only cells with: Option

Cell Value
less than
2000

➤Click Format Option

excel conditional formatting multiple conditions

After that, the Format Cells Dialog Box will open up.
➤Select Fill Option
➤Choose any Background Color
➤Click on OK.

multiple conditions for one column

After that, the Preview Option will be shown as below.
➤Press OK.

multiple conditions for one column

Now, you will get the cells having a value less than $2000.00 highlighted.

multiple conditions for one column

Step-03:
➤Follow Step-01 of this method.
After that, you will get the following New Formatting Rule Dialog Box.

multiple conditions for one column

➤Choose the following in the Format only cells with: Option

Cell Value
greater than
5000

➤Click Format Option

excel conditional formatting multiple conditions

After that, the Format Cells Dialog Box will open up.
➤Select Fill Option
➤Choose any Background Color
➤Click on OK.

multiple conditions for one column

Then, the Preview Option will be shown as below.
➤Press OK.

multiple conditions for one column

Result:
In this way, you will get the cells highlighted for a value less than $2000.00 and more than $5000.00.

excel conditional formatting multiple conditions

Read More: Excel Conditional Formatting on Multiple Columns

Method-2: Conditional Formatting for Multiple Conditions Using AND Function

While dealing with multiple conditions in different columns you can use the AND function, this will highlight the rows only when both conditions are met.
Assume, you want to highlight the rows which have a Sales Person named Richard and sales value greater than $5,000.00, and to do this you can use the AND function here.

excel conditional formatting multiple conditions

Step-01:
➤Select the data range on which you want to apply the Conditional Formatting
➤Go to Home Tab>>Conditional Formatting Dropdown>>New Rule Option.

AND function

Then the New Formatting Rule Wizard will appear.
➤Select Use a formula to determine which cells to format option.

AND function

➤Click on Format Option.

AND function

After that, the Format Cells Dialog Box will open up.
➤Select Fill Option
➤Choose any Background Color
➤Click on OK.

excel conditional formatting multiple conditions

After that, the Preview Option will be shown as below.

AND function

Step-02:
➤Write the following formula in the Format values where this formula is true: Box

=AND($C5="Richard",$E5>5000)

When the string of Column C will match Richard and the sales values of Column E will be Greater than 5000, then the Conditional Formatting will appear in those rows.

➤Press OK

AND function

Result:
After that, you will get a row fulfilling both conditions highlighted.

excel conditional formatting multiple conditions

Read More: Conditional Formatting with Formula for Multiple Conditions in Excel

Method-3: Conditional Formatting for Multiple Conditions for One Column Using OR Function

For dealing with multiple conditions you can use the OR function as well, unlike the AND function it will highlight the rows if any of the criteria meets.
Suppose, you want to highlight the cells of a single column based on multiple conditions on the Sales column. You can use the OR function to highlight the cells of the Sales column containing values less than $2000.00 and more than $5000.00.

excel conditional formatting multiple conditions

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

OR function for a single column

➤Type the following formula in the Format values where this formula is true: Box

=OR($E5<2000,$E5>5000)

When the sales value of Column E will be Less than 2000 or Greater than 5000, then the Conditional Formatting will appear in those rows.

➤Press OK

OR function for a single column

Result:
In this way, you will get the cells highlighted for a value less than $2000.00 or more than $5000.00.

excel conditional formatting multiple conditions

Read More: Excel Conditional Formatting Formula

Method-4: Conditional Formatting for Multiple Conditions Using OR function

For dealing with multiple conditions in different columns we will use the OR function here. We will highlight the rows which have a North Region or sales value greater than $5,000.00.

excel conditional formatting multiple conditions

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

OR function

➤Type the following formula in the Format values where this formula is true: Box

=OR($D5= “North”,$E5>5000)

When the string of Column D will match North and the sales values of Column E will be Greater than 5000, then the Conditional Formatting will appear in those rows.

➤Press OK

OR function

Result:
Afterward, you will get the rows fulfilling any one condition highlighted.

excel conditional formatting multiple conditions

Read More: How to Use Conditional Formatting in Excel [Ultimate Guide]


Similar Readings


Method-5: Conditional Formatting for Multiple Conditions Using IF function

In this section, we are using the IF function for highlighting rows that have fulfilled multiple conditions. For this purpose, we have added a column named Helper.

excel conditional formatting multiple conditions

Step-01:
➤Select the output Cell F5.
➤Type the following formula

=IF(C5="Richard",IF(D5="West",IF(E5>5000,"Matched","Not Matched"),"Not Matched"),"Not Matched")

IF will return “Matched” if the three conditions given here are met, otherwise “Not Matched”.

IF function

➤Press ENTER
➤Drag down the Fill Handle Tool.

IF function

Now, we will get Matched only for a row where all of the three conditions have met, and then we will highlight this row.

IF function

Step-02:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

IF function

➤Type the following formula in the Format values where this formula is true: Box

=$F5="Matched"

When the values of Column F will be Equal to “Matched”, then the Conditional Formatting will appear in those rows.

➤Press OK

IF function

Result:
Afterward, you will get the row fulfilling all of the conditions highlighted.

excel conditional formatting multiple conditions

Read More: Excel Conditional Formatting Formula with IF

Method-6: Using AND Function for Multiple Conditions Including a Condition for Dates

Let’s say, you want to highlight the rows which have delivery dates after today (today’s date is 12-15-21 and the date format is mm-dd-yy) and sales value greater than $5,000.00, and to do this you can use the AND function here.

excel conditional formatting multiple conditions

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

AND function for dates

➤Type the following formula in the Format values where this formula is true: Box

=AND($D5>TODAY(),$E5>5000)

When the dates of Column D will be Greater than TODAY() (gives today’s date) and the sales values of Column E will be Greater than 5000, then the Conditional Formatting will appear in those rows.

➤Press OK

AND function for dates

Result:
After that, you will get the last row fulfilling both conditions highlighted.

excel conditional formatting multiple conditions

Read More: Excel Conditional Formatting Based on Date Range

Method-7: Conditional Formatting for Empty and Non-Empty Cells

If you want to highlight the rows corresponding to the Delivery Dates which are empty (for explaining this method I have the dates from the three cells of the Delivery Date column and one cell of the Order Date column) that means not delivered yet and the Order Dates which are non-empty, then you can follow this method.

excel conditional formatting multiple conditions

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

Empty and non-empty cells

➤Type the following formula in the Format values where this formula is true: Box

=AND($C5<>"",$D5="")

When the cells of Column C will be Not Equal to Blank, and Column D will be Equal to Blank, then the Conditional Formatting will appear in those corresponding rows.

➤Press OK.

Empty and non-empty cells

Result:
Then, you will get the rows highlighted when the corresponding cells of the column Order Date will be non-empty and the column Delivery Date will be empty.

excel conditional formatting multiple conditions

Read More: Conditional Formatting for Blank Cells in Excel (2 Methods)

Method-8: Conditional Formatting for Multiple Conditions for One Column Using AND Function

If you want to highlight the cells of a single column based on multiple conditions on the Sales column. You can use the AND function to highlight the cells of the Sales column containing values of more than $2000.00 and less than $5000.00.

excel conditional formatting multiple conditions

Step-01:
➤Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

AND function for a single column

➤Type the following formula in the Format values where this formula is true: Box

=AND($E5>2000,$E5<5000)

When the sales value of Column E will be Greater than 2000 or Less than 5000, then the Conditional Formatting will appear in those rows.

➤Press OK

AND function for a single column

Result:
In this way, you will get the cells highlighted for a value of more than $2000.00 and less than $5000.00.

excel conditional formatting multiple conditions

Read More: How to Do Conditional Formatting with Multiple Criteria (11 Ways)

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice

 

Conclusion

In this article, I tried to cover the easiest ways to do Conditional Formatting for multiple conditions in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

2 Comments
  1. I am trying to do a conditional format when a specific Cell is blank and another cell with a date is less than 30 days, leave blank or greater than 30, 60 or 90 days fill with a color for each category. And if the original cell has a value, ignore the dates and leave blank. As example.
    Date Fields are in Column A and Data fields are Column B.
    Column A Column B
    1/1/2022 Cleared
    2/1/2022
    3/1/2022 Cancelled

    I want to be able to use a multiple value option in Conditional formatting.

    • Hello Gabrielle, after going through your problem, I have understood it like this that you want to highlight those dates which are greater than 30 days (or January month) or 60 days (or February month), or 90 days (or March month) and the corresponding specific cells are blank for these dates. If the specific cells are not blank, then the coloring should be avoided. After understanding this scenario, I have created the following dataset like your sample to illustrate the process.
      dataset
      • Firstly, select the dates, and then go to the Home tab >> Conditional Formatting dropdown >> New Rule option.
      conditional
      • In the opening dialog box, choose the indicated option and then type the following formula in the box
      =AND(B2=””,OR(A2>DATEVALUE(“1/30/2022”),A2>DATEVALUE(“2/28/2022”),A2>DATEVALUE(“3/31/2022”)))
      • Click on Format
      formula
      • In the Format Cells dialog box go to the Fill tab >> select your desired color >> press OK.
      format
      Then, the following result will appear.
      result

Leave a reply

ExcelDemy
Logo