In this article, you learn how to apply **Conditional Formatting** with the formula for multiple conditions in Excel. Here, we will demonstrate a gif for a better understanding of how to apply conditional formatting with formulas.

## Download Practice Workbook

You can download the free practice Excel template from here.

## 5 Easy Wayson 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.

### Method 1: Combining IF and AND Functions to Apply Conditional Formatting with Formula for Multiple Conditions in Excel

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

We will consider the following dataset as our example.

**Steps:**

- To begin with, 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**.

- After that, 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**.

- Then, go to the
**Fill**tab in thewindow and*Format Cell***pick any color**that you like. - Besides, click
**OK**.

- Again click
**OK**on the*Edit Formatting Rule.* - The result is shown in the picture below.
- Finally, 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**

### Method 2: Using AND Function to Apply Conditional Formatting with Formula for Multiple Conditions in Excel

We can also use **the AND function **to apply **Conditional Formatting **with formulas for multiple conditions. With the following dataset as an example, we will find out how to utilize the **AND** function to extract data that fulfill multiple conditions.

**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, and click**OK**. - Click
**OK**again**.**

- As a result, you will get the following result.

**Read more:** **How to Do Conditional Formatting for Multiple Conditions**

### Method 3: Applying OR Function to Use 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 *** A **and

**and**

*C***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, and click**OK**and**OK**again.

- Therefore, you will get the following output with numbers and text.

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

### Method 4: Nesting AND, LEFT, and RIGHT Functions to Apply 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 from 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, and click**OK**. - Click
**OK**again**.**

- Lastly, you will see the following result with text here in the below image.

### Method 5: EmployingÂ VLOOKUP Function to Apply Conditional Formatting with Formula for Multiple Conditions for the Same Range 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 the 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`

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

**Formula Breakdown**

- Here,
**E5**represents cell reference a number of the first cell in the*Order Qty.* - This argument (
**$G$5:$H$12**) shows the cell range to match the value - Besides,
**2**represents the corresponding column number to extract the value. - Then, the
**FALSE**argument gets the exact match. - Finally,
**The ABS function**is for returning the absolute value of a number without its mathematical sign (e.g.**+/-**signs).

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

- Therefore, click
**Format**,**pick a color**from the**Fill**tab (we picked Orange this time), and click**OK**and**OK**again.

**Formula Breakdown**

- Here,
represents cell reference a number of the first cell in the Order Qty. column.*E5* - Then,
matches the Product ID.*B5* - This argument (
**$G$5:$H$12**) shows the cell range to match the value. - Besides,
**2**represents the corresponding column number to extract the value. - After that, the
**FALSE**argument gets the exact match.

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

- Next, click
**Format**,**pick a color**from the**Fill**tab (we picked Blue this time), and click**OK**and**OK**again.

**Formula Breakdown**

- Here,
represents cell reference a number of the first cell in the Order Qty. column.*E5* - Then,
matches the Product ID.*B5* - This argument (
**$G$5:$H$12**) shows the cell range to match the value. - Besides,
**2**represents the corresponding column number to extract the value. - After that, the
**FALSE**argument gets the exact match.

- Finally, you will get the following result.

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