Enhance your Excel proficiency by mastering if statements, a powerful tool to automate decisions based on logical tests. Learn practical applications, comparisons, and the use of if error functions for clean, error-free spreadsheets.
Key Insights
- If statements in Excel begin with a logical test, enabling actions based on whether a condition is true or false; for example, determining if an employee's hours exceed 40 to automatically indicate overtime status.
- You can use various logical comparisons within if statements, including equal to, greater than, less than, greater than or equal to, less than or equal to, and not equal to, allowing for diverse data analysis scenarios, such as checking attendance by verifying whether a cell contains a "Y" or "N".
- The if error function helps maintain clean spreadsheets by providing alternative values or descriptive placeholders when formulas return errors, effectively communicating issues like missing data instead of displaying standard error messages.
This lesson is a preview from our Excel Bootcamp Online (includes software) and Excel Expert Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.
In this section, we'll cover IF statements. IF statements allow you to check to see if something is true or false and then take different actions depending on which result you get. Now I'm going to start off by doing a quick review of the true/false function.
This forms the basis of the IF statement because the IF statement starts with a logical test and the true/false statement is that test. For instance, someone might say that cell C5 is equal to 27. So I want to test that out with a true/false statement.
I will type in equal. I will select the cell C5, and I will check to see if it's equal to 27. Now when I press ENTER, Excel will give me a result of either true or false.
That is true. Someone disagrees and thinks cell C5 is equal to 29. So I'm not just going to take their word for it.
I'm actually going to test this out. So I'll type equal. I'll select C5, and I'll check to see if it's equal to 29.
Excel will render a verdict and tell me whether or not that's true or false, and Excel says that is false. Now this is a very simple function. Someone could arguably say, "Well, I don't see why I would need to use that because I can simply look at the sheet and tell that cell C5 is equal to 27."
That is true. But let's say you wanted to compare several values. If I go over to columns M, N, and O, I might want to check to see if one value is equal if I'm comparing two lists.
Now for the first value, it's not a mystery. They are exactly the same. But if I don't want to compare each and every value down several rows, I can simply use the AutoFill handle here and quickly see which values are true and which ones are false.
Then I may be able to filter them and get a subset of that information. Not only can you check to see if cell references are equal, you can also check to see if one value is greater than another value. So I'll delete this, and I want to check to see if M3 is greater than N3.
I'll press ENTER. Again, the obvious answer is false, but take a look at what happens when I AutoFill this down. Where the values in list 1 are greater than the values in list 2, that clearly stands out because I see true.
What other kinds of comparisons can you make besides equal to and greater than? You can also check to see if values are less than, greater than or equal to, less than or equal to, or not equal to. So how does this work with the IF statement? Well, I want to check to see if something is true or false, and based on that result, I can enter a value if true or a value if false. So I can just enter a value.
I can also enter a calculation if I wanted to. That's taking it a step further. So let's take a look at our first exercise.
Exercise 1. Someone wants to find out if these employees are working overtime. So there are three employees, but maybe we have 3,000 employees. We can create a simple IF function to check the hours and see if those hours are overtime or not.
We'll start out with equal IF. Now what I'm comparing are the hours. So I'll start by selecting Joe's hours.
I want to see if those hours are greater than 40. If they are greater than 40, then the answer to the question overtime is "yes." Again, if you're using text, you'll always use double quotes inside of a function when you're working with Excel.
Now if that value is not greater than 40, then the answer to the question overtime is "no." I'll press ENTER, and now I can double-click on the AutoFill handle and check everybody's status. I can see Ruth is the only employee in this table who is working overtime. An example that we would have our students take on is one where we might want to check the meeting attendance of 10 people at a staff meeting.
The way I can tell someone was present is if I look at their present column. If they have a Y in their status, then that means they were present. If there is not a Y there, that means they're absent because the only other possible option is N. So I'll type in equal IF this value to the left is equal to Y, then I'm going to say that person was "present." Now if that value is not equal to Y, then I'm going to say that person was "absent."
I'll press ENTER. I get my result. I'll just double-click, and there we go.
The nice thing about this is if the values ever change for the value that's being compared, the IF statement automatically updates. If Sue actually didn't attend the staff meeting, if I just change her present status from Y to N, the IF statement automatically updates. So you write the formula once, and then it continues to work for you after the fact.
We'll take a look at one more type of IF statement. This is called IFERROR. IFERROR is perfect for a situation where you have a formula that sometimes returns the correct value and sometimes returns an error message.
Now the problem is not the formula, because on some occasions it does return a value. It's just that in certain situations, maybe there are no values to calculate. That's not necessarily a fault of the formula, but there is no data to calculate; therefore, the formula will return an error message.
So instead of seeing an error message, what I might like to do is create an IFERROR function that checks to see if there's a value or if there's an error. Now if there's an error, I can have a substitute value or a placeholder in place of the error message. So the first thing we need to do is be very clear about what the formula is.
Here it's D53 divided by E53. So I'm going to go into the cell over to the right. I'll type equal and I'll say IFERROR.
Now this is why I need to be clear about the formula. I'm going to select cell D53, and I'm going to divide it by E53. If that gives me a value, fine.
Now this is where I interrupt and say if I'm about to get an error message, show me something else. The something else I'd like to see is nothing. So I'll enter two double quotes, close parentheses, and press ENTER.
I see nothing now. I'll AutoFill down, but I do see something when the formula is about to give me a value. So that's how you can use IFERROR to make your work look neater.
If I wanted to improve upon this, here's another option. Maybe I want to be a little bit more descriptive about why we're not seeing anything in that cell. I can simply type "no values," because that's actually the situation.
There are no values for me to divide. If I press ENTER, this is a little bit more descriptive. Also, it accounts for the error and is a lot easier for someone to look at.
So those are IF statements and also the IFERROR statement in situations where you might get an error.
