Conditional Formatting with Formula for Multiple Conditions in Excel

In this article, you learn how to apply Conditional Formatting with the formula for multiple conditions in Excel.


Download Practice Template

You can download the free practice Excel template from here.


5 Methods on Applying Conditional Formatting with Formula for Multiple Conditions in Excel

This section will help you to learn how to use Excel’s Conditional Formatting command with the formula for multiple conditions in Excel.

1. Conditional Formatting with Formula for Multiple Conditions with the IF and the AND Functions in Excel

In this phase, we will learn how to apply Conditional Formatting for multiple conditions with the IF and the AND functions in Excel.

We will consider the following dataset as our example.

Steps:

  • Select the cells that you want to format (e.g. all the cells except headers).
  • Then in the Home tab, select Conditional Formatting -> New Rule

Applying Conditional Formatting with Formula for Multiple Conditions in Excel

  • In the Edit Formatting Rule pop-up window, select Use a formula to determine which cells to format as Rule Type and in the Edit the Rule Description box write the following formula,
=IF(AND(B5>5, B5<50),TRUE,FALSE)
  • Next click Format.

Conditional Formatting with Formula for Multiple Conditions with the IF and the AND Functions in Excel

  • Go to the Fill tab in the Format Cell window and pick any color that you like.
  • Click OK.

  • Again click OK on the Edit Formatting Rule.
  • The result is shown in the picture below

result of Conditional Formatting with Formula for Multiple Conditions with the IF and the AND Functions in Excel

Our formula will highlight the values which fulfill both the conditions set by the IF and the AND functions (e.g. the values between 5 and 50).

Read more: Excel Conditional Formatting Formula with IF


2. Conditional Formatting with Formula for Multiple Conditions with the AND Function in Excel

With the following dataset as an example, we will find out how to utilize the AND function to extract data that fulfill multiple conditions, for instance, values that are less than 6000 and greater than 5000.

Steps:

  • As shown in the previous phase, select the cells that you want to format (e.g. all the cells except headers) and in the Home tab, select Conditional Formatting -> New Rule
  • In the Edit Formatting Rule pop-up window, select Use a formula to determine which cells to format as Rule Type and in the Edit the Rule Description box write the following formula,
=AND($C6<6000,$D6>5000)

Conditional Formatting with Formula for Multiple Conditions with the AND Functions in Excel

  • Next, similar to before, click Format, pick a color from the Fill tab, click OK and OK.

The result is shown below.

result of Conditional Formatting with Formula for Multiple Conditions with the AND Functions in Excel

Only the values greater than 5000 and less than 6000 are highlighted here.

Notice -> If you want only the values greater than 5000 or less than 6000, then instead of using the AND function, simply use the OR function.

Then the formula will be,

=OR($C6<6000,$D6>5000)

Read more: How to Do Conditional Formatting for Multiple Conditions


3. Conditional Formatting with Formula for Text and Number Criteria with Multiple Conditions in Excel

Here, we will learn how to utilize Conditional Formatting to fulfill multiple conditions based on texts and numbers.

From the example below, we will extract the students’ information from Dept B and Marks greater than 5000.

Steps:

  • As shown in the previous phase, select the cells that you want to format (e.g. all the cells except headers) and in the Home tab, select Conditional Formatting -> New Rule
  • In the Edit Formatting Rule pop-up window, select Use a formula to determine which cells to format as Rule Type and in the Edit the Rule Description box write the following formula,
=OR($C6="B",$D6>5000)

Conditional Formatting with Formula for Multiple Conditions with text and number in Excel

  • Next, similar to before, click Format, pick a color from the Fill tab, click OK and OK.

The result is shown below.

result of Conditional Formatting with Formula for Multiple Conditions with text and number in Excel


Similar Readings:


4. Conditional Formatting with Formula for Text Criteria as Multiple Conditions in Excel

Here, we will learn how to utilize Conditional Formatting to fulfill multiple conditions based on texts.

From the example below, we will extract the information of the texts that start with A and end with X.

Steps:

  • As shown in the previous phase, select the cells that you want to format (e.g. all the cells except headers) and in the Home tab, select Conditional Formatting -> New Rule
  • In the Edit Formatting Rule pop-up window, select Use a formula to determine which cells to format as Rule Type and in the Edit the Rule Description box write the following formula,
=AND(LEFT(B5,1)="A", RIGHT(B5,1)="X")

Conditional Formatting with Formula for Multiple Conditions with texts in Excel

  • Next, similar to before, click Format, pick a color from the Fill tab, click OK and OK.

The result is shown below.

result of Conditional Formatting with Formula for Multiple Conditions with texts in Excel


5. Conditional Formatting with Formula for Multiple Conditions for the Same Range Based on VLOOKUP in Excel

We can also utilize Conditional Formatting for multiple conditions with the VLOOKUP function in Excel.

Consider the following data. We will format the Order Qty. into three categories based on the Quantity predefined by the seller.

Steps:

  • As shown in the previous phase, select the cells that you want to format (e.g. all the cells except header in the Order Qty. column) and in the Home tab, select Conditional Formatting -> New Rule
  • In the Edit Formatting Rule pop-up window, select Use a formula to determine which cells to format as Rule Type and in the Edit the Rule Description box write the following formula,
=ABS(E5-VLOOKUP(B5,$G$5:$H$12,2,FALSE))<=10

Here,

E5 = cell reference number of the first cell in the Order Qty. column
$G$5:$H$12 = cell range to match the value
2 = corresponding column number to extract the value from
FALSE = to get the exact match
The ABS function is for returning the absolute value of a number without its mathematical sign (e.g. +/- signs).

Conditional Formatting with Formula for Multiple Conditions with three conditions in Excel

  • Next, similar to before, click Format, pick a color from the Fill tab (we picked Green), click OK and OK

The result is shown below.

  • Repeat the steps from selecting the cells to writing the formula. This time write the formula as,
=AND(ABS(E5-VLOOKUP(B5,$G$5:$H$12,2,FALSE))>10,ABS(E5-VLOOKUP(B5,$G$5:$H$12,2,FALSE))<30)

Here,

E5 = cell reference number of the first cell in the Order Qty. column
B5 = to match the Product ID
$G$5:$H$12 = cell range to match the value
2 = corresponding column number to extract the value from
FALSE = to get the exact match

  • Click Format, pick a color from the Fill tab (we picked Orange this time), click OK and OK.

The result is shown below.

  • Again repeat the steps from selecting the cells to writing the formula. And now write the formula as,
=ABS(E5-VLOOKUP(B5,$G$5:$H$12,2,FALSE))>=30

Here,

E5 = cell reference number of the first cell in the Order Qty. column
B5 = to match the Product ID
$G$5:$H$12 = cell range to match the value
2 = corresponding column number to extract the value from
FALSE = to get the exact match

  • Click Format, pick a color from the Fill tab (we picked Blue this time), click OK and OK.

The result is shown below.

result of Conditional Formatting with Formula for Multiple Conditions with three conditions in Excel


Conclusion

This article showed you how to apply Conditional Formatting with the formula for multiple conditions in Excel. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


You May Also Like to Explore

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo