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 function**s 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**

- In the
pop-up window, select*Edit Formatting Rule***Use a formula to determine which cells to format**asand in the*Rule Type*box write the following formula,*Edit the Rule Description*

`=IF(AND(B5>5, B5<50),TRUE,FALSE)`

- Next click
**Format**.

- Go to the
**Fill**tab in thewindow and*Format Cell***pick any color**that you like. - Click
**OK**.

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

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
pop-up window, select*Edit Formatting Rule***Use a formula to determine which cells to format**asand in the*Rule Type*box write the following formula,*Edit the Rule Description*

`=AND($C6<6000,$D6>5000)`

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

The result is shown below.

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

**greater than 5000.**

*Marks***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
pop-up window, select*Edit Formatting Rule***Use a formula to determine which cells to format**asand in the*Rule Type*box write the following formula,*Edit the Rule Description*

`=OR($C6="B",$D6>5000)`

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

The result is shown below.

**Similar Readings:**

**Excel Conditional Formatting on Multiple Columns****Conditional Formatting Entire Column Based on Another Column(6 Steps)****Excel Conditional Formatting Based on Multiple Values of Another Cell****Conditional Formatting on Multiple Rows Independently in Excel**

**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
pop-up window, select*Edit Formatting Rule***Use a formula to determine which cells to format**asand in the*Rule Type*box write the following formula,*Edit the Rule Description*

`=AND(LEFT(B5,1)="A", RIGHT(B5,1)="X")`

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

The result is shown below.

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

**predefined by the seller.**

*Quantity***Steps:**

- As shown in the previous phase,
**select the cells**that you want to format (e.g. all the cells except header in thecolumn) and in the*Order Qty.***Home**tab, select**Conditional Formatting -> New Rule** - In the
pop-up window, select*Edit Formatting Rule***Use a formula to determine which cells to format**asand in the*Rule Type*box write the following formula,*Edit the Rule Description*

`=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).

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

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