Today I'm sharing you five ways to use the IF function inside your Google Sheets. We're going to sprinkle a little magic on your sheets.
Let's get started on what's going on first. I’ll show you each of them, all of them together, and then I'm going to break them down and show you each individual one. I’ll also explain a couple of use cases you might have with it.
The 5 ways to use the IF function in Google Sheets
We have a blank cell and we want to fill it in with something. For this example, let’s go with “Hi!”
This one is a blank and we’ll use the IF formula. Google Sheets has this great little helper that tells you what it needs in order to do what it does. This is what it looks like:
IF(logical_expression, value_if_true, value_if_false)
First, we have ISBLANK formula and then C2, which is all of the cells merged together.
Then next, we have a comma. We get value_if_true for this. What this means is this: If C2 is blank, what do we want to happen? In our case, we want nothing to happen if it actually is. We’ll leave it blank. Just give another comma and we have literally nothing here.
You might think that you have to have something here, such as two quotes in. You don't really have to. You can just have nothing. We want to get past that value_if_true.
If the cell is not blank, our logical expression here in the first part is false.
ISBLANK returns either a true or false. Is it blank? Yes, true. Is it not blank? False.
When it’s false, we want to display a message. Maybe something like, “Hey Message Here!”
This one's even easier. Checkboxes are visual representations of true false.
If we look up here in the formula bar, we can see already it says “FALSE” for C3 and if we check it, it's “TRUE.” We have nothing to do.
Now let’s create a checkbox in Google Sheets. Enter this formula:
=IF(C3, “Hey”, “Nope”)
Now we have “Hey” when we check it. And if we uncheck the box, we get “Nope.” There you go! It's working right now with two checkboxes.
With two checkboxes, both must be checked in order to be true. This is going to be the formula:
The logical expression will only be true if both of those are checked off. If only one of them is checked off or none of them is checked off, we'll say “Nope.”
And that is using the AND() function.
Now maybe you don't need both of these checked off.
This is the exact same thing as we just did with AND(), except that this time we use the OR().
Let’s use this formula for this example:
OR(C5,E5) = logical expression
“yep” = the value if it’s true (checked)
“noooo” = the value if not true (unchecked)
All right, get your coffee for this one. This one's a little more difficult.
Let me share with you the final thing, the final state of this, and then I will try to break it down.
What we have here is this formula:
=if(C6,if(E6,”Both True”,“First True Only”),)
In this IF(logical_expression), we want C6. This first one, the first if statement, is the one that’s based on true or false.
What else? We just do C6. If everything we do now is based on if the first one is true, then we go, “If it's false, what happens?”
Now we want E6 next (on the second if statement). If both are true, we want to say “both are true.”
What if the first one is true and the second one is false? We want to say “one is true.”
Don’t forget to add a comma before closing the whole formula with a parenthesis.
I’m not going to do anything with the false statement. We’ll just say “gate is closed.”
There we go! Now both are true. Let's keep going...
Uncheck the second checkbox. Now this one says, “First True Only” and the other one says “one is true.” They're both correct.
Let’s uncheck the gatekeeper, where both checkboxes are not checked. Now this cell (G6) says nothing, but the one we just did says “gate is closed.” In this case, both are false. (Both checkboxes are unchecked.)
Let me make things easier for you
This is what I like to do, at least when I'm trying to do these sort of if trees or if decision trees or issue trees.
Let's go put some text: true and false. From these, I have two possible outcomes or “true” and “false.”
I’ll duplicate these next and then add “first” and “second” on top of each column to clearly mark what’s what.
What this does is to make it more understandable. With this technique, we can see what the results are for each given statement. Now we can say, “Okay. What going to happen if both are true?” Then we can type in the result next to it.
This is useful just to write out first before I create the if formula, so that I can test out. Sometimes you need to know the result first and then you can test it out.
How to Implement This Magic
Your new found formula combinations will be handy.
I'm going to come back to the if statements and let you know how I've used this in the past. ISBLANK() is really fun because it's literally making something from nothing.
Let's say I need someone to fill something out and they need the feedback of knowing that they filled it out and it is complete. Sometimes you can do an emoji or a checkbox instead of a message. Maybe they need to fill in their name and so you have a checkbox.
For the checkbox
If you have a quiz with multiple choices that you've created in Google Sheet, you can set the answers to show up on a final page. These formulas don't necessarily have to be on the exact same page. This check could be one of four answers, like A, B, C, D on a page. And on the last page, it could have the grade already done. That’s really fun for quizzes.
If you're not a teacher and you're not creating quizzes, you might actually be able to use it with info products. If you're creating a checklist for people, your workbook, or a planner, you can add a checkbox at the bottom of every page and say, “Hey, I read this page.”
You can do that on each of these tabs. Create a little workbook and then at the end or at the beginning, have progress bars over how many pages have they actually gotten through. It might be nice to even have a "Start here" page where you show five sessions to go through. And as you go through each one at the bottom, they can check it. Then their progress is shown as they go through it. That's really nice!
Using the AND() on processes
The easiest and best use case is when you have a few people that need to approve some process. You have a business process or an operation in your business in which multiple people need to see the end result or approve it, or at least check off that they've seen it.
You can create columns of check boxes. If there are three or four people working on a project and they need to approve it, you can use this for that scenario, too. Let’s say you have a list of websites and you need to find contacts for each of those. You need a person to go through each company and find those details.
Give each of them a a column and say, “Hey, check off the one you've done.” And now you have checks in those columns.
Using OR() with the same scenario
By using OR() with keeping track of the list of contacts for each website, you can say, “Did anyone see this contact?” And then you can figure out which contacts haven't been done, if not all of them are checked off, or if none of them are checked off.
The fun and useful IF(IF())
This one is really fun and useful. It can be very useful if you have a number of different results based on different evaluations:
You have these different results from one or two things that happened. This also works if you have some kind of gatekeeper, someone who needs to do something in an order.
If you're doing some kind of ranking or evaluation of past processes, you and your team need to pick just one. Everybody needs to vote on which one they want, but if you have one dominating vote where this one person dictates more than anyone else, then IF(IF()) might be a good use case. But honestly, it is better if it's more about the first, second, third order; a time division rather than some sort of ranking division.
Another formula called SWITCH
There's another formula in the same sense and it's called Switch, where you have one expression and this is not necessarily good for true/false. It’s if you have some answer and you know based on the answer something else has to happen.
I've used this in months. If I have a dropdown selection containing the different months, then based on that month (i.e. January, February, March, etc.) something else has to happen. Here is an example of the formula:
=SWITCH(G1,”January”,”1st month”,”February”,”2nd month”)
Now you’ll notice that when you hit enter after typing out the formula, you get an error. Why is that? This is because G1 is empty. It has nothing in it. Both of the cases in the formula are false.
We need to have something in G1. You can try typing in “Something” in that cell and you will still get an error. It really has to be a month name, like January or February.
SWITCH is very good if you have a dropdown menu and data validation based on only these set of potential options.
I did checkboxes here because they're easy representations of true/false, but you can have any kind of true/false statement. Basically anything that renders a true/false statement. You can even have a text=something. You can do text as well. Like this one:
Thank you for reading this tutorial! If you have any questions, let me know. I'm really excited to see more magic in spreadsheets.
Watch the video for this tutorial:
Learn more magical things about Google Sheets:
Get more from Better Sheets
Join other members. Pay once and own it forever. You get instant access to everything: All the tutorials and templates. All the tools you’ll need. When you’re a member, you get lifetime access to 200+ videos, mini—courses, and Twitter templates. For starters. Find out more here.
Don’t make any sheets. Make Better Sheets.