How to Use the IF Function in Excel With Examples

Type =IF(test, result if true, result if false) and press Enter. Worked examples for AND, OR, nested IF, Excel for the web and the common formula errors.

Contents

Don't Miss the Good Stuff

Get tech news that matters delivered to your inbox.

To use the IF function in Excel, click the cell where the answer should go, type =IF(B2>=70,"Pass","Review") and press Enter. This IF statement checks the test before the first comma, then shows the second part when the test is true and the third part when it is false. On our score sheet, a score of 92 in B2 showed Pass, and the 68 below it showed Review.

Put words such as Pass in double quotes, copy the formula down the column to test each row, and put AND or OR inside the test when two things decide the answer. The same formula works in Excel for the web, where the function dialog has its own Next and Insert steps.

The short version

  • The pattern: =IF(test, result if true, result if false), for example =IF(B2>=70,"Pass","Review").
  • Quotes: text results go in double quotes. Without them, Excel may show #NAME? instead of the word.
  • Copy it down: drag the fill handle, or select the cells and press Ctrl + D.
  • Two conditions: IF(AND(...)) when both must be true, IF(OR(...)) when either one is enough.
  • Three or more answers: nest one IF inside another, highest threshold first for >= tests and lowest first for <= tests.

IF formulas at a glance

These formulas ran on our practice sheet, where column A holds four made-up names, column B a score and column C whether the work was submitted. The results are what Excel showed for Ada, Ben, Cy and Dee, who scored 92, 68, 70 and 70.

What you wantFormula or step we usedWhat it returned
One test, two answers=IF(B2>=70,"Pass","Review")Pass for 92, Review for 68
Both conditions must be true=IF(AND(B2>=70,C2="Yes"),"Pass","Review")Pass, Review, Review, Pass
Either condition is enough=IF(OR(B2>=70,C2="Yes"),"Pass","Review")Pass on all four rows
Three answers from score bands=IF(B2>=90,"A",IF(B2>=70,"B","C"))A, C, B, B
Copy the formula down with Ctrl + DSelect the formula cell and the cells below it firstEach row tests its own cells, so G4 tested B4 and C4
Fill in boxes instead with Shift + F3Choose IF, then type B3>=70, "Pass" and "Review" in the three boxesFormula result = Review before OK

How the IF function works

Microsoft writes the pattern as IF(logical_test, value_if_true, [value_if_false]). The first part is a test that is either true or false, the second is what the cell shows when it is true, and the third is what it shows when it is false. Commas separate the parts.

PartWhat it doesIn our formula
logical_testA comparison that comes out TRUE or FALSEB2>=70, is the score 70 or more
value_if_trueWhat the cell shows when the test is TRUE"Pass"
value_if_falseWhat the cell shows when the test is FALSE"Review"

The test compares two things with an operator: = for equal to, > and < for greater and less than, >= and <= to include the number itself, and <> for not equal to. Our two scores of 70 both passed because >= counts 70 itself, while a plain > leaves it out. To check that a cell does not hold a certain value, use the not equal operator in the test.

Text results go inside straight double quotes, as in "Pass". Numbers and cell references go in without quotes, and Microsoft's own examples show that a result can also be a calculation, such as a sales tax worked out only when a cell says Yes. If cells and the formula bar are still new to you, our guide to learning the basics of Excel covers them first.

Type an IF formula into a cell

Our practice sheet has Case in column A, Score in B, Submitted in C and an empty Decision column in D. The four rows are made up: Ada scored 92 and submitted, Ben 68 and submitted, Cy 70 without submitting, and Dee 70 with a submission.

Click D2 and type =IF. Excel lists IF with IFERROR, IFNA and IFS under the cell, and its tip reads Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. Type the opening bracket and a ScreenTip shows the three parts, with the one you are typing in bold.

Type the rest, B2>=70,"Pass","Review"), and press Enter. On our sheet D2 showed Pass, and when we clicked D2 again the formula bar held the whole formula. That is the quickest check on any IF result, because the cell shows the answer and the formula bar shows the question.

Excel suggesting IF as the formula is typed, its argument tip, then Pass in cell D2.
Click to expand
Typing =IF in D2: AutoComplete offers IF (1) with Excel's description of it (2). After the bracket, the ScreenTip shows the three parts with the current one in bold (3). After Enter, the formula stays in the formula bar (4) and the cell shows Pass (5).

The ScreenTip on our PC showed the second part in brackets too, as IF(logical_test, [value_if_true], [value_if_false]), even though Microsoft's reference page lists value_if_true as required. Fill in both results anyway. The section on zero results below shows what an empty slot does.

Build an IF formula with the Insert Function box

If you would rather fill in boxes than remember commas and quotes, click the answer cell and select the fx button to the left of the formula bar. On our PC the Insert Function box opened on the Most Recently Used category with IF already in the list. If it is not there, type IF in the search box and select Go.

Select IF and click OK. The Function Arguments box has one field per part, and each field shows its value on the right as you type, so B2>=70 showed TRUE beside it. Ours read Formula result = Pass before we clicked OK, and OK put =IF(B2>=70,"Pass","Review") in F2 with Pass in the cell.

Excel Insert Function dialog with IF found by search, then Function Arguments showing Formula result Pass.
Click to expand
The fx button (1) opens Insert Function, where you can search for IF and select Go (2), select IF in the list (3) and see its syntax line (4). In Function Arguments, each part gets its own box with its value beside it (5), a help line explains the box you are in (6), the result shows before you click OK (7), and OK puts the formula in the cell (8).

Two other routes open the same dialog. Shift + F3 brought it up on our sheet, and with B3>=70 in the test the preview read Review before we clicked OK. On the Formulas tab, the arrow under AutoSum lists Sum, Average, Count Numbers, Max, Min and More Functions, which opens the same search, and searching for IF put it first in the list.

Copy the IF formula down a column

One formula covers the whole column. Click D2, point at the small square at its bottom right corner, called the fill handle, and drag it down to D5. Our column filled with Pass, Review, Pass and Pass, and an Auto Fill Options button appeared beside the last cell.

Excel moves the cell reference one row at a time as it fills. D3 held =IF(B3>=70,"Pass","Review"), so Ben's 68 got Review on its own row. If the pass mark sits in a cell of its own rather than inside the formula, lock that reference with dollar signs before you fill. Our guide to locking cells in Excel notes that pressing F4 on the reference while you type the formula adds them.

The keyboard works too. Select the formula cell and the cells below it, then press Ctrl + D or choose Home > Fill > Down. On our screen Fill was an icon without a label in the Editing group, and Down copied F4 into F5 as a formula that tested B5. Ctrl + D and Shift + F3 are two of the Excel keyboard shortcuts worth learning if you build formulas often.

Excel IF formula filled down column D with D3 testing B3, and the Home Fill menu.
Click to expand
After filling down, D3 tests B3 instead of B2 (1) and the column shows Pass, Review, Pass, Pass (2). The same copy is on the Home tab under Fill (3) and Down (4).

If dragging does nothing, check the setting behind it. Under File > Options > Advanced, in Editing options, Enable fill handle and cell drag-and-drop was ticked by default on our PC. With it cleared, our drag from the corner of D2 only selected D3:D5 and filled nothing, although the small square was still drawn on the cell right after we clicked OK.

Excel Options Advanced page with Enable fill handle cleared, and a drag that selects cells but fills nothing.
Click to expand
In File > Options > Advanced (1), Enable fill handle and cell drag-and-drop (2) is ticked by default; here it is cleared. With it cleared, dragging from D2 only selected D3:D5 and copied nothing (3).

With the box ticked again, our drag filled the column as before. If dragging still will not copy with the setting on, fix a fill series that will not work with the other checks in that guide.

Test two conditions with AND or OR

Put AND inside the test when each condition must be true. Our formula =IF(AND(B2>=70,C2="Yes"),"Pass","Review"), filled down with Ctrl + D, returned Pass, Review, Review and Pass. Cy scored 70 but had not submitted, so the AND test failed and Cy got Review.

Use OR when any one condition is enough. With =IF(OR(B2>=70,C2="Yes"),"Pass","Review") the same four rows all returned Pass, because Ben's 68 was saved by the submission and Cy's 70 by the score. Microsoft says AND and OR can take up to 255 conditions, but advises against more than a few because the formulas get hard to build and check.

Excel IF formulas with AND and with OR side by side on the same four score rows.
Click to expand
With AND, both the score and Submitted must pass (1), giving Pass, Review, Review, Pass (2). With OR, either one is enough (3), and all four rows show Pass (4).

A text test such as C2="Yes" only matches a cell that holds that word, so Y, or Yes with a stray space after it, makes that comparison FALSE. The AND version then returns Review, while the OR version can still pass the row on its score. If people type into a column like Submitted, make a drop-down list of Yes and No so the test always gets a value it expects.

Return three or more results with a nested IF

An IF can sit inside another IF to sort scores into bands. Our =IF(B2>=90,"A",IF(B2>=70,"B","C")), filled down I2:I5, returned A, C, B and B. Excel checks the tests from left to right and stops at the first one that is true, so 92 gets A before the second test is reached.

Order matters. With the tests reversed, =IF(B2>=70,"B",IF(B2>=90,"A","C")) returned B for the same 92, with no error and no warning, because 92 already passed the first test. Put the highest threshold first when you use >=, or the lowest first when you use <=.

Excel nested IF grade bands returning A for 92, and the reversed formula returning B.
Click to expand
With the highest test first (1), Ada's score of 92 (2) gets A and the column reads A, C, B, B (3). With the same tests reversed (4), the same 92 gets B (5).

Nesting gets hard to read fast. Microsoft's page on nested IF says Excel allows up to 64 IF functions but that doing so is not at all advisable, while the help card in Excel for the web says you can nest up to seven. Either way, keep it to a few levels, and for more bands Microsoft points to IFS, which returns the result for the first TRUE condition, so its tests need the same highest-first order.

Microsoft lists IFS for Excel 2019 and later and for Microsoft 365. When the rule is really a list of exact matches, such as a code that maps to a department, a lookup table is easier to keep up to date than a chain of IFs, and our guide shows how to look up a value in a table with XLOOKUP.

Use IF in Excel for the web

The same formula works in a browser. In Excel for the web in Microsoft Edge, typing =IF in D2 listed IF with the shorter tip Returns TRUE or FALSE based on a condition, along with IFERROR, IFNA and IFS. After the bracket, a larger card opened with a description, the example =IF(C2>B2, "Over Budget", "Within Budget") and a line of help for each part.

That card marks value_if_true as optional and notes that TRUE is returned if it is left out. On the desktop, an empty true slot gave us 0 instead, so fill in both results either way. Our finished =IF(B2>=70,"Pass","Review") showed Pass, and dragging the fill handle to D5 gave Pass, Review, Pass and Pass, the same as the desktop app.

Excel for the web IF help card with an example, then the formula filled down to D5.
Click to expand
In Excel for the web, the help card shows the optional parts in brackets (1), an example (2) and the note that TRUE is returned if value_if_true is left out (3). The finished formula (4) dragged down to D5 gives Pass, Review, Pass, Pass (5).

The fx button opens a panel called Insert function, with a Search for a function box and a Select category list that opened on Recently used for us. Type IF, select it and choose Next. The second page, Function arguments, marks only logical_test as required and previews Function result = "Pass", and Insert puts the formula in the cell.

Excel for the web Insert function panel with IF found, then its Function arguments page.
Click to expand
In Excel for the web, the fx button opens Insert function. Search for a function (1), select IF in the results (2) and choose Next (3). The next page has one box per part (4), shows the result before you insert it (5), and Insert puts it in the cell (6).

Filling works the same way. We selected F2:F5 and pressed Ctrl + D, and the web app returned Pass, Review, Pass and Pass. If a workbook opens in Viewing mode, Microsoft notes that editing commands won't work, so switch the mode button at the top right to Editing first.

Why an IF formula shows 0 or #NAME?

A 0 can mean a result slot is empty. We typed =IF(B2>=70,,"Review"), with nothing between the two commas, and Excel accepted it without a warning and showed 0 for Ada's 92. Typing "Pass" into the empty slot turned the 0 into Pass.

Microsoft's IF page lists 0 in the cell as a common problem when value_if_true or value_if_false has no argument, and its fix is to add text, TRUE or FALSE to both. If you want a result that looks blank, type two double quotes with nothing between them, "", which is the empty text Microsoft uses in its own examples.

#NAME? means Excel met a word it does not know. Our =IF(B3>=70,Pass,Review), with the words left unquoted, showed it, and so did =IFF(B5>=70,"Pass","Review") with the function name misspelled. Neither opened a message box, and each cell got a small green triangle and a warning button beside it.

Excel IF formulas showing 0 for an empty result and #NAME? for unquoted text and IFF.
Click to expand
Nothing between the commas (1) shows 0 (2). Pass and Review without quotes (3) give #NAME? (4), marked with an error button and no message box (5). IF typed as IFF (6) gives #NAME? again (7).

Put text results in quotes and check the spelling, and the error goes. With "Pass" and "Review" quoted, J3 showed Review, and correcting IFF to IF made J5 show Pass. If several functions fail the same way across a workbook, the cause is wider than one formula, and our guide to fixing Excel functions that do not work goes through it.

Why an IF formula shows the formula instead of a result

Two common causes look different. If the formulas across the whole sheet show as text, Show Formulas is on. It sits on the Formulas tab as an icon without a label on our screen, and its tip gives the shortcut Ctrl + `. Selecting it again brought our results back.

If only one cell shows its formula, that cell was probably formatted as Text before you typed. We set J4 to Text from the Number Format list on the Home tab, typed the IF formula, and Excel kept it as plain text. Changing the format to General, then pressing F2 and Enter, turned it into Pass.

Excel cell formatted as Text showing an IF formula, then General applied and Pass shown.
Click to expand
When the Number Format box says Text (1), the formula shows as typed (2). Change the format to General (3), then press F2 and Enter, and the cell shows Pass (4).

The F2 and Enter step matters, because it makes Excel read the cell again under its new format. If the cell is not formatted as Text, the formula has to start with = as its very first character, and you can fix formulas showing as text with that guide, which covers an apostrophe or space before the = and whole columns stuck as text.

Why an IF result does not update

If you change a score and the IF result stays the same, check Formulas > Calculation Options. Ours listed Automatic, ticked by default, then Partial, Manual, Format Stale Values and Compatibility Version. Excel for the web lists Automatic, Automatic Except for Data Tables and Manual in the same menu.

We switched to Manual and changed Ada's score to 50. The IF cell kept its old Pass, drawn struck through, and the status bar read Calculate. Pressing F9 recalculated it to Review, and switching back to Automatic made the result follow the score again.

Excel Calculation Options menu, a stale struck-through IF result, then Review after F9.
Click to expand
Formulas > Calculation Options (1) has Automatic ticked by default (2) and Manual below it (3). In Manual, changing B2 to 50 (4) leaves the old Pass struck through (5), and after F9 the cell shows Review (6).

Microsoft's fix for formulas that do not recalculate is the same, choosing Automatic under File > Options > Formulas. If Automatic is already on and formulas still do not update, fix formulas that are not calculating with the other checks in that guide.

A protected sheet stops you from editing the IF cell at all. On our protected sheet, typing in the formula cell brought up a message that it is on a protected sheet and that unprotecting it might ask for a password, and Review > Unprotect Sheet restored editing on our sheet, which had no password. If the workbook is yours or you have permission to edit it, unprotect an Excel worksheet before you change the formula.

How we tested this guide

We tested this in Excel for Microsoft 365 on Windows 11, version 25H2, and in Excel for the web in Edge, writing the formulas above on a made-up score sheet.

Frequently Asked Questions

What does the IF function do in Excel?

It checks a condition and returns one result when the condition is true and another when it is false. For example, =IF(B2>=70,"Pass","Review") shows Pass for a score of 70 or more and Review for anything lower.

Do I need quotation marks in an IF formula?

Yes, around text results such as "Pass", and without them our formula showed a #NAME? error. Leave numbers, cell references and calculations unquoted, so Excel uses them as values.

Why does my IF formula return 0?

One cause is an empty result slot, as in two commas with nothing between them. Type the result you want in both slots, or "" for a cell that should look blank.

How do I copy an IF formula down a column?

Drag the fill handle at the bottom right of the cell, or select the formula and the cells below it and press Ctrl + D. Excel moves the cell references one row at a time, so each row tests its own values.

How do I use IF with two conditions?

Put AND inside the test when both must be true, as in =IF(AND(B2>=70,C2="Yes"),"Pass","Review"). Use OR in the same place when either one is enough.

Can an IF formula return a calculation instead of text?

Yes. Leave the calculation without quotes and Excel works it out when that result is chosen, as in Microsoft's example that adds sales tax only when a cell says Yes. In quotes, Excel would show it as text.

What is the difference between IF and IFS?

IF gives one of two results and needs nesting for more. Microsoft says IFS checks several conditions in one function and returns the result for the first TRUE one, and lists it for Excel 2019 and later.

Share