IF statements may seem scary – after all, they involve logic, they’re an “advanced” Excel topic, and what the heck is a Boolean! Take a deep breath. This article will walk you through the basics of writing IF Statements in Excel and teach you how they can make your life easier and more productive. IF Statements are a super powerful tool, and with a little practice can be mastered by anyone.
What IF statements do is tell Excel “If this one thing is true, do this, if not do something else.” You’ve likely used IF statements in your everyday life – when your coworker offers to buy you a soda when she’s at the store and you tell her: “If they have a can of Coke, get me that, but if not a bottle is fine.” Or when you make plans to go to the beach but the weather doesn’t look great: “If it doesn’t rain let’s go to the beach, if not we can lie around and watch Netflix all day.”
Those sentences and Excel IF statements follow the same syntax:
If (statement with true or false outcome), then (option if true), or else (option if false)
Written in fancy excel syntax:
=IF([logical expression], [value if true], [value if false])
If we were able to write the real life examples above in Excel they would look something like:
=IF(store has canned soda, get me a can, get me a bottle)
=IF(no rain, go to the beach, be lazy)
Let’s move on to some more Excel specific examples. Say we have a spreadsheet that tallies up all your coworkers favorite neighborhood lunch spots and we want to add a column to determine the relative popularity of each. Let’s say we decide that if a spot has more than 10 votes it’s considered popular, and anything less will be called not popular. So in column C we want our IF statement to read “If tally in column B is greater than or equal to 10, it’s popular, if not it’s not popular.” We can write that as follows in Cell C2, and copy our formula down to fill in our column:
=IF(B2>=10, “popular”, “unpopular”)
Maybe we also want to keep track of which lunch spots are really unpopular and got no votes at all. In column D we could add a new IF statement that says “If votes equal zero, write “no votes”, if not, keep the cell blank (writing open and close quotations with nothing inside will fill in the cell with nothing, i.e. leaving it blank)
=IF(B2=0, “no votes”, “”)
IF([logical expression], [value if true], [value if false])
The “logical expression” portion of an IF function has to result in a Boolean value – in plain English, it has to equal True or False.
- A2<>B2 (<> is not equals)
What you enter into the outcome fields ([value if true] and [value if false]) can be whatever your heart desires:
- Numbers: (ex: 3.14)
- Strings: (ex: “banana”)
- More formulas (ex: A2+B2)
- More IF statements (ex: IF(A2=”banana”, “stop writing banana”, “where’s the banana”))
In our lunch example from before, you may have thought to yourself that the design of our additional columns was kind of awful. Why would we add two columns to account for one metric? Wouldn’t it be better to have one column with “popular”, “not popular” and “zero votes,” or however we decide to group them?
And the answer is yes! That sounds like a much better idea. We can do this with some help from nested IF statements.
Let’s split up our popularity qualification like so:
- popular: >=10 votes
- neutral: 2-10 votes
- very unpopular: 0-1 votes
IF statements check one condition and output one of 2 results. But we can make Excel consider more conditions and output more possible results by nesting IF statements within each other – if condition 1, then do this, if not check condition 2 and do this, if neither, do this.
=IF([condition 1], [value if condition 1 true], IF([condition 2], [value if condition 2 true], [value if condition 2 false]))
So to deal with our new popular ratings, we can write:
IF(B2>=10, “popular”, IF(b2>=2, “neutral”, “very unpopular”))
Notice as the if statement goes on we don’t have to specify as much information, because we’ve already ruled out certain possibilities. Neutral restaurants have 3-10 votes, but because we start with the popular >=10 condition, when we get to the second condition we can just check if the tally is greater than 2 because we already know if we’ve gotten to that point that the restaurant is NOT greater than 10. If the restaurant was >10 it would have already been deemed “popular”, so by next checking that it’s greater than 2, it’s really just checking for 2-10. Then we can write the “very unpopular” qualification as the [value if false] portion of our second if statement, because if the restaurant is not greater than 10 and not greater than 2 then it has to be either 0 or 1 (as long as our data is all positive, which it should be!)
We can get pretty crazy with nesting – recent versions of Excel will let you nest up to 64 levels of IF statements. If you’re writing something crazy, I find it helpful sometimes to 1) either say what you’re trying to do out loud in plain English, translating it to Excel as you go or 2) draw it out:
Note that if you work in an office with other people and go with Option 1, your coworkers may think you’re crazy.
IF Statements and YOU
My personal favorite thing about Excel is its huge capacity for general productivity uses. Sure, Excel can be used for intense data analysis and number crunching, but it’s ultimately a spreadsheet utility application that can help you do things as simple as check your work for mistakes.
When I first started working, I quickly found out that I did not have very strong attention to detail skills, which was an issue considering I was working in ad operations and that was basically the only skill you needed to have. After making a few detail-oriented mistakes - and having a lot of panic attacks about getting fired and having to move back in with my parents - I realized that if my brain wasn’t good enough at catching small differences in long lists of things, I’d use Excel to do it for me, because people make mistakes and computers (generally) don’t. I started using IF statements to do things like make sure all of the numbers in an updated report were greater than they’d been last week, and call out if anything hadn’t increased like it should have:
=IF(‘Week 4’!A2>’Week 3’!A2, “”, “ERROR!”)
To this day, I still use IF statements like this – to check to make sure data has increased or decreased when it’s supposed to, to make sure two columns are equal and call out when they’re not. Let IF statements do all the work of checking things over, so you don’t have to!