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

If you are looking for some of the easiest ways to do Conditional Formatting with multiple criteria, then you are in the right place.

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

Download Workbook

11 Ways to Do Conditional Formatting with Multiple Criteria

Here, I have the three data tables to show the ways of Conditional Formatting for multiple criteria in Excel. The first table contains the Salary Records of a company.

The second one has the Start Date, Due Date, and Cost of some projects of a construction company.

And the last one contains Grades for some students of a college.

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 with Multiple Criteria for One Column

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

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.

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

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

⧫ Cell Value
⧫ less than
⧫ 5000

➤Click Format Option

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

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

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

Step-03:
➤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
⧫ 9000

➤Click Format Option

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

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

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

Read more: Conditional Formatting Entire Column Based on Another Column

Method-2: Conditional Formatting with Multiple Criteria Using Between And Option

We will highlight the cells of a single column based on multiple criteria on the Salary column. By using Conditional Formatting we will highlight the cells of the Salary column containing values between $5000.00 and $9000.00.

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.

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

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

⧫ Cell Value
⧫ between
⧫ 5000
⧫ 9000

➤Click Format Option

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

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

Result:
After that, you will get the cells highlighted for a value between $5000.00 and $9000.00.

Method-3: Conditional Formatting with Multiple Criteria for One Column Using AND Function

While dealing with multiple criteria you can use the AND function, this will highlight the rows only when both conditions are met.

You can use the AND function to highlight the cells of the Salary column containing values more than $5000.00 and less than $9000.00.

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.

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

➤Click on Format Option.

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

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

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

When the Salary of Column D will be Greater than 5000 and Less than 9000, then the Conditional Formatting will appear in those rows.

➤Press OK

Result:
Afterward, you will get the cells of the Salary column highlighted for a value of more than $5000.00 and less than $9000.00.

Method-4: Conditional Formatting with Multiple Criteria for One Column Using OR Function

For dealing with multiple criteria 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 Salary column. You can use the OR function to highlight the cells of the Salary column containing values less than $5000.00 and more than $9000.00.

Step-01:
➤Follow Step-01 of Method-3.
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

When the Salary of Column D will be Less than 5000 or Greater than 9000, then the Conditional Formatting will appear in those rows.

➤Press OK

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

Method-5: Conditional Formatting with Multiple Criteria for Multiple Columns Using AND Function

While dealing with multiple criteria in multiple columns you can use the AND function.

Let’s say, you want to highlight the rows which have a Department named Purchasing and Salary greater than $6,000.00, and to do this you can use the AND function here.

Step-01:
➤Follow Step-01 of Method-3.
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

When the string of Column C will match Purchasing and the Salary of Column D will be Greater than 6000, then the Conditional Formatting will appear in those rows.

➤Press OK

Result:
Then, you will get a row fulfilling both criteria in both columns highlighted.

Read more: Excel Conditional Formatting on Multiple Columns

Method-6: Conditional Formatting with Multiple Criteria for Multiple Columns Using OR Function

For dealing with multiple criteria in different columns we will use the OR function here. We will highlight the rows which have a Purchasing Department or Salary greater than $6,000.00.

Step-01:
➤Follow Step-01 of Method-3.
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

When the string of Column C will match Purchasing or the Salary of Column D will be Greater than 6000, then the Conditional Formatting will appear in those rows.

➤Press OK

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

Similar Readings:

• How to Do Conditional Formatting for Multiple Conditions
• Conditional Formatting with Formula for Multiple Conditions in Excel
• Excel Conditional Formatting Based on Multiple Values of Another Cell
• Pivot Table Conditional Formatting Based on Another Column

Method-7: Conditional Formatting with Multiple Criteria Using IF Function

In this method, we are using the IF function for highlighting rows that have fulfilled multiple criteria. For this purpose, we have added a column named Yes/No.

Step-01:
➤Select the output Cell E5.

➤Type the following formula

IF will return “YES” if the two criteria given here are met, otherwise “NO”.

➤Press ENTER
➤Drag down the Fill Handle Tool.

Now, we will get YES only for a row where all of the two criteria have been met, and then we will highlight this row.

Step-02:
➤Follow Step-01 of Method-3.
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

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

➤Press OK

Result:
Then, you will get the row fulfilling all of the criteria highlighted.

Read more: Excel Conditional Formatting Formula with IF

Method-8: Conditional Formatting with Multiple Criteria Using AND, TODAY Function

Let’s say, you want to highlight the rows which have Due Dates after today (today’s date is 12-22-21 and the date format is mm-dd-yy) and Costs greater than $30,000.00, and to do this you can use the AND function and the TODAY function here.

Step-01:
➤Follow Step-01 of Method-3.
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

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

➤Press OK

Result:
In this way, you will get the last row fulfilling both criteria highlighted.

Method-9: Using AND Function with Multiple Criteria for a Certain Period

Suppose, you want to highlight the rows for the Start Date of the projects in January month. To highlight the rows for this certain period you can use the AND function and the DATEVALUE function.

Step-01:
➤Follow Step-01 of Method-3.
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

When the dates of Column C will be Greater than or Equal To the first date of January month and Less than or Equal To the last date of January month, then the Conditional Formatting will appear in those rows.

DATEVALUE will convert the text dates into values.

➤Press OK

Result:
After that, you will get the rows fulfilling both criteria of the dates for January month highlighted.

Method-10: Conditional Formatting with Multiple Criteria for Blank and Non-Blank Cells

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

Step-01:
➤Follow Step-01 of Method-3.
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

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.

Result:
Finally, you will get the rows highlighted when the corresponding cells of the column Start Date will be non-blank and the column Due Date will be blank.

Read more: Conditional Formatting for Blank Cells in Excel

Method-11: Conditional Formatting with Multiple Criteria Using LEFT and RIGHT Function

Let’s say, you want to highlight the rows corresponding to the cells of the Student Id column which has a Student Id starting with Y character and ending with G character. To do this you can use the AND function, the LEFT function, and the RIGHT function.

Step-01:
➤Follow Step-01 of Method-3.
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

LEFT($B5,1) will extract the first character of the string in the cell $B5 and then it will check if it is equal to “Y”. Similarly, RIGHT($B5,1) will extract the last character of the string in the cell $B5 and then it will be compared to “G”.

When these two criteria meet, then the corresponding rows will be highlighted.

Result:
In this way, you will get the rows highlighted for the corresponding cells having the Student Ids Y-0121634G and Y-0254615G.

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.

Conclusion

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

Further Readings

• Conditional Formatting If Cell is Not Blank
• Conditional Formatting Based On Another Cell in Excel (6 Methods)
• How to Highlight Highest Value in Excel (3 Quick Ways)
• Use Conditional Formatting in Excel [Ultimate Guide]