How to Write an IF Then Formula in Excel (Examples)

It’d be quite cool and easy if you could automate your tasks by telling software to enter data for you based on preset conditions.

This is no longer a thought only – in fact, the IF-THEN formula in Excel lets you do this in a snap.

Want to learn how do you do it? Join me until the end of this tutorial to learn everything about how to write the IF-THEN statement in Microsoft Excel.

Your free practice workbook for this guide is available for download here. Grab it, and let’s go spread-sheeting all the way 🚴‍♀️

Table of Contents

What is the IF-then statement

IF-THEN statements in Excel refer to the IF function of Excel.

By IF-THEN statements we tell Excel to check if a logical test is true or false, if true THEN do this, and if false, THEN do that.

The syntax of an IF-THEN statement looks like below 👀

• Logical_test: The first argument is a logical test which is a required argument.
• Value_if_true: The second argument is the value you want to be returned by the IF function if the logical test turns true. It is an optional argument and can be omitted only if the value_if_false argument is supplied.
• Value_if_false: The third argument is the value you want to be returned by the IF function if the logical test turns false. It is an optional argument and can be omitted only if the value_if_true argument is supplied.

Putting these arguments into action through practical examples will better explain how an IF-then statement works.

Using the IF-then statement in Excel

This has to be my favorite section of this article since it’s all about practical examples of using IF-then statements in Excel.

Let’s go 🚀

Basic Example

What’s a better way to start than to walk through a basic example?

So here we have a set of students with their results.

Anyone who scores above 50 has done well, and anyone who scores below 50 has had an average performance.

In terms of an IF-then statement, it comes out as:

If a student scores above 50 THEN grade him good, otherwise grade him Average.

Okay – who will do the grading for each student now? Excel, for sure.

Let’s put it to work then.

Pro Tip!

The IF-then statement is always used together with the following logical operators:

• =: Equals to
• <: Less than
• >: Greater than
• <>: Does not equal to
• >=: Greater than or equal to
• <=: Lesser than or equal to

Step 1) Write the IF-then statement by writing the IF function as follows:

Step 2) As the logical test, specify the condition to check if the scored marks of each student are more than 50.

Step 3) Specify the value_if_true that we want to be returned if the logical test turns true i.e., Good.

Step 4) Specify the value_if_false to be returned if the logical test returns false i.e. Average.

Step 5) That’s it – Hit enter.

Step 6) Drag the formula down across the whole list of students.

All students who did good or average have been identified based on the IF-then statement based on their scores. Isn’t it amazing! 😎

IF-then statement function with text

Using the IF-then statement with text is fun.

For example, we have some grocery items and their stock status 🛒

I quickly want a list of whether an item must be reordered or not. For items that are out of stock, we will place orders for them, and for those that are still in stock, we will hold their orders.

To do this, we will deploy the IF-then statement as below 🧾

Step 1) Write the IF function as follows:

Step 2) Write the logical test to check if the status for each item in Column B is “Out of stock”.

Step 3) Give in the value_if_true as “Place Order”.

This tells Excel to check if the status for any grocery item is “out of stock”, and then return the text “Place order” for it.

Step 4) Supply the value_if_false as “Hold” in the following formula.

Step 5) Press enter.

Step 6) Drag the formula down the whole list of grocery items to have the same results populated for all.

Took a snap to have the ordering status ready 📹

IF-then statement with case-sensitive text

In the above example, we have some text in capital case, some in small case, and some in sentence case.

Although the logical test in the IF function uses a normal case, the results are unaffected by the case. This tells that the IF-then statement is case insensitive.

However, if you want case-sensitive results, what happens then?

It is still possible, however, for that, we’d have to nest in the EXACT function. The EXACT function returns a true/false based on whether both the supplied texts are the same (including case sensitivity) 🆎

Step 1) Write the logical test for the same above criteria by nesting the EXACT function as below:

The EXACT function will check if the text in Cell B2 is exactly equal to the supplied text i.e., “Out of Stock”.

Step 2) Complete the remaining IF function by supplying the value_if_true and value_if_false.

Step 3) Drag these results down the list.

You see this time, Excel has returned “Place Order” only for those items that for which the status “Out of Stock” is in the same case as supplied to the EXACT function.

For all other items, even though the text remains the same, due to inconsistent cases, the IF-then statement returns the value_if_false.

IF-then statement for partial match

After we have seen the IF-then statement work with numbers and case-sensitive and insensitive text, how about a situation where you only want a partial match?

For example, in the below scenario, I have a list of employees along with their hiring status 👩‍💼

I quickly want to see which employees are selected and which are not selected. Although some employees are already hired and others are to be hired, however, both fall within the selected category.

Whereas, the remaining fall in the “not selected” category ❌

Pro Tip!

Long story short, we need the IF-then statement to see if the onboarding status of any employee contains the text string “hired”, which means he is selected. Whether it is “Hired” or “To be Hired”. This is a partial match.

Let’s see if we can achieve this category automation by using the IF-then statement of Excel.

Step 1) Write the SEARCH function to search for the text string “hired” in Cell B2.

Tells Excel to search for the text string “hired” in Cell B2 and return the number of the text string from where it starts.

Step 2) Drag it down the list.

Very clearly – It returns the number of text strings from where the given text starts in the referred cell. And for cells where the given text string doesn’t exist, it returns the #VALUE! Error.

Step 3) Now we will wrap this function into the ISNUMBER function as follows:

The ISNUMBER function checks if the supplied value is a number and returns TRUE / FALSE accordingly. In our example, for all the cells where the text string “hired” exists, the SEARCH number returns a number, and the ISNUMBER function returns TRUE.

For all other cells, the SEARCH number returns #VALUE! Error and the ISNUMBER function returns FALSE.

Step 4) Nest this function into the IF-then statement as the logical test argument.

Step 5) Give in the value_if_true as “Selected” and value_if_false as “Not Selected”.

Step 6) Now we will wrap this function into the ISNUMBER function as follows:

Step 7) And now press enter to have the result.

Step 8) Drag these down across the whole list of employees.

There you go! Whether an employee is already hired or is about to be hired, the IF-then statement of Excel tells you if he is selected or not using the partial match technique.

Pro Tip!

We have used the SEARCH function in this example to yield results for partial matches. However, the SEARCH function performs a case-insensitive search.

In other words, it didn’t matter if the text string hired was written in lower case, upper case, sentence case, or toggle case within the cell. The SEARCH function would still have searched it.

However, if you want to perform a case-sensitive search, then you must use the FIND function instead of the SEARCH function 💡

No big deal, just the above function would have changed as follows:

Nested If-then statements

That’s all about using simple IF-then statements.

Nevertheless, things are not always as simple as if this happens, do this otherwise that.

Sometimes, you might want things to work like “IF XYZ is true, do this, otherwise, check if another condition is true, then do that, and so on..”

In other words, a simple IF-then statement can only check for a single condition to be true or false at a time. But if you want it to check for multiple conditions in a sequential order, a nested IF-then statement is all that you need 💪

For a quick example, for the same marks of students (see below):

I want to grade them as anyone who:

• Scored above 60 gets Excellent
• Scored above 50 gets Good
• Scored above 40 get Average
• Scored equal to or less than 40 get Poor

These are four conditions, let’s see how we work them all out using a nested IF-then statement.

Step 1) Begin writing the IF function and write the first logical test along with its value_if_true.

Step 2) In the place of the value_if_false, nest another IF function and write the second logical test with its value_if_true.

This tells Excel that if the first logical test fails, instead of returning the value_if_false, it should run the next IF function 👩‍🏫

Step 3) In the place of the value_if_false of the nested IF function, nest another IF function and write the second logical test with its value_if_true.

Step 4) Once again – nest another IF function as the value_if_false of the latest IF function.

Step 5) For this last nested IF function, we are leaving the value_if_false omitted.

Step 6) Add enough closing parentheses (as many IF functions are nested) to close the function

Step 7) We are done – Hit enter.

Step 8) Drag the formula down the whole list.

This time the IF-then statement evaluates 4 conditions and returns results accordingly 🧐

Conclusion

IF-then statements make one of the most amazing functions of Excel. It not only automates most of our tasks but is also easy to use. From beginners to advanced Excel users, name someone who is not a fan of the IF function of Excel.

There’s so much more that you can do in Excel other than using IF as a basic function. What and how? Read the following Excel tutorials of mine to learn that.

• How to Use Multiple IF Statements in Excel (Step-by-Step)
• How to Use the IF and AND Functions in Excel (Examples)
• How to Use the IFNA Function in Excel: Step-by-Step (2024)