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.

conditional formatting with multiple criteria

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

conditional formatting with multiple criteria

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

conditional formatting with multiple criteria

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.

conditional formatting with multiple criteria

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 criteria for one column

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

multiple criteria for one column

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

Cell Value
less than
5000

➤Click Format Option

conditional formatting with multiple criteria

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

multiple criteria for one column

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

multiple criteria for one column

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

conditional formatting with multiple criteria

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

multiple criteria for one column

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

Cell Value
greater than
9000

➤Click Format Option

multiple criteria for one column

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

multiple criteria for one column

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

multiple criteria for one column

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

conditional formatting with multiple criteria

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.

conditional formatting with multiple criteria

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.

using between and option

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

conditional formatting with multiple criteria

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

Cell Value
between
5000
9000

➤Click Format Option

using between and option

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

using between and option

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

conditional formatting with multiple criteria

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

conditional formatting with multiple criteria

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.

conditional formatting with multiple criteria

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 for one column

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

conditional formatting with multiple criteria

➤Click on Format Option.

AND function for one column

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

AND function for one column

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

conditional formatting with multiple criteria

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

=AND($D5>5000,$D5<9000)

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

AND function for one column

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.

conditional formatting with multiple criteria

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.

conditional formatting with multiple criteria

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

OR function for one column

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

=OR($D5<5000,$D5>9000)

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

OR function for one column

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

OR function for one column

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.

conditional formatting with multiple criteria

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

AND function for multiple columns

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

=AND($C5="Purchasing",$D5>6000)

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

AND function for multiple columns

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

AND function for multiple columns

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.

conditional formatting with multiple criteria

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

OR function for multiple columns

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

=OR($C5="Purchasing",$D5>6000)

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

OR function for multiple columns

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

OR function for multiple columns


Similar Readings:


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.

conditional formatting with multiple criteria

Step-01:
➤Select the output Cell E5.

➤Type the following formula

=IF(C5="Purchasing",IF(D5>6000,"YES","NO"),"NO")

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

IF function

➤Press ENTER
➤Drag down the Fill Handle Tool.

conditional formatting with multiple criteria

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.

IF function

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

=$E5="YES"

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

➤Press OK

IF function

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

conditional formatting with multiple criteria

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.

conditional formatting with multiple criteria

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

TODAY function

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

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

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

TODAY function

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

TODAY function

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.

conditional formatting with multiple criteria

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

DATEVALUE function

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

=AND($C5>=DATEVALUE("1/1/2021"),$C5<=DATEVALUE("1/31/2021"))

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

DATEVALUE function

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

DATEVALUE function

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.

conditional formatting with multiple criteria

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

blank and non-blank 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.

blank and non-blank cells

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.

blank and non-blank cells

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.

conditional formatting with multiple criteria

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

LEFT and RIGHT function

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

=AND(LEFT($B5,1)="Y",RIGHT($B5,1)="G")

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.

LEFT and RIGHT function

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

conditional formatting with multiple criteria

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 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

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo