Conditional Formatting with AND Logic in Excel

Conditional Formatting with AND Logic in Excel


Conditional Formatting with AND logic allows you to apply formatting to cells only when multiple conditions are TRUE at the same time. The AND function returns TRUE if all its arguments are true; otherwise, it returns FALSE. You can use it within a custom formula rule in Excel's Conditional Formatting feature.

How it works:

=AND($C2="Done", $D2=0, $E2="100%")

Highlighting Logic:

Rows are highlighted only when:

    • Status = "Done"
    • Error = 0
    • Accuracy = 100%

Explanation of Formula:

AND(...): Ensures all the specified conditions are true.

$C2="Done": Checks if the Status is "Done".

$D2=0: Ensures there is no error (0).

$E2="100%": Confirms that accuracy is exactly 100%.

Step-by-Step Guide:

  1. Select the range of cells you want to format (e.g., A2:D100).
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose “Use a formula to determine which cells to format.”
  4. Enter the AND formula.
  5. Set the formatting style (fill color, bold text, etc.).
  6. Click OK.


Effect of the Formula

When this formula is used in Conditional Formatting (with "Use a formula to determine which cells to format"), it applies formatting (like orange fill) only to rows that satisfy all three conditions. That's why:

  • Rows 3, 4, and 9 are highlighted — they are "Done", with 0 Error, and 100% Accuracy.

If you enjoyed learning about CONDITIONAL FORMATTING, there's so much more waiting for you! Dive deeper into the world of Excel with my eBook “Ultimate Conditional Formatting Formulas” — your complete guide to mastering powerful lookup functions like IF, AND, OR, ISBLANK, ISERROR TODAY(), NOW(), DATEDIF() OFFSET, INDEX, INDIRECT and more.

Whether you're a beginner or an advanced user, this book is packed with real-world examples, practical tips, and easy-to-follow explanations to boost your productivity and confidence in Excel.

Start your journey to becoming an Excel pro — just download your copy today!

Post a Comment

Previous Post Next Post