How to Use the IF Function in Excel (Learn in 5-Minutes!)


Introduction

I’m sure we do use the word “If” quite commonly in conversations in our lives. It usually indicates a condition that has to be satisfied before something can happen. Similarly, such a statement can be expressed using an IF function in Excel too! That’s why I’ve done some sourcing on behalf of you the simplest and quickest explanation on how you can use this IF function in Excel. This post should provide a basic introduction to the function and how you can use it too.

How is the IF function in Excel used?

Put simply, the IF function works by taking in 3 different inputs – the logical test, the value to display if true and the value to display if false. The function takes a number or text and checks this against the test. Only either true or false can be displayed. If the test results in true, the value if true will be displayed. On the other hand, if the test results in false, the value of false will be displayed. Depending on the logical test, the displayed output can vary.

However, most Excel users tend to overlook the different uses for the IF function. In fact, the IF function can provide useful calculations using its simple logical concepts.

What is the logical concept behind the IF function?

Naturally, when hearing the word “If” and using in sentences you are already using a logical concept in your mind. Unknowingly, we all tend to put situations to the test: “Is this true?”, “Is this false?”. This is somewhat intuitive to us as humans but Excel, being a computer program, has to use this simple logical true/false checking mechanism to replicate this concept through the IF function.

As shown in the simple flowchart diagram above, there are two pathways where a value can take, either a true result or the false result.

The Logical Test

The logical test in this function is essentially just a simple true/false test. It compares and checks if a certain value (as chosen by you) matches criteria. The result is two-way, either a true result or a false result. This concept of logic helps the formula decide what to display – the value if true or the value if false.

Syntax

=IF (logical_test, [value_if_true], [value_if_false])

The cell block above shows the syntax of the IF function, which basically means how the function is structured. The formula begins with an equal sign followed by “IF” and 3 arguments enclosed in brackets.

Arguments

logical_test
value_if_true
value_if_false

If you’re new to Excel or programming concepts, the concept of arguments may seem complicated and confusing to you. But not to worry, the arguments of each function are simply what you have to key into the formula bar in order for it to work.

Most arguments are required, meaning they are a must to key in while some are optional, meaning they can be left blank. Arguments are usually separated from each other using a comma “,”.

The 3 different arguments listed above are the inputs a user has to provide when filling up the formula. The logical test is typically used with an equals “=” sign, to indicate the condition. Its value is then evaluated and given the true/false outcome.

The value_if_true argument is an optional argument that returns the result when evaluated true. The value_if_false argument is also an optional argument but returns the result when evaluated false.

When is the IF function used?

The IF function is typically used when a user needs to check if the value in a cell matches that of the condition (one that’s made by the user). The function then displays one of two possible values, according to the user’s condition.

Here are some possible use cases for the IF function:

  • Displaying a Yes/No result based on whether a checkbox is ticked for an inventory list
  • Tracking personal expenses to see if they have reached a certain limit

IF Combined with AND & OR

Being a versatile logical function, the IF function can be complemented with other functions within Excel. Some of these include the AND function and the OR function. For example, when used in combination, we can find out if a certain number matches the multiple criteria that we want and show us a result based on the multiple criteria that we have set.

The AND function can be used to combined multiple conditions for the logical test to fulfill. As the name of the function goes, anything within the parentheses of this function will combined in an additive manner.

=IF(AND(A3 > 15, A3 < 20),"Yes","No")

Meaning: If Cell A3 is larger than 15 and lesser than 20, display “Yes”, else display “No”.

The OR function is also a great option for combinations with the IF function. Similar to what we did with the AND function, the OR function is placed within the logical test argument. It is used to show the either/or style of evaluation.

=IF(OR(A3 > 15, A3 < 20),"Yes","No")

Meaning: If Cell A3 is larger than 15 or lesser than 20, display “Yes”, else display “No”.

Nested If Formulas

Nested IF functions are formulas that have multiple layers of IF functions nested within each other to allow for more conditional testing and more specific output. For a nested IF formula, a minimum of one IF function should be nested into the parentheses of the other.

The term nested is used because the innermost functions are put inside the outer-few layers like eggs in a nest. In many ways, this may seem like an onion with multiple layers. Within each layer, we have another layer. For the Nested IF formulas, a maximum of 7 layers is allowed in Excel. Now that’s a lot of layers.

Which IF function goes first in Nested IF formulas?

The innermost function is computed first before moving outwards, the following outer functions then take into consideration of the output of the previous function.

Example:

=IF(=IF(OR(A3 > 15, A3 < 20),"Yes","No"), "Within range", "Outside range"

This example shown above is a nested IF function that has a total of 2 IF statements that build upon the logical concepts of each other.

The IF function shown in orange is the inner function, and therefore is processed in Excel first. The IF function shown in blue text is the outer function and is processed second. The second function “wraps” itself around the first. This concept of wrapping allows us the flexibility and functionality of 2 IF statements.

Logical Operators

As mentioned in the arguments section above, the logical test argument usually includes an equal sign. However, the equals sign is one of several logical operators. Logical operators are part of our lives more than you think!

For example, we think about them when considering the prices of items – which price is greater than which. Another typical example is discount minimum purchases, where we have to consider buying an item with greater than a certain amount to qualify for a discount.

Similarly, these concepts are built into Excel through operators shown in symbols. Apart from the equals sign, there are several other mathematical operators commonly used among Excel users as well. The summary table shows the possible operators and their respective meanings.

Logical OperatorMeaning
=equals to
>greater than
>=greater than or equal to
<less than
<=less than or equal to
<>not equal to

With the versatility of these operators, we can create unique conditions in our logical test to create and fulfill a specific criteria that we want.

If functions with multiple conditions

When used correctly and in combination with other functions, the IF function can perform some complicated computations. Multiple IF statements can be built together up to 7 times, which allows for building up a strong logical structure similar to a flowchart! These nested IF functions can be used together with the AND and OR functions to build complex logical calculations.

Conclusion

In conclusion, the IF statement can be a simple and plain function but that’s what makes it all the more powerful! It is essentially a crucial building block that forms the foundation for more complicated and versatile use cases. Hopefully, this quick introductory article provided a simple and friendly approach to the IF function.

If you would like to read more articles on Excel, you can access them here. Thanks for reading!

My Favorite Data Learning Resources:

Here are some of the learning resources I’ve personally found to be useful as a data analyst and I hope you find them useful too!

These may contain affiliate links and I earn a commission from them if you use them.

However, I’d honestly recommend them to my juniors, friends, or even my family!

My Recommended Learning Platforms!

Learning PlatformWhat’s Good About the Platform?
1CourseraCertificates are offered by popular learning institutes and companies like Google & IBM
2DatacampComes with an integrated coding platform, great for beginners!
3PluralsightStrong focus on data skills, taught by industry experts
4StratascratchLearn faster by doing real interview coding practices for data science

My Recommended Online Courses + Books!

TopicOnline CoursesBooks
1Data AnalyticsGoogle Data Analytics Professional Certificate
2Data ScienceIBM Data Science Professional Certificate
3ExcelExcel Skills for Business Specialization
4PythonLearning Python for Data Analysis and VisualizationPython for Data Analysis
5SQLIntroduction to SQLSQL: The Ultimate Beginners Guide: Learn SQL Today
6TableauData Visualization with TableauPractical Tableau
7Power BIGetting Started with Power BI DesktopBeginning Microsoft Power BI
8R ProgrammingData Science: Foundations using R SpecializationLearning R
9Data VisualizationBig Book of Dashboards

To see all of my most up-to-date recommendations, check out this resource I’ve put together for you here.

More Articles For You

Data Analyst VS BI Analyst: 7 Key Differences

Austin

A budding data analyst with great interest in writing all things about data!

Recent Posts