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 for multiple conditions based on another cell range in Excel.
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
and the second one contains the Order Date, Delivery Date, and Sales for some items of another company.
For creating the article, I have used Microsoft Excel 365 version, you can use any other version according to your convenience.
Method-1: Conditional Formatting for Multiple Conditions in One Column
Here, we will attempt to do conditional formatting of a column based on multiple conditions in 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.
- Firstly, select the cell range on which you want to apply the Conditional Formatting.
- Then, go to Home Tab>>Conditional Formatting Dropdown>>New Rule Option.
- Then, the New Formatting Rule Wizard will appear.
- Afterward, select the Format only cells that contain option.
- Further, choose the following in the Format only cells with: Option Cell Value, less than, 2000 consecutively.
- Click Format Option.
- After that, the Format Cells Dialog Box will open up.
- Select Fill Option.
- Choose any Background Color.
- Lastly, click on OK.
- After that, the Preview Option will be shown below, and press OK.
- Now, you will get the cells having a value less than $2000.00 highlighted.
- Follow Step-01 of this method.
- After that, you will get the following New Formatting Rule Dialog Box.
- Choose the following in the Format only cells with: Option Cell Value, greater than, 5000.
- Click Format Option.
- After that, the Format Cells Dialog Box will open up.
- Select the Fill option.
- Choose any Background Color.
- Lastly, click on OK.
- Then, the Preview option will be shown as below, and press OK.
- Finally, you will get the cells highlighted for a value less than $2000.00 and more than $5000.00.
Read More: How to Apply Conditional Formatting on Multiple Columns in Excel
Method-2: Using AND Function to Apply Conditional Formatting for Multiple Conditions in Excel
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 a sales value greater than $5,000.00, and to do this you can use the AND function here.
- Firstly, Select the data range on which you want to apply the Conditional Formatting.
- Then, go to Home Tab>>Conditional Formatting Dropdown>>New Rule Option.
- Then, the New Formatting Rule Wizard will appear.
- Afterward, select Use a formula to determine which cells to format option.
- Again, click on Format Option.
- After that, the Format Cells Dialog Box will open up.
- Select Fill Option.
- Choose any Background Color.
- Lastly, click on OK.
- After that, the Preview option will be shown as below.
- Afterward, write the following formula in the Format values where this formula is true: box
=AND($C5="Richard",$E5>5000)- Press OK.
- After that, you will get a row fulfilling both conditions highlighted.
Read More: Conditional Formatting Based on Multiple Values of Another Cell
Method-3: Using OR Function to Apply Conditional Formatting for Multiple Conditions in One Column
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 is met.
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.
Steps:
- Primarily, follow Step-01 of Method-2.
- After that, you will get the following New Formatting Rule Dialog Box.
- Type the following formula in the Format values where this formula is true: Box
=OR($E5<2000,$E5>5000)- Press OK
- In this way, you will get the cells highlighted for a value less than $2000.00 or more than $5000.00.
Read More: How to Format Cell Based on Formula in Excel
Method-4: Conditional Formatting for Multiple Conditions Using OR Function in Excel
For dealing with multiple conditions in different columns we will use the OR function here. We will highlight the rows that have a North Region or sales value greater than $5,000.00.
Steps:
- Primarily, follow Step-01 of Method-2.
- After that, you will get the following New Formatting Rule Dialog Box.
- Type the following formula in the Format values where this formula is true: Box
=OR($D5= “North”,$E5>5000)- Press OK
- Afterward, you will get the rows fulfilling any one condition highlighted.
Method-5: Using the IF Function for Conditional Formatting with Multiple Conditions in Excel
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.
Steps:
- Firstly, select the output cell F5.
- Then, type the following formula
=IF(C5="Richard",IF(D5="West",IF(E5>5000,"Matched","Not Matched"),"Not Matched"),"Not Matched")
- Press ENTER
- Then, drag down the Fill Handle Tool.
- Now, we will get Matched only for a row where all of the three conditions have been met, and then we will highlight this row.
- Follow Step-01 of Method-2.
- After that, you will get the following New Formatting Rule Dialog Box.
- Type the following formula in the Format values where this formula is true: Box
=$F5="Matched"- Press OK
- Afterward, you will get the row fulfilling all of the conditions highlighted.
Read More: Excel Conditional Formatting Formula with IF
Method-6: Using AND Function for Multiple Conditions Including Condition for Dates
Let’s say, you want to highlight the rows that 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.
Steps:
- Firstly, follow Step-01 of Method-2.
- After that, you will get the following New Formatting Rule Dialog Box.
- Type the following formula in the Format values where this formula is true: Box
=AND($D5>TODAY(),$E5>5000)- Press OK
- After that, you will get the last row fulfilling both conditions highlighted.
Method-7: Conditional Formatting for Empty and Non-Empty Cells in Excel
If you want to highlight the rows corresponding to the Delivery Dates that are empty (to explain 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.
Steps:
- Firstly, follow Step-01 of Method-2.
- After that, you will get the following New Formatting Rule Dialog Box.
- Type the following formula in the Format values where this formula is true: Box
=AND($C5<>"",$D5="")- Press OK.
- 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.
Read More: Conditional Formatting for Blank Cells in Excel
Method-8: Conditional Formatting for Multiple Conditions in One Column Using AND Function
If you want to highlight the cells of a single column based on multiple conditions in 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.
Steps:
- Firstly, follow Step-01 of Method-2.
- After that, you will get the following New Formatting Rule Dialog Box.
- Type the following formula in the Format values where this formula is true: Box
=AND($E5>2000,$E5<5000)- Press OK.
- In this way, you will get the cells highlighted for a value of more than $2000.00 and less than $5000.00.
Download Practice Workbook
You can download the practice workbook from here to practice yourself.
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.
Conditional Formatting for Multiple Conditions in Excel: Knowledge Hub
- Conditional Formatting If Cell is Not Blank
- Change Text Color Based on Value with Excel Formula
- Conditional Formatting Multiple Text Values
- Conditional Formatting Entire Column Based on Another Column
- Excel Highlight Cell If Value Greater Than Another Cell
- VBA Conditional Formatting Based on Another Cell Value
- Change Font Color Based on Value of Another Cell
- Do Conditional Formatting Based on Another Cell
- Conditional Formatting Based on Multiple Values of Another Cell
- Conditional Formatting Based On Another Cell Range
<< Go Back to Conditional Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
• Firstly, select the dates, and then go to the Home tab >> Conditional Formatting dropdown >> New Rule option.
• 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
• In the Format Cells dialog box go to the Fill tab >> select your desired color >> press OK.
Then, the following result will appear.