How to Use the IF Function in Excel

Easily learn to perform logical tests

What to Know

  • The IF function is used to perform a logical test, i.e. whether something is true or not. 
  • The IF function syntax and arguments are =IF(logical_test, value_if_true, [value_if_false]).
  • For example =IF(A2>A3,"Bigger","Smaller").

This article explains how to use the IF function to perform a logical test all versions of Excel, including Excel 2019 and Microsoft 365. Several examples are outlined.

What is the IF Function?

The IF function in Excel is used to perform a logical test. A formula using this function is also called an IF statement or an if/then statement.

All formulas that use this function can have one of two results. The way it works, as we’ll see in the examples below, is that the formula is set up to test if something is true. If it’s true, one thing happens, but if it’s false, something else happens.

IF function examples in Excel

The IF function is one of several logical functions you can use in Excel. Others include AND, IFERROR, IFS, NOT, and OR.

IF Function Syntax & Arguments

Every formula that uses the IF function has a few parts to it:

=IF(logical_test, value_if_true, [value_if_false])

  • logical_test: The condition you’re testing. It’s required.
  • value_if_true: What should happen if logical_test is true. It’s required.
  • value_if_false: What should happen if logical_test is false. It’s optional.

Writing an Excel IF statement is easy if you read it a little differently: if the first part is true, then do this thing. If the first part is false, then do this other thing instead.

Keep these rules in mind:

  • Excel returns FALSE if logical_test is false and value_if_false is omitted.
  • To return text as value_if_true or value_if_false, it must be surrounded in quotes, with the exception of the words TRUE and FALSE.
  • The IF function isn't case sensitive.
  • Excel 2010 and newer allow up to 64 IF statements to exist within the same formula. Older versions of Excel are limited to seven.

IF Function Examples

Here are some of the different ways you can use IF formulas in Excel:

Write Text If Statement Is True

=IF(A2>A3,"Bigger","Smaller")
IF function example in Excel

This is a really basic example of an IF statement in Excel. The test is to see if A2 is larger than A3. If it is, write Bigger, otherwise write Smaller.

Do Math If Statement Is True

=IF(A2>A3,A2-A3)
Excel IF formula example with subtraction

This IF statement is written a little differently. Instead of having the value_if_true result be a word, it’s subtracting one value from another. So, if A2 is in fact larger than A3, the difference will be the result. If it’s not true, since we’ve omitted the value_if_false part, Excel returns FALSE.

Test the Statement With Math

=IF(A2/A3=5,A2/A3,"")
Excel IF formula with calculations

Another way to write an IF statement is to perform a calculation within the logical_test section. The IF condition here is A2/A3=5. If that's true, then we do the calculation A2/A3. If it’s not equal to 5, we want the result to be nothing, so we use double quotes.

Test If a Date Is Today

=IF(A2=TODAY(),"This is today","")
IF function used with TODAY function

Other Excel functions can be used within an IF statement. In this example, we’re using the TODAY function to check if A2 is today’s date. If it is, the formula writes This is today, otherwise nothing is written.

Using AND With IF Formula

=IF(E2<=TODAY(),"Now","Soon")
=IF(AND(F2="Now",D2>=(B2-C2)),"Yes","No")
IF statements used with the AND function in Excel

This example of the IF function is a bit more involved. The idea here is to see if an item we owe money on is past due, and if it is, we're seeing whether or not that amount is in our budget so that we can pay it off. If both of those statements are true, we can see in Column G if it's time to pay it.

IF(E2<=TODAY(),"Now","Soon") is in the Urgency column. It tells us if the item is overdue or if it's due today by comparing the due date with today's date. If the due date is today or in the past, Now is written in Column F, otherwise we write Soon.

The second IF statement is still structured like an IF statement even though AND is being used in it. The bold part here is where the AND function sits, and since it's within the first set of commas, it's what we're using as logical_test:

=IF(AND(F2="Now",D2>=(B2-C2)),"Yes","No")

Here it is written differently to show that it's just like other IF statements:

=IF(test this AND function,write Yes if it's true, or write No if it's false)

Within the AND function are two IF statements:

  • F2="Now" is part of the formulas in Column G. It checks whether Now is in F2.
  • D2>=(B2-C2) has two parts: it first performs the calculation B2-C2 to see how much we have left to pay on the item, and then it checks the available budget in D2 to see if we have the money to pay it off.

So, if we owe money now, and we have the funds to pay it off, we're told that Yes, it's time to pay the item off.

Nested IF Statement Examples

Nested IF statements is what it's called when more than one IF statement is included in the formula. The setup is nearly identical, but instead of closing the parenthesis at the end of the first set, we put a comma and write another statement.

Two IF Statements In One Formula

=IF(B2="F","Class A",IF(B2="M","Class B"))
Nested IF statements in Excel

This first example is used to categorize students by their gender, where females are assigned Class A and males Class B. The formula checks for F and M in B2 and then writes either Class A or Class B depending on the statement that's true.

The number of parenthesis you need at the end of a formula with nested IF functions is the same number of times IF is written. In our example, IF is written twice, so we need two parentheses at the end.

Three IF Statements in One Formula

=IF(A2=TODAY(),"This is today",IF(A2<TODAY(),"Old date",IF(A2>TODAY(),"Future date")))
Nested IF statements in Excel that check today's date

Here’s an example of a formula with multiple IF statements. It’s identical to the TODAY example above but with one more logical test:

  • The first set checks if A2 is today’s date and returns This is today if it is.
  • The second tests if today is greater than A2 to determine if A2 is an older date, and returns Old date if it is.
  • Finally, there's a test to see if today’s date is less than the date in A2, and the formula returns Future date if it is.

Copy Price If Statements Are False

=IF(C2="Bill","",IF(C2="Food","",B2))
IF Excel function example transactions

In this final nested IF formula example, we need to quickly identify the total amount of all the purchases that don't fall under a certain category. We're totaling all of our unnecessary purchases, and with a long list, this is the best way to do it. We've determined that any item description that says Bill or Food is important, so the price, in B2, needs to be shown for all the other items.

This is what's happening:

  • C2="Bill","": If C2 says Bill, leave the cell blank.
  • C2="Food","": If C2 says Food, leave the cell blank.
  • B2: If either of those statements are false, write what's in B2.

What this formula leaves us with is a list of prices that we can then total with the SUM function to quickly assess how much money was spent on items that we didn't need.

An Easier Way to Write Nested IF Statements

As you build more and more into the formula, it can quickly become unmanageable and hard to edit later. One way to make nested IF statements easier to work with is to put a line break after each statement, like this:

=
IF(A2=TODAY(),"This is today",
IF(A2<TODAY(),"Old date",
IF(A2IF(A2>TODAY(),"Future date")))

To do this in Excel, we need to edit from the formula bar:

  1. Select the formula bar at the top of Excel.

  2. Put the mouse under the text space until the cursor changes to a double-sided arrow, and then click and drag the box down to provide more working space.

  3. Put the cursor after the equals sign and press Alt+Enter (Windows) or Ctrl+Option+Enter (Mac). This puts the rest of the formula on a new line.

  4. Repeat Step 3 before each IF statement so that every instance is put on its own line.

    Nested IF statements in Excel with line breaks
Was this page helpful?