Skip to main content

How to use the Excel IF function

The IF function is one of the most commonly used functions in Microsoft Excel. With it, you can test a value to see if it meets criteria. If it does, then display one result and if it doesn’t, then display a different result. This is basically an If, Then scenario.

You can use the IF function with text, numbers, or any combination, giving you flexibility. Here, we’ll explain the syntax for the formula and arguments followed by a few examples to help you use the function for your own data.

Difficulty

Moderate

Duration

15 minutes

What You Need

  • Microsoft Excel

The IF function formula syntax

The IF function has a simple syntax with only three arguments, making it easy to use. The syntax is IF(test, if_true, if_false) where only the first two arguments are required.

Test: This is the value you want to test and the criteria you want to use. Since you're comparing data, you can use a cell reference with the condition to compare it to.

If_true: This is the result to display if the value meets the criteria. This can be text or a number.

If_false: This is the result to display if the value does not meet the criteria and can also be text or a number.

Now that you have the syntax and arguments for the formula, let’s look at some examples for how to use the IF function in Excel.

Use IF with text

You can use text for the test as well as the results. Be sure to include the text within quotation marks.

For this example, we have student grades. We want to display Fail if the grade is an F and Pass for any other grade. The grade is located in cell B2.

Step 1: Select the cell where you want to display the result. This is the cell where you’ll enter the formula.

Step 2: Type the formula into the cell, replacing the arguments with your own: =IF(B2=”F”,”Fail”,”Pass”).

To break down the formula, if the text in cell B2 is (equals) an F, then display Fail. If it’s not an F, display Pass.

IF function using text for the test and results.
Image used with permission by copyright holder

Step 3: Press Enter or Return.

You’ll then see the result in your cell. If you want to use the same formula for additional cells, use the fill handle to drag the formula to the other cells.

Dragging to copy the IF function formula in Excel.
Image used with permission by copyright holder

Use IF with numbers

It’s just as easy to use the IF function to test numbers as text. With numbers, you do not have to place the values within quotation marks.

For this example, we have a product order. If the order subtotal is greater than or equal to $100, the customer gets free shipping. If it’s less than $100, they pay $49.99 for shipping. The subtotal is located in cell D6.

Step 1: Select the cell where you want to display the result to enter the formula.

Step 2: Type the formula into the cell and replace the arguments with yours: =IF(D6>=100,0,49.99).

To break down this formula, if the value in cell D6 is greater than or equal to 100, then display nothing (zero). If it’s not, then display 49.99.

IF function using numbers for the test and results.
Image used with permission by copyright holder

Step 3: Press Enter or Return.

You’ll then see your result in the cell with the formula. To give it a test, you can change the subtotal to be sure that the shipping amount changes accordingly.

IF function using numbers for the test and results.
Image used with permission by copyright holder

Use IF with text and numbers

You can use text or a number for the test and text or a number for each result. Again, this gives you great flexibility with the IF function in Excel.

For this next example, we’ll go back to our student grade sheet, but this time we’ll use the numeric grade for the test. The numeric grade is in cell C2 and we want to display Pass for grades above 1.0 and Fail for grades below 1.0.

Step 1: Select the cell where you want to display the result and enter the formula.

Step 2: Type the formula into the cell, replacing the arguments with yours: =IF(C2>1,”Pass”,”Fail”).

To break this one down, if the value in cell C2 is greater than 1, display Pass. If it’s not, display Fail.

IF function using a number for the test and text for the results.
Image used with permission by copyright holder

Step 3: Press Enter or Return.

Like the example directly above, you can test your formula by changing a grade to confirm that the correct result displays, or copy the formula down to the other cells to check the other grades.

Dragging to copy the IF function formula in Excel.
Image used with permission by copyright holder

Use IF with calculations

Another way to use the IF function is with a calculation such as addition, subtraction, or multiplication. For instance, if our value meets the condition, we’ll perform a calculation. If it doesn’t, then we’ll do something else.

Let’s use our product order and shipping fee example. We’ll automatically add the fee if the subtotal is less than $100. If it’s $100 or more, we’ll simply display that subtotal because the shipping is free. The subtotal is in cell D6.

Step 1: Select the cell where you want to display the result and enter the formula.

Step 2: Type the formula into the cell and replace the arguments with your own: =IF(D6>=100,D6,D6+49.99).

Breaking down this formula, if the value in cell D6 is greater than or equal to 100, simply display the value in cell D6. If it’s not, then add 49.99 to the value in cell D6.

IF function using a number for the test and calculation for the results.
Image used with permission by copyright holder

Step 3: Press Enter or Return.

Like the other formulas above, you can quickly test your formula to be sure the result changes for different amounts.

IF function using a number for the test and calculation for the results.
Image used with permission by copyright holder

Now that you know how to use the IF function in Excel, take advantage of this useful tool to test values of your own or those you pull in from a PDF file.

For more, check out how to merge and unmerge cells or how to use concatenate to combine data in Excel.

Editors' Recommendations

Sandy Writtenhouse
Sandy has been writing about technology since 2012. Her work has appeared on How-To Geek, Lifewire, MakeUseOf, iDownloadBlog…
How to delete a page in Word (including a blank page)
A person using MS Word.

Microsoft Word is one of the best word processors of 2024, allowing you to craft powerful documents with an intuitive interface. If you're working with large files that span multiple pages, you'll be happy to know that Microsoft gives you several easy ways to delete a page in Word, including both blank pages and those you simply no longer need.

Read more
How to change your background in Microsoft Teams
The Microsoft Teams Communities features is now available for Microsoft 365 Personal and Family.

Microsoft Teams is an incredible video conferencing and workflow platform. Similar to programs like Zoom and Skype, Teams allows you to do more than just confer with your teammates. You’ll be able to share files, photos, and videos on these calls too, and the Teams app is available for most desktop PCs and mobile devices. But sometimes the most poignant features are the ones you have to dig a little deeper for.

Read more
How much does an AI supercomputer cost? Try $100 billion
A Microsoft datacenter.

It looks like OpenAI's ChatGPT and Sora, among other projects, are about to get a lot more juice. According to a new report shared by The Information, Microsoft and OpenAI are working on a new data center project, one part of which will be a massive AI supercomputer dubbed "Stargate." Microsoft is said to be footing the bill, and the cost is astronomical as the name of the supercomputer suggests -- the whole project might cost over $100 billion.

Spending over $100 billion on anything is mind-blowing, but when put into perspective, the price truly shows just how big a venture this might be: The Information claims that the new Microsoft and OpenAI joint project might cost a whopping 100 times more than some of the largest data centers currently in operation.

Read more