Excel – Conditional Formatting Using And() Function

The AND() function in Excel is used to test multiple conditions, and it returns TRUE if all the conditions are met, or FALSE if any condition is not met. When used in Conditional Formatting, it highlights cells that meet all specified criteria.

Steps to Use AND() in Conditional Formatting

1. Select the Range of Cells to Apply Formatting
   • Highlight the cells where you want to apply conditional formatting. For example, select A1:A10.
2. Open the Conditional Formatting Menu
   • Go to the Home tab.
   • In the Styles group, click on Conditional Formatting.
   • Select New Rule → Use a Formula to Determine Which Cells to Format.
3. Enter the AND() Formula
   • Write the formula using the AND() function to test your conditions.
   • Example formula:

     =AND(A1>50, B1="Completed")
     

     Explanation:

     • This formula highlights a cell in column A if:
       1. The value in A1 is greater than 50.
       2. The value in B1 is "Completed".
4. Choose the Formatting Style
   • Click the Format button.
   • Select a color, font style, or border to apply when the condition is met.
   • Click OK to save the formatting.
5. Apply and Test
   • Click OK to apply the conditional formatting rule.
   • Test the formatting by changing values in the selected range to see if the rule works.

Examples of Conditional Formatting with AND()

Example 1: Highlight Rows Where All Conditions Are Met

• Formula:

  =AND($A1>50, $B1="Completed")
  

• Use Case: Highlights rows where column A has a value greater than 50 and column B contains “Completed”.

Example 2: Highlight Dates in the Past and Status “Pending”

• Formula:

  =AND(A1<TODAY(), B1="Pending")
  

• Use Case: Highlights cells where column A contains a past date and column B contains “Pending”.

Example 3: Highlight Cells Between Two Numbers

• Formula:

  =AND(A1>=10, A1<=20)
  

• Use Case: Highlights cells in column A with values between 10 and 20 (inclusive).

Tips

1. Use Absolute/Relative References Carefully:
   • Use $ for absolute references if the condition should apply to all rows/columns.
   • Example: $A1 fixes the column, while A$1 fixes the row.
2. Debugging:
   • Test your formula in a blank cell before applying it to conditional formatting. It should return TRUE or FALSE as expected.
3. Complex Conditions:
   • Combine AND() with other functions like OR(), IF(), or NOT() for more advanced conditions.