Conditional Logic

IF, IFS, AND, OR — build status flags and pass/fail rules.

Highlight Duplicate Values With Color

Use a COUNTIF rule in conditional formatting to automatically color every cell whose value appears more than once.

=COUNTIF($A$2:$A$20,A2)>1

Highlight Overdue Rows Automatically

A conditional formatting rule that colors the whole row when a due date has passed and the task still isn't marked Complete.

=AND($B2<TODAY(),$C2<>"Complete")

conditional logic

Create a Pass/Fail Status Column

Turn a score column into clear Pass or Fail labels with a single IF — the simplest and most-used conditional formula there is.

=IF(B2>=70,"Pass","Fail")

conditional logic

Create a Status with Multiple Conditions

Grade scores into Excellent, Pass, or Fail with IFS — cleaner than nested IFs, with a TRUE catch-all so nothing slips through.

=IFS(B2>=90,"Excellent",B2>=70,"Pass",TRUE,"Fail")

conditional logic

Flag Rows Where a Cell Contains Specific Text

Label an order Priority when its notes mention "rush" — SEARCH finds the word anywhere in the cell and IF turns the result into a clean status.

=IF(ISNUMBER(SEARCH("rush",A2)),"Priority","Standard")

conditional logic

Test Whether a Number Falls Between Two Values

AND checks both ends of the range in one IF — label order quantities that qualify for a carton discount, with the boundaries counted correctly.

=IF(AND(B2>=10,B2<=20),"In range","Out of range")

conditional logic

Test Whether a Cell Is Filled or Empty

Flag rows with a missing PO number using a simple not-equal-to-empty test — and know when it disagrees with ISBLANK on formula-made blanks.

=IF(A2<>"","Filled","Missing")

conditional logic

Build Three-Tier Results with Nested IF Statements

Put one IF inside another to return a third outcome — tiered unit pricing by order quantity, checked from the strictest condition down.

=IF(B2>=100,4.5,IF(B2>=50,5.25,6))

conditional logic

Flag a Row When Any One Condition Is True

OR inside IF fires when any test passes — escalate an order if it shipped Rush or its value tops $500, without writing two formulas.

=IF(OR(A2="Rush",B2>500),"Escalate","Normal")

conditional logic

Combine AND with OR in a Single IF Formula

Nest OR inside AND to express rules like "Open, and either older than 30 days or high priority" — the grouping decides everything.

=IF(AND(A2="Open",OR(B2>30,C2="High")),"Review","OK")

conditional logic

Return a Blank Instead of a Zero

Swap zeros for empty cells so reports read cleanly — and understand how the invisible "" quietly changes averages computed on that column.

=IF(A2=0,"",A2)

conditional logic

Check Whether a Value Appears in a List

COUNTIF counts how often a value appears in a reference list — wrap it in IF to mark each vendor Approved or Not approved in one pass.

=IF(COUNTIF($D$2:$D$5,A2)>0,"Approved","Not approved")

conditional logic

IFERROR vs IFNA: Which Errors to Catch

IFNA only catches #N/A — perfect for lookups; IFERROR hides every error, including the typos and broken refs you need to see. Default to IFNA.

=IFNA(VLOOKUP(D2,A2:B10,2,FALSE),"Not on list")

conditional logic

IF vs IFS: When to Stop Nesting

IFS reads as flat condition-result pairs, best for three or more tiers with a TRUE catch-all; nested IF still wins for one either/or and pre-2019 Excel.

=IFS(B2>=90,"Gold",B2>=75,"Silver",TRUE,"Bronze")

conditional logic

Catch Any Formula Error With IFERROR

Wrap a risky calculation in IFERROR to swap any error for a fallback like 0 — keeping totals, charts, and reports working.

=IFERROR(A2/B2,0)