AND, OR and NOT Functions in Excel

AND, OR and NOT are logical functions that evaluate conditions and return TRUE or FALSE. AND is TRUE only when every condition holds, OR is TRUE when at least one holds, and NOT flips a result. They rarely stand alone. Their value is nesting inside IF so a single formula can react to several conditions at once.

Syntax and how the logic works

The signatures are =AND(logical1, logical2, ...), =OR(logical1, logical2, ...) and =NOT(logical). Each logical is a test that resolves to TRUE or FALSE, such as A1>100 or B1="West".

AND returns TRUE only if all of its tests are TRUE, so a single FALSE makes the whole result FALSE. OR returns TRUE if any test is TRUE and only returns FALSE when every test fails. NOT takes exactly one argument and reverses it, turning TRUE into FALSE and the reverse. AND and OR accept up to 255 conditions.

Worked example: flag accounts that pass a screen

A balance sits in B2 and a status code in C2. You want a flag that reads OK only when the balance is at least 1000 and the status is Active, otherwise Review.

1. Build the combined test first: =AND(B2>=1000, C2="Active").
2. Wrap it in IF to return text: =IF(AND(B2>=1000, C2="Active"), "OK", "Review").
3. With B2 equal to 1200 and C2 equal to Active, both tests pass so the result is OK.
4. Change C2 to Hold and the AND becomes FALSE, so the cell returns Review.

Only the first row passes both conditions, so only it returns OK.

How analysts use logical functions in models

These functions keep complex conditions readable instead of stacking many nested IF statements.

• Combine covenant tests with AND so a flag turns on only when every threshold is met.
• Use OR to catch any one of several exception conditions in a data quality check.
• Use NOT to invert an existing flag rather than rewriting the underlying test.
• Feed the TRUE or FALSE result into conditional formatting to highlight rows automatically.

Common pitfalls

A frequent mistake is writing AND(A1>10, A1<20) as a standalone cell and expecting a number. It returns TRUE or FALSE, so you usually need IF around it to produce a useful output.

Order of logic also trips people up. AND of two ORs behaves very differently from OR of two ANDs, so group conditions deliberately. When tests reference blank cells, remember that a blank compares as zero in a numeric test and as an empty string in a text test, which can make a condition pass or fail unexpectedly.