# How to Apply Excel Conditional Formatting with Formula for Multiple Conditions

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. ## 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 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. • Then, go to the Fill tab in the Format Cell window and 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). ### 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 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)`

• 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. ### 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 C 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)`

• 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. ### 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 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")`

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

• 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, E5 represents cell reference a number of the first cell in the Order Qty. column.
• Then, B5 matches the Product ID.
• 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, E5 represents cell reference a number of the first cell in the Order Qty. column.
• Then, B5 matches the Product ID.
• 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.

## You May Also Like to Explore #### 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  5 Excel Hacks You Never Knew  