5 Ways to use IF() Formula to Create Magic in Your Sheets

Intermediate

About this Tutorial

We're going to create some magic here. Some if magic, sprinkle a little magic on your sheets, you can use these formulas in existing sheets.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

AND() IF() IFS() ISBLANK() OR() SWITCH()

Video Transcript

<div>00:00 Hello, better sheet members. Thanks for joining me in this video. We're going to create some magic here. Some if magic, sprinkle a little magic on your sheets, you can use these formulas in existing sheets. 00:14 If you want to add a little magic to them, if you want to give users or yourself, some surprises along the way. 00:21 So we have five things we're doing today. We're going to combine it with five things.

And even if it well, four things, and then if itself, so let's get started, I'll show you, what's going on. 00:32 First show you each of them, all of them together. Then I'm going to break them down and show you each individual one and explain a couple of use cases you might have with it. 00:40 So, first one is blank. So if we have a blank cell and we want to fill it in with something, uh, we can just say hi, And they're in the G column. 01:01 We filled it in, see something happened once we filled in and the cell was not blank anymore.

Next, if you have checked boxes and you're like, Hey, we want something to happen. 01:11 Based on this, checkbox can see checked off over in the G column. Maybe you have to Jack check boxes to check boxes in which both of them need to be checked off. 01:23 Maybe you have some kind of approval system or review system. And you're like these two people need to both approve this. 01:31 And then I want to be able to know that they both approve it. So let's see, check off both of these boxes, both true and see this happen. 01:40 We can add whatever text you want here.

Now, maybe you have two people in which only one of them needs to say approve something, or look at something. 01:48 Maybe it's some kind of QA system and at least one person of a group , but it's an or concept this person or this person needs to check off or, or something needs to happen, right? 01:59 We can have this person check off at least one true. And then this person, and if both of them check it off, nothing changes. 02:10 Now, maybe you have some case where , where you have someone who is the sort of gatekeeper.

Let's say, uh, someone who is, uh, the first approval or the first step in a process. 02:24 And you're like, nothing else matters until this one thing happens. So even if other things are checked off, like the second check box here, if this is checked off, it doesn't matter. 02:36 We need this like other thing to happen necessarily then this thing. So here we have this one and you say first true only. 02:45 And if this is checked out, both true. Now, if we uncheck the first one, see it goes away.

So that nothing happens based on the second checkbox , nothing happens, but if it's checked off and this one's checked off now it's is both true. 02:57 So you have some sort of decision tree or issue three here, uh, going on. So now I'm going to go into each one and show you how it's works. 03:07 Uh, the formula used, it's all using if and something else. So, uh, we'll show.

And then I'm going to share with you some use cases for these, um, maybe, uh, adding to your sheets. 03:20 You've probably already might have some ideas just seeing how this works, but after I show you how to do it, I'll show you why you might do it. 03:29 All right. This one is the is blank. Uh, we use the, if formula and then we have is blank formula and then C two, which is actually all of this, uh, cells here merged together. 03:42 Then we have two commas and you filled it in and I'll explain those two comments in a second.

So let's start from the beginning. 03:48 Let's go equals F let me have a parentheses. Let me go is blank. And when you see two, which is the cell that is this merged cell here, uh, we encourage disease. 04:02 Now, if you're not familiar yet, uh, Google has this great little helper that tells you what it needs in order to do what it does this formula . 04:14 Now, once we hit comma, we have a value. If it's true. So if this C2 is blank, what do we want to happen? 04:21 Well, in our case, we want nothing to happen if it actually is blank.

So we just give another comma and have literally nothing here, what you might be. 04:32 Um, uh, what is logical is you might think you have to have something here. So you might put two quotes in. 04:40 You don't really have to, you can just have nothing. You can also have a different cell or something else, and I'll show you that in a second, but you don't have to do two quotes. 04:49 Um, but right now we're just gonna go back to ourself and rewrite it because we can pause it. Okay?

If his blade and then see two, two commas, because we want to get past that value, if true, and now to the false. 05:08 Now, if the cell is not blank, our logical expression here in the first part is going to be false. So is blank returns either a true or false? 05:21 Is it blank? Yes. True. Is it not blank? False. So when, when it's false, we want some kind of message. 05:27 We want a message here. And what's interesting too, is that as you're typing it it'll show you based on what the actual true false is at the moment. 05:41 Um, we can go here, delete this.

And now we have two blanks. One thing that might happen here, that is a bit, uh, something to look after is if you have a space, you felt it in, even though it visibly, visually not look like you filled it in, you filled it in. 06:03 That is if weighing is blank. Now, based on the checkbox , this one is even easier. If you got these blank, one, this one's even easier. 06:12 Boxes are visual representations of true false. So if we look up here in the formula bar , we can see already, it says false for this C3. 06:22 And if we check it, it's true.

So we have nothing to do for a checkbox other than create the checkbox and then do equals if we need C2, is the cell that attend comma. 06:35 If it's true, meaning it is checked off. That's what on C2, C3. Well, I almost messed up there, but that's good that it showed you, right? 06:46 This is showing false. And I see the checked off. So, oh my God. I know that it's a problem. All right. 06:53 C3 camo. What happens when we want is when the checkbox is checked, we want days. And then if we, if the it's false, we want, Nope. 07:12 Now we have hay.

And if we uncheck the box, Nope, there we go. It's working now if with a checkbox right now, with two check boxes, they both must be checked in order to be true. 07:27 This is going to be equals F and then in our logical expression , we need the formula .

And now, and this bunny, because it's with at least with checkboxes, we literally just have to put in C, four and C uh, <inaudible> is the other box here, separate them with a comma and the parentheses. 07:55 And now this will, the logical expression will only be true if both of those are checked off, but both are true. 08:03 We say, yes, True. And now, if they're not both checked off, if only one of them checked off, or none of them are checked off, we'll say, Nope. 08:18 And now we can go back and check them and see perfect. If both are checked out, it works perfectly.

That is using M maybe you don't need both of these checked off, or maybe you have like a lot of things out of all these things. 08:36 Only one thing you need, only one thing. And later in the video, I'll go over some use cases of this. 08:44 This is the exact same thing as we just did an except we use the, or did you see five comma five. 08:55 These are the two cells over here on the left. And what do we want if it's true? Yep. And comma, if it's false, new, and now we uncheck both of them, or we know we checked only one of them. 09:14 Yes. True. Perfect. 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. 09:32 Uh, so what we have here is AF first, and we have the, if is C six C column here, the first one, then we have comma. 09:47 If again, in the diary with true state, then we have Aesics and then we have our texts that we want. 09:58 But we also have another text. That's not seeing here after this camo, in this false one over here.

And I'll get to that in a second. 10:06 So this is the end state we need to get to, um, over here. And this is very much a determinant upon your need, like what you're trying to do here, which one is the gatekeeper? 10:19 Uh, this is I'll go into the reasons why you might need this in a second after this, but let's just get through it. 10:26 It's if logical expression are in our case, we want the C6, this first one on the left to be the one that based on true or false, what else happens? 10:38 So we just do C6 comma. If now I just want to point out that I'm in the true area.

So if his logical expression is a true or false is a true or false comma, the true truth, comma false. 10:53 So everything we do now is based on if the first one is true, then we go, if it's false, what happens? 11:00 What state do we want? So if it's true, we want if as well, and now we want Aesics over here. 11:10 And if it's, if both are Trisha, now we're in the state of both are true. I'm going to break this down in just a second. 11:17 So if both are true, we want to say both are true.

Now, if the first one is true and the second one is false, we want to go. 11:29 One is true. Now I'm going to end the parentheses and add a comma, and then I'm going to not do anything. 11:39 I'm going to add another parentheses, but I'm not going to put anything in the false, but actually that's, that's what we did in the G column. 11:44 So let's do here. Uh, let's say, uh, wouldn't we wouldn't know if both are false. We'll just say gate is closed. 11:54 There we go. So now both are true unchecked. The second one. Now this one is first. It's only true.

This one says one is true. 12:02 They're both correct. We unchecked the gatekeeper and now this says nothing but our one that we just did says gain is called. 12:09 So in this case, both are false. All right, let me share with you one thing about this that might make it easier is what I like to do at least when I've been trying to do these sort of if trees or if decision trees or issue trees. 12:24 Uh, what I do is I put, let's go put some text here.

I have an if statement and I have two possible options, true, false. 12:36 And from these I have two possible, so, uh, outcomes are true, false. So what I do is I take true, double it up here and false, uh, double it up here and now we have true false boss. 13:08 Okay. So this is based on the first one, And this is the second. And now we can say, okay, what's going to happen if both are true. 13:18 And we can say the result here, results one, we can do results. Two result, three result, four.

And now we know based on true, true, true, false, false, true, false, false. 13:36 And this is really unique because this is not unique . Sorry, useful. This is useful just to write out first, before I put in, before I create the, if formulas and the, if, if formula , uh, just so that I, I can test out, right? 13:52 Sometimes you need to know the result first, and then you can test it out.

All right, we're going to go into some, uh, use cases now. 14:01 And I'm going to come back to the F statements and let you know how I've used this in the past, and also some type of use case, but we're going to go back to the other ones first and share with some use cases. 14:13 Alright, is blank is really fun because it's literally making something from nothing.

A few things I've used this in the past for is if I need someone to fill something out and they need the feedback of knowing that they filled it out, that this is complete. 14:29 Sometimes you can do a emoji, a checkbox , instead of a message. Maybe they need to fill in their name and you have a checkbox . 14:40 You can actually add emojis to this text. Um, we can have a thumbs up. We might not have like yellow background on your sheet. 14:54 You can use a yellow, a thumbs up to have some levity, some, some nice fun things.

As someone's filling out something, it can be a really fun thing or really nice things in the show and share that, Hey, you filled it out. 15:06 We know you got it built in For the checkbox . What I like to do in this case is have a checkbox . 15:19 Like if you are grading, say papers, create a minute. Oh my God. What I meant is if you're, if you have a quiz, if you have say a multiple choice quiz you've created in Google sheets, and you can set the answers to like show up on a final page, right? 15:38 This does these formulas don't necessarily have to be on the exact same page.

So this check box could be one of four answers , like ABCD on a page. 15:49 And on the last page, it could have the grade already done, right? And say, Hey, if the person said B on page one, quiz one line three, if it's B yes, that is correct. 16:03 If it's a C or D it's wrong. And so that actually combines both the ore and the checkbox . So you want to get the one checkbox is correct. 16:12 Say, Hey, you got it correct. If, if any of the others, Nope, you got an incorrect.

So that's really fun for quizzes. 16:21 And if you're not a teacher and you're not creating quizzes, you might actually be able to add, uh, to info products. 16:27 So if you're creating a checklist for people and or even a workbook or a planner, one thing is at the bottom of every page, add a checkbox and say, Hey, I filled in this page or I've, I've looked at this page, I've read this page. 16:43 You can do that on each of these sorts of tabs, create a workbook.

And then at the end, or at the beginning, have progress bars over how many pages have they actually gotten through. 16:52 It might be nice to even have like a start here page where you show, Hey, you have five sets. You have five sections to go through. 16:59 As you go through each one at the bottom, check it off and then show their progress as they go through it. 17:05 That's really nice. Might've mentioned the end, a use case already.

One of the most easiest and best use cases is say, you have a few people that need to approve some process. 17:19 Like, do 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 and is really beautiful for this. 17:32 You can create columns of check boxes for like eat. You can have a process and have a column for each person and say, Hey, Janet, go over here and check off these boxes as you see it. 17:43 And then you can let Brian go over here and check off these boxes.

And you can say, uh, George, go over here and check off these boxes. 17:49 And it could be all labeled for each person.

They can have separate columns, but then each process that everybody has gone through, right? 17:57 If we only want to know, has everybody gone through this one process, one call a, this one line and this one row you can use and to say, which ones are actually all done. 18:10 And in the same sense, if there's say three or four people working on sort of a, a project and they need to say, pull the data in and then approve it or do something with it, maybe a lot of date data munging with people. 18:23 Like if you have a large set of data and you're like, Hey, I have, um, this is really, uh, useful in contacts and lead lists. 18:32 If you're creating lead lists, but you have like say all the websites and you need to find contacts for each of these websites and you need a person to go through each, each one and find it. 18:42 But there's like, you have three or four say, VA's you give them all a, a checkbox , give each of them a row, a column and say, Hey, check off the one you've done. 18:53 And now you have checks in sort of three or four columns, right?

But using, or you can say, did anyone see this one contact? 19:02 And then you can fill it, figure out which contacts haven't been done yet. If not, all of them are checked off, right? 19:10 None of them are checked off. And now the, if, uh, if, if the, the, if assembly line or factory, whatever you want to call it, uh, this one's really fun and useful. 19:22 Once you get crack it and you get it into your soul, uh, it can be very useful if you have a number of different results based on different, uh, evaluations. 19:31 Right? True, true, true, false, false, true, false, false.

If you have, um, these different results, right? Four results from one, two things that happen. 19:42 As I said before, if you have some kind of gatekeeper, you have like say someone who needs to do something first, say in an order, or if there's someone more important, right. 19:51 If say, um, you're doing some kind of ranking or evaluation of past processes, and you're like out of these three processes, we need to pick one. 20:02 Now everybody needs to sort of vote on which one they want.

But if you have one dominating vote where you're like, oh, this one person sort of dictates more than anyone else. 20:13 If, if might be a good, might be a good use case, but honestly it is better if it's like more first, second, third order, like in a time division, then sort of ranking, division and ranking, you might choose other things. 20:31 Um, there are a couple of, sorry, there are a couple of different formulas if you're looking at this and you're like, you know, my use case doesn't necessarily fit with this kind of thing. 20:43 Like, yes, we, we have true or false logical expressions in which we need to like do something or show something is happening. 20:53 Right.

But it's, doesn't fit. Exactly. There are two other formulas that I have other videos for, for members. If you are interested in getting better at better sheets, um, and not a member yet, then you can go and find other videos. 21:08 I have other videos. And the two other formulas in this sort of set of things is one EFS. Now ifs has one condition and a value. 21:19 And if that condition is not met or sorry, if it's false, then it goes to the next condition and the next value, uh, option. 21:28 Um, if the first one is returned true, then it's done it. It shows the value.

There's another, uh, sort of formula in the same sense. 21:40 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. 21:53 So I've used this in like I've used this in months. So if I have a dropdown selection of, okay, pick your month, then based on that month of January, February, March, something else has to happen. 22:08 So the expression is like G one and the case is January. What do I want? This is first month.

I'll say, and then if the case in case G one is February, February, I can never spell fed Ruairi. 22:28 I want it to say second month And we can go on and on. If we hit enter, what do we have here? 22:48 It's going to give us an a and why is that is because this is, has nothing in it. And both of these cases are false dates and says, the error is not, the error is not, it doesn't work. 22:57 It says that there was no match . So we need to have something here. And if we even have let's just put something, it's still going to say no match . 23:04 So it really has to be gin.

And now it's first one Ruairi second month. So this is very good. Switch is very good. 23:16 If you have like a dropdown menu and data validation based on like, you know, only these set of potential options are here. 23:24 So we're going to give some other things, but that's really good. Switch is really good. If you, if you are thinking of this, if you're like, oh, this could really be useful, but it's just not the right thing. 23:40 Then switch might be better. Uh, you can also, you don't have do checkboxes.

I did check boxes here because they're easy representations of true false, but you can have any kind of true false statement, basically. 23:53 Anything that renders a true false statement. So you can have a text equals something. We can even do that here. 23:59 We can say, um, Equals F C seven equals. Yes, sir. And if that's true, let's say hello. And if the false will say bike, so right now it says bike, but if C7 says, yes, sir, see, hello. 24:28 So you can do text as well. Thanks for watching this entire video.

I hope you got something out of it. 24:35 If you have any questions, let me know. I'm really excited to see more magic in spreadsheets all over. And, um, hopefully we can make more videos like this. 24:46 If you like it.</div>