How to Use Conditional Formatting with AND Function in Excel

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.


Download Workbook


4 Ways to Use Conditional Formatting with AND Function in Excel

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.

excel AND function 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.

Using AND Function to Apply Conditional Formatting in a Single Column in Excel

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.

formatting

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 will be fulfilled, AND will return TRUE, and the corresponding rows will be highlighted.

  • Press OK.

formula to Use AND Function to Apply Conditional Formatting in a Single Column in Excel

Finally, the rows which 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.

Using AND Function to Apply Conditional Formatting in Multiple Columns in Excel

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.

formula for Using AND Function to Apply Conditional Formatting in Multiple Columns

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


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.

highlighting after 7 days using excel AND function conditional formatting

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.

formula for using excel AND function conditional formatting

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.

applying Excel AND function in conditional formatting for two different conditions

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.

dropdown formula

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.

formula for applying Excel AND function in conditional formatting for two different conditions

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.

practice section for excel AND function conditional formatting


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.

Tanjima Hossain

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

Leave a reply

ExcelDemy
Logo