Sanjida Ahmed
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 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).
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 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.
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 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.
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 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.
- 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.
- 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.
- 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.
