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.
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.
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.
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.
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.
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.
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.
Use IF with calculations
Another way to use the IF function is with a calculation. You can use it to subtract in Excel, or multiple in Excel, or simply add values. 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.
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.
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.
