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:
- Select
the range of cells you want to format (e.g., A2:D100).
- Go
to Home > Conditional Formatting > New Rule.
- Choose
“Use a formula to determine which cells to format.”
- Enter
the AND formula.
- Set
the formatting style (fill color, bold text, etc.).
- 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.