Use the Excel IF function when you want to test something, and show one result if the test result is True, and a different result if the test result is False.

NOTE: In many cases, another Excel function would be more efficient than a complex IF formula. See the examples below.

The Excel IF function is helpful if you want to check something on a worksheet, then show result A or B. This short video shows the steps for setting up a simple IF formula. There are written steps below.

If you need to do anything more than a simple test, another Excel function might be better. For example:

- Convert student grades from numbers to letters -- use VLOOKUP or INDEX/MATCH
- Choose an interest rate, based on transaction date -- use HLOOKUP
- Show a specific value instead of an error -- use IFERROR (Excel 2007 and later)

There are 3 parts (arguments) to the IF function:

- TEST something, such as the value in a cell.
- Specify what should happen if the test result is TRUE.
- Specify what should happen if the test result is FALSE.

In this example, the Total in cell E7 should only show an amount if the Quantity has been entered in cell D7.

These are the 3 parts (arguments) to the IF function for this example:

- TEST cell D7, to see if it is empty (
**D7=""**),. - If D7 is empty (TRUE), the cell with the Total formula will show nothing
(
**""**). - If D7 is not empty (FALSE), the cell with the Total formula will multiply
Price x Quantity (
**C7*D7**)

The completed formula is: **=IF(D7="","",C7*D7)**

In the screen shot below, cell D7 is empty (TRUE), so the IF formula in cell E7 also looks empty.

When cell D7 is not empty (FALSE), the Total cell shows the value of Price x Quantity.

You can nest one or more IF functions within another IF function, to create more options for the results. In this example, the formula has two IF functions, to check both the Price and the Quantity cells

- IF the Price cell is empty (""), the total will be empty ("")
- Then, IF the Quantity cell is empty (""), the total will be empty ("")
- Otherwise, multiply Price x Qty to calculate the Total

- When building nested IF formulas, the order in which the tests are listed might be important. See the section below -- the hardest tests must come first.
- Some nested IF formulas can be replaced by a VLOOKUP formula, or INDEX and MATCH, for more flexibility. See the example of converting numeric grades to letter grades for students.

In this example, the orders are stored in a named Excel Table. As a result, there are structured references in the formula, that show the field names, instead of normal cell references.

For example, to calculate a simple total (Price x Quantity), this formula
would be used: **=[Price] *** **[Qty]**

In Example 1, only the Quantity cell was checked in the IF formula. Here is that formula from Example 1, written with structured references:

**=IF([Qty]="","",[Price]*[Qty])**

To check both the Price and Quantity cells, **another IF will be added**
to the existing formula:

**=IF([Price]="","",**IF([Qty]="","",[Price]*[Qty])**)**

- If the Price cell is empty (TRUE), the cell with the Total formula
will show nothing (
**""**) - If the Qty cell is empty (TRUE), the cell with the Total formula will
show nothing (
**""**) - If neither cell is empty, the total is calculated (
**[Price]*[Qty]**)

The results for each possible scenario are shown below.

When nesting IF functions, be sure to put the hardest tests first, or the results could be incorrect.

For example, in the following formula, there are two tests:

**=IF(B2>=20000,"Good",IF(B2>=10000,"Average","Poor"))**

If the amount in cell B2 is 25000, the result would be "**Good**",
because it is greater than 20000.

However, if the tests are reversed, the formula would check for >=10000 first.:

**=IF(B2>=10000,"Average",IF(B2>=20000,"Good","Poor"))**

Now, if the amount in cell B2 is 25000, the result of the formula would
be **"Average"**, because the results of that first test
are TRUE.

In this example, sales tax will be applied to local orders. On the order form, there is a check box that is linked to cell G11.

- For local orders, click the check box, to add a check mark, and the linked cell shows TRUE
- If the check mark is removed, the linked cell shows FALSE

The IF formula in cell E12 checks the linked cell (G11)l, to see if it contains "FALSE". If so, the tax amount is zero.

If cell G11 does not contain "FALSE", the subtotal in cell E10 is multiplied by the Tax Rate in cell D12, to show the tax amount.

**=IF(G11=FALSE,0,E10*D12)**

To follow the examples in this tutorial, download the sample IF workbook. The zipped file is in xlsx format, and does not contain any macros.

Last updated: July 9, 2021 7:48 PM