How to Use Excel's IF Function

Create IF, THEN statements in Excel to create dynamic values

Blank Excel worksheet

Ted French

The IF function in Excel is a powerful way to add decision making to your spreadsheets. The IF function tests a specified condition to see if it's true or false, and then carries out a specific set of instructions either way.

For example, you might use an IF, THEN statement in Excel to show information only if the value of a specific cell is greater than 20. If it is, the formula will say "JUST RIGHT," but if the value is not greater than 20, then the formula will say "TOO SMALL."

There's a wide variety of conditions you can enter into the formula beyond our example, and the same goes for the action that will occur if the statement is true or false. Keep reading for some other examples of how to use the IF function in Excel.

Tip: The IF function is often used with OR and AND by nesting the functions into one formula.

IF Function Syntax

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

This is the proper syntax of the IF function:

=IF(logic test, value if true, value if false)

The types of actions the function can carry out include:

  • Performing calculations using a formula or entering number data
  • Displaying text statements in designated worksheet cells
  • Leaving the target cell blank

Simple IF Function Example

Before moving on to more complex calculations, let's look at a very simple example of an IF statement. 

Our spreadsheet is set up with cell A1 as 4. We can use the following formula against A1 to indicate whether or not the value is larger than five. 

=IF(A1>5,"Large","Too small")

As you can see, the "value if true" value is set to Large and the "value if false" value is set to Too small. So, because four is not larger than five, entering this particular formula will spit out the text Too small in whatever cell the formula is sitting in. In our example, B1 is where the formula was written, so the words Too small are written there.

If we were to make another value in A2 with 60 written in it, and then use the same formula on that cell, it'd look like this:

=IF(A2>5,"Large","Too small")

With this one, the IF statement creates the value Large because the statement — if A2 is bigger than the value 5 — is true.

The logic test is always a comparison between two values. Comparison operators are used, for example, to see if the first value is greater than or less than the second, or equal to it.

Performing Calculations With the IF Function

The IF function in Excel with a calculation

Ted French

The IF function can perform different calculations depending on whether the function returns a true value or not.

In the image here, a formula is used to calculate a deduction amount based on employee earnings. The logic test compares an employee's earnings located in Column B to see if they're greater than $30,000.00.

=IF(B2>30000,B2*1%,300)

Once the function determines if the logic test is true or false, it carries out one of the two actions specified by the value if true and value if false arguments.

In this example, B2 is not larger than 30,000, so the "value if false" value is entered, which is 300 in this case. However, B3 is bigger than 30,000, so the first part of the formula is what happens (B3*1%), so the result is 354.78.

As you can see, you can have one part do a calculation and another just input a predefined value (300 in this example). You might have a number or word entered without doing a calculation if you want to use that value later in a different formula.

Note: Neither the dollar sign nor comma separator is entered with the numbers 30000 or 300 in the function. Entering either one or both creates errors in the formula. You can format the cell itself but don't do it from inside the formula.

Excel functions use the comma as a separator between arguments, so entering commas with numbers leads Excel into believing that more than three arguments have been entered for the IF function.

Since dollar signs are text characters, entering them with numbers appears to Excel as a typing error.

Using Blanks and Text Statements With the IF Function

The IF Excel function with words surrounded by quotes

Ted French

Having text displayed by an IF function rather than a number can make it easier to find and read specific results in the worksheet.

In this example, the IF function is set up to test whether students taking a geography quiz correctly identify the capital cities for a number of locations in the South Pacific.

The logic test of the IF function compares the students' answers in Column B with the correct answer entered into the argument itself.

If the student's answer matches the name entered into the logic text argument, the word Correct is displayed in Column C. If the name does not match, the cell is left blank.

=IF(B2=“Wellington”,"Correct"," ")

To use single words or text statements in an IF function, each entry must be enclosed in quotes like "Wellington" or "Correct".

As shown for the "value if false" argument in the example above, cells are left blank by entering a pair of " " (empty quotation marks.