How to use the Excel IF function for yes-or-no formulas

Share
A laptop displays a spreadsheet on the screen.

The Excel IF function returns one value when a condition is true and a different value when it’s false. Use it when you want a simple yes/no, pass/fail, approved/denied, or in-range/out-of-range result in a cell. The exact pattern is `IF(logical_test, value_if_true, value_if_false)`, and once you know how to write it, you can copy it down a column safely.

What IF does in one sentence

IF checks a logical test and returns one value when the test is true and a different value when it’s false.

That makes it a good fit for labels like Pass/Fail, Approved/Denied, or Yes/No. Say you want a score of 60 or higher to show Pass. IF can do that in one cell and repeat the same rule down a whole list.

The formula pattern you’ll type

The exact syntax is:

IF(logical_test, value_if_true, value_if_false)

Here’s what each part means in plain language:

  • logical_test: the condition Excel checks. This is the yes-or-no question.
  • value_if_true: what Excel returns if the condition is met.
  • value_if_false: what Excel returns if the condition is not met.

If you keep those three roles straight, the formula is much easier to write and debug. In practice, I find it helps to say the sentence out loud first: “If A2 is 60 or more, return Pass, otherwise return Fail.” Then turn that sentence into the formula.

Build your first IF formula in a real sheet

Say column A contains test scores, and you want column B to show Pass or Fail.

  1. Click the first result cell, such as B2.
  2. Type the formula start.
=IF(A2>=60,"Pass","Fail")
Formula
  1. Press Enter.
  2. Look at the result in B2 and compare it to the score in A2.

If A2 is 60 or higher, the cell shows Pass. If it’s below 60, the cell shows Fail.

That’s the simplest valid IF formula: one comparison, one result for true, one result for false. Once that works in one row, you can copy the formula down the column and let Excel adjust the row number for each record.

A hand types on a laptop with a spreadsheet open.

How to write the logical test correctly

The logical test is where most mistakes happen. Excel compares values with comparison operators:

  • = equal to
  • > greater than
  • < less than
  • >= greater than or equal to
  • <= less than or equal to
  • <> not equal to

A few practical points matter here:

  • Numbers should be compared as numbers. If a threshold or score is stored as text, the formula may not behave the way you expect.
  • Text comparisons use quotes around the text in the formula, such as =IF(A2="East","Yes","No").
  • Blank cells can behave differently from cells that contain an empty-looking formula result. If you need to test for a truly blank cell, compare with ="" or use a blank check pattern that fits your sheet.
  • Dates are stored as numbers behind the scenes, so date comparisons work with operators too. For example, =IF(A2>=DATE(2026,1,1),"Current","Old") compares the date in A2 to a real date value.

If a formula looks right but gives the wrong answer, check whether you’re comparing text to text, number to number, or date to date. A value that only looks like a number can still be stored as text.

How to choose the true and false results

The value_if_true and value_if_false parts can return text, numbers, or even another formula result.

  • Put text in double quotes, like "Pass", "Yes", or "Approved".
  • Put numbers without quotes if you want a numeric result, like 1 or 0.
  • Use "" if you want the cell to appear blank when the test is false.

For example:

=IF(A2>=60,"Pass","Fail")

or

=IF(A2>=60,1,0)

or

=IF(A2>=60,A2*0.1,"")

If the condition is false, Excel returns whatever you put in the third argument. It does not guess a default answer for you. In newer Excel versions, some functions and patterns can appear to accept omitted arguments in special cases, but for a clean beginner formula, keep both results explicit so the behavior is obvious.

Copy the formula down without breaking it

Once the formula works in one row, you’ll usually want to copy it down a column. That’s where relative and absolute references matter.

A relative reference changes when you copy the formula. In A2, the row number will shift to A3, A4, and so on as you copy down. That is what you usually want for row-by-row checks.

An absolute reference stays fixed when copied. You lock it with $, like $E$1.

Use each one on purpose:

  • Keep the row-based input cell relative, such as A2, when each row should be checked separately.
  • Lock a shared threshold cell with an absolute reference, such as $E$1, when every row should compare against the same value.

For example, if E1 holds the passing score, this formula stays pointed at E1 while A2 changes as you copy it down:

=IF(A2>=$E$1,"Pass","Fail")

If you forget to lock the threshold cell, copying the formula down can shift the reference and compare each row against the wrong cell. That is one of the most common beginner mistakes.

After you enter the formula in the first row, drag the fill handle or use copy and paste to copy it down. Then spot-check a few rows to make sure the row references changed the way you expected.

A laptop screen shows a spreadsheet with rows of copied cells.

Common IF mistakes and when to use something else

A few IF problems show up again and again.

Quoted text mistakes are a big one. Text results need double quotes, but numbers do not. If you type Pass without quotes, Excel treats it as something else and the formula won’t work the way you expect.

Separator mistakes are another one. Some regional settings use commas in formulas, while others use semicolons. If Excel rejects the formula, check your locale’s list separator and match the separators Excel expects on your system. The exact separator depends on your regional settings, so check the current setting in your copy of Excel instead of assuming it will match someone else’s workbook.

You can also get an unexpected match because of hidden spaces or text-formatted numbers. A cell that looks blank may contain a space, and a cell that looks numeric may actually be text. Those are the cases where the formula seems right but the result is off.

When formulas start to stack up, nested IF can get messy fast. If you’re chaining several conditions, stop and consider a cleaner tool:

  • Use IFS when you have several conditions in order and want Excel to test them one by one.
  • Use AND when several tests must all be true at the same time.
  • Use OR when any one of several tests can be true.
  • Use XLOOKUP when you’re really matching a value to a table and returning a related result, not making a simple true/false decision.

A good rule: if your nested IF is turning into a long, hard-to-read stack, another function is probably a better fit.

Check that the formula worked

The best way to confirm an IF formula is not just to look at it, but to test it on purpose.

Use this quick check:

  • Compare the displayed result to the rule you meant to build.
  • Test one row that should return the true result.
  • Test one row that should return the false result.
  • If you copied the formula down, confirm the references still point to the right cells.
  • If the result seems wrong, inspect whether the comparison is against the right data type and whether any threshold cell should be locked.
  • Click the formula cell and read the formula bar so you can see the exact references Excel used.

If the formula returns Pass where you expected Fail, or the opposite, the problem is usually in the logical test or in the references, not in the IF function itself. Once those two pieces are right, the formula tends to behave predictably.

When you’ve finished, you should see the expected label or value in the first row and the same rule should carry down the column without changing the meaning of the formula.