# How to Use Conditional Formatting with AND Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to apply Conditional Formatting with the AND function in Excel, then this article will serve this purpose. Sometimes you may need to highlight cells only when they fulfill both conditions at a time. So, letâ€™s get into the main article.

## How to Use Conditional Formatting with AND Function in Excel: 4 Ways

Here, we have the following dataset containing a list of products, their corresponding delivery dates, locations to be delivered, and their estimated sales values. Using this dataset, we will try to apply various conditions to highlight using conditional formatting.

We have used Microsoft Excel 365 version for creating this article. However, you can use any other version at your convenience.

### Method-1: Using AND Function to Apply Conditional Formatting in a Single Column

Here, we will highlight the rows having sales values greater than 10,000 USD and smaller than 15,000 USD by using the AND functionÂ to specify these two conditions in the Sales column.

Step-01:

• Select the range and then go to the Home tab >> Conditional Formatting dropdown >> New Rule

After that, the New Formatting Rule dialog box will open up.

• Choose the option Use a formula to determine which cells to format.
• Click on Format.

In this way, the Format Cells wizard will pop up.

• Go to the Fill tab >> choose Blue, Accent 5, Lighter 80% color >> press OK.

Then, you will be taken to the New Formatting Rule dialog box again.

Step-02:

• Type the following formula in the Format values where this formula is true
`=AND(\$E4>10000,\$E4<15000)`

When these two conditions are fulfilled, AND will return TRUE, and the corresponding rows will be highlighted.

• Press OK.

Finally, the rows that have sales values greater than 10,000 USD and smaller than 15,000 USD will be highlighted.

### Method-2: Using AND Function to Apply Conditional Formatting in Multiple Columns

Here, we will highlight the rows which have Canada as the Location, and sales values greater than 10,000 USD.

Steps:

Then, you will get the following preview of the New Formatting Rule dialog box.

• Type the following formula in the Format values where this formula is true
`=AND(\$D4="Canada",\$E4>10000)`

When the country of the Location column is Canada and the Sales values of the Sales are greater than 10,000 USD. AND will return TRUE, and the corresponding rows will be highlighted.

• Press OK.

Finally, you will get the highlighted rows for Canada and sales values greater than 10,000 USD.

Read More: How to Use IF with AND Function in Excel

### Method-3: Highlighting Dates within 7 Days

Here, we will highlight the dates which are situated within 7 days of today (9/22/2022 as mm/dd/yyyy format). By using the TODAY function we will extract todayâ€™s date and then apply the conditions to notify the upcoming delivery dates within 7 days.

Steps:

Then, you will get the following preview of the New Formatting Rule dialog box.

• Type the following formula in the Format values where this formula is true
`=AND(\$C4>=TODAY(),\$C4<TODAY()+7)`

When a date is greater than or equal to todayâ€™s date and less than the date after 7 days from today, AND will return TRUE, and the corresponding rows will be highlighted.
You can insert 15, 30, or whatever days you need.

• Press OK.

Afterward, you will get the following highlighted dataset showing the upcoming delivery dates within 7 days.

### Method-4: Applying Conditional Formatting with AND Function for Two Conditions

Here, we will insert a code of a corresponding countryâ€™s name, and then depending on this code, we will highlight the full name of the countries in the Location column.

Step-01:

Firstly, we will create a dropdown list of codes in cell E15.

• Select cell E15, and then go to the Data tab >> Data Tools group >> Data Validation.

After that, you will have the Data Validation dialog box.

• Select List from different Allow
• In the Source box type the following formula or select the range of the codes.
`=\$B\$15:\$B\$17`
• Press OK.

After that, the dropdown symbol will appear in cell E15.

• Click on the dropdown symbol and select USA.

Then, the USA will appear in cell E15.

Step-02:

Then, you will get the following preview of the New Formatting Rule dialog box.

• Type the following formula in the Format values where this formula is true
`=OR(AND(\$E\$15=\$B\$15,\$D4=\$C\$15),AND(\$E\$15=\$B\$16,\$D4=\$C\$16),AND(\$E\$15=\$B\$17,\$D4=\$C\$17))`

Three AND functions are used for each code and their country names. Finally, the OR function will highlight the rows when any of these conditions become TRUE.

• Press OK.

Finally, the rows with the country United States for the code USA will be highlighted.

If we change the code to CA, the rows corresponding to Canada will be highlighted.

## Practice Section

For doing practice, we have added a Practice portion on each sheet on the right portion.

## Conclusion

In this article, we tried to show the ways to apply Conditional Formatting with the AND function in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF