Bottom line: Learn how to write an IF formula in Excel using the IF function.
Skill level: Beginner
Everything You Do Is An IF Statement
Everyday we do hundreds (if not thousands) of IF statements in our head. Every decision we make is an IF statement:
- IF I eat breakfast, THEN I will have energy for work/play.
- IF I do the laundry, THEN my wife will be happy.
- IF I brush my teeth, THEN more people will talk to me.
- IF I read the rest of this article, THEN I might learn something about Excel…
My favorite example is the old traffic light analogy.
- IF the light is green, THEN go.
- IF the light is red, THEN stop.
- IF the light is yellow, THEN what??? We'll talk about that a little later…
These IF statements are also referred to as logical tests. We are testing for a condition (the color of the light), then taking an action based on whether the condition is TRUE or FALSE.
Writing your first IF formula in Excel is an exciting moment. It gives you the feeling of how you can program Excel to make it interactive.
Logical Tests – You Don't Always Need the IF Function
Before you write your first IF statement (formula), it's best to get an understanding of how Excel makes comparisons.
Type your first name in cell A1 and your last name in cell B1. Then type the following formula in cell C1:
If your first name and last name are different, then the result will be FALSE.
Now type the number 2 in cell A2 and B2. Then type the following formula in cell C2:
The result will be TRUE.
We are using the equal sign “=” to compare the two values. The result will always be TRUE or FALSE (unless one of the cells contains an error).
This also works with the greater than “>” and less than “<” symbols. These are called comparison operators.
Comparison Operators in Excel
|<>||Not Equal To|
|>=||Greater Than or Equal To|
|<=||Less Than or Equal To|
Using the comparison operators in a formula is a very EASY way to compare two values in Excel. It's a great way to compare lists of names, text, dates, or numbers.
It's also important to note that the case doesn't matter when comparing text. The following formula will return TRUE.
=”Excel Campus” = “excel campus”
The IF Function Explained
What if you want to return a result other than TRUE or FALSE?
For this we can use Excel's IF function.
=IF(logical_test, value_if_true, [value_if_false])
The IF function is pretty simple, and contains the following three arguments.
- Logical Test
- Value If True
- Value If False (optional)
For the 1st argument, Logical Test, we can use the same formula we used above to compare two values.
If the logical test evaluates to TRUE, then the result of the IF function will return whatever you put in the 2nd argument (Value If True).
The Value If True argument can contain just about anything:
- A number
- Text wrapped in double quotes – “hello”
- A reference to another cell – A2
- A formula with another IF function.
- A formula with any other combination of functions.
The 3rd argument, Value If False, will be returned if the Logical Test (1st argument) evaluates to FALSE. It can hold any of the same items that the Value if True argument can hold.
The 3rd argument is Optional. This means that you don't have to put anything in the formula for it. You can just close the parenthesis after the 2nd argument. If the logical test is false then the formula result will display FALSE in the cell.
Nested IF Formulas – Yellow Light!!!
So we can see how to program our decision for the green light and red light, but what if the light is yellow???
This is where we can get in trouble in life (and Excel).
You have a decision to make, and you have to make it fast! Your brain does a series of logical tests within a second or two.
The first test is probably, “Do I have enough distance to stop?” Then, “Are there any cops around?”
Then it's something like, “If I stop I definitely won't risk anything. Plus, I could check Facebook, send that text, or see if there are any new blog posts on Excel Campus…”
“If I go, then I'll have a chance to finally get to work on time…”
It's never an easy decision. But more importantly, how would we write a formula for this decision?
One way is to use a bunch of IF functions in the same formula. This is called a nested IF formula. As you can see in the image below, the formula can get long and ugly.
The nested IF formula also becomes difficult to read and interpret.
Fortunately, there are lots of alternatives to nested IF formulas in Excel. We can use:
- Lookup functions like VLOOKUP, INDEX, MATCH
- Logical functions like CHOOSE, AND, OR, NOT
- Math functions like SUMIFS, COUNTIFS, AGGREGATE
Each of these alternatives require an explanation, and I will write separate articles for those. They can be much more efficient to read, write, and calculate.
Check out my free video training series on the lookup functions to learn more.
Programming Excel with the IF Function
Writing IF formulas gives us a powerful feeling with Excel. You start to see how you can use it to display results based on criteria or user inputs. This can make your financial models, charts, and dashboards interactive.
You are basically programming Excel with formulas to build spreadsheet applications. If you are interested in programming and automating Excel even further, checkout my free video training series on Macros and VBA.
Please leave a comment below with any questions. Thanks and have a great day!