0:00 In this video I'm going to show you how to imbue your spreadsheet with a little bit of magic so that your buyers or people that are getting your sheet are going to feel like it is absolutely magical and it is really useful and really special.
0:13 Uh so something you might do especially on a deal site uh a deals list you might have a done or a skip.
0:19 Um so you might want to insert uh check boxes here and then I'm going to actually add one more column.
0:26 I'm going to name the first column applied and the next one skip actually. And so we have these two here.
0:40 Oh my god. So we have these two here check boxes and we can say either applied or skip um if we're like hey I don't want to.
0:48 Apply that one or I want to apply this one or I did apply. I use this and you get a list of you know how many are done.
0:54 So what happens if you check this off? Well what you might know about already is um custom not custom for conditional formatting format condition.
1:04 Formatting and you can add some conditional formatting based on say this text here. Custom formula is equal to a two will do there and anytime it is applied.
1:18 We can actually do. Put a dollar sign in a and now we can highlight them once they're applied to great uhm and every time it's applied.
1:29 If we actually do the application. Let's do it down to 100. Now every time we click applied it'll highlight it sure you may know that that's conditional formatting but I think there's something really more special we can do with the if function and if is blank as well because you can do a lot more cool
1:48 stuff here and can show maybe some information. So in this case if I would click. Applied I want just like a little bit of magic.
1:55 I want a thumbs up here. So I'm going to do thumbs up in this column and I'm going to equals if and if in this case with the checkbox we just need the A2 the checkbox range.
2:09 We need nothing else and then we need to know if that's true, if it is checked what do we want?
2:14 We want a thumbs up and then if it's false we want nothing. And so now if we change this to array formula and a2 colon a, now we see every time we click the applied we have a thumbs up.
2:37 Pretty cool. So we can add some extra context information. If we're interested if we skip it maybe we want to add some, hey if you're skipping this why add some more information, you might want to do something else.
2:48 And if you're doing calculations and you only want those calculations to show up if you have a rate. So let's say we're taking uhm rates like percentage rate.
2:56 Rate of, let's say there's a loan rate and we want to know loan rate per year or per month, sorry per month.
3:04 And so we're going to put loan rate per year is 29% and we only want this rate per month equals 29 divided by 12.
3:12 To show up if there is some information there, right? We don't want 0%. Well, we're going to wrap this function with if and is blank.
3:24 We're going to do G2 and only put two. commas there. We want if it's true, meaning it is blank, we want to do nothing.
3:33 But if it is not blank, meaning it is false, is blank is false, we want to do something to our value if false is going to be our rate here.
3:41 And now we can copy paste this all the way down. And we can see that any time we add, you know, 12%, 13%, we can have some number, oh, that's going to be, need to be a percentage, there.
4:00 13%. There we go. And so, this if is blank adds a lot of little magic to your sheets, uhm, really cool if you want to ha, sell something where a user puts in some information, shows more stuff when they add in, when they add ah, some information.
4:16 information there. Hopefully, this is helpful to you. And in the next, uh, video, we're going to go a little bit deeper into creating automatic calculations here in case you want to sort of just go deeper.
4:30 Add some automa, actually. School automations. I'm going to show you how to do it super easily, uh, here step-by-step in the next video.