0:00 Hi. So Andrews has a question that I think everyone would benefit from. So I'm going to answer this very generally, and I'm going to show you the answer, but I'm also going to show you how we get to that answer.
0:11 Um, so Anders asked, I have, uh, the sort of, uh, columns that I want the entire row highlighted. If column a, B and C all have something in it.
0:22 See here on row four, we have something, something, and then nothing here. But if we write something entire row becomes highlighted, um, and the idea is I want to be able to select these rows or these columns, right?
0:37 Which columns do I pick? Well, I want to know, um, say maybe C F and J you know, and maybe there's a, a wide variety of reasons.
0:48 You'd have different, um, um, columns that specifically you want to know, are these finished? Uh, or is there something in it?
0:57 So, um, I'm going to show you the answer, but I'm going to show you the answer by going through, if you want to skip to the end of the video, you can see the answer, but let's walk through it and see what we can do.
1:07 So on a blank blank sheet, we have, you know, our header here and nothing shows up, right? We have a, B, C, and then we'll do something.
1:18 Well, some something, and we'll leave this blank to know that when we're done, we know that this entire row will be highlighted and this one will not be, um, and you can also have a variety of different setups, maybe like this.
1:34 Um, and you have, you know, a variety of different setups, so, you know, okay, did I get it right? All right, let's go first to, um, the first idea might be highlight the, the, the, the columns and go to format, conditional formatting and we'll work there.
1:53 So we're applying this to aid to see, we want ABC highlighted, didn't see, right, right away. It gives us is not empty in this format cells.
2:02 And this is wrong, right? One, the entire row is not entirely highlighted, but also all of these, some things are highlighted.
2:09 We only want ABC, so we're not gonna use this. We're going to go down the next. We're going to create a custom formula.
2:17 Now our custom formula is going to be something like, um, equals is blank, a one. Okay. And what that does see it, it's sort of the complete opposite where, um, ABC this row two is not highlighted at all.
2:35 Okay. Well, we, if it's complete opposite, we can put not in front of it and say, not is blank. So what we get here now is exactly the same that we got with this is not empty.
2:49 All right. We don't want this. Right. But we're, we're sort of on the path. If we want to select the different columns, what we can do, is it around and around it, uh, around this custom form and we'll do an comma and then we'll copy this and change it to be one.
3:09 And let's see what that does. Okay. We're sort of there, we've now selected a and B. These are, um, highlighted the weird one is this right here, right?
3:21 This is sort of, I can see where we're going. Um, these two are highlighted, but, but it's weird that, um, sort of with this, I, if I thought of this, a and B are not blank, I would think these two would be highlighted, but it's this one.
3:40 Okay. Well, we don't want just to let, let's do three. So let's just add, um, uh, at the end, another, before that last, uh, parentheses comma, and then we're gonna paste that, not as blank, or we're going to do C um, cause that's what we want.
3:58 We want a, B and C to be, um, uh, ha not blank, write some answer. And what we get now is something strange stranger.
4:09 We get this a column, a highlighted, but it is correctly identifying that it's this row and this row, um, this row is not highlighted correctly.
4:23 This row is not highlighted correctly, but the highlight is wrong. So let's figure that out. So one, um, one thing in custom formula that is very useful, but you really have to know that it exists.
4:39 Is this a one is actually a relative. It's not an absolute. So we're typing this in here is blank a one.
4:48 Um, and we think, Oh, that means a one. And what's really going on is when you do this, um, apply to arrange a one to see 1000 as it goes down the rows, this a one changes.
5:02 So for roadway one, uh, it is a one, but when it, when we're looking at row a two, this two changes.
5:11 Now, if we know that we know also that by going across on columns, it's also changing relative to what the cell it's in.
5:20 So this B one is relative. The C one is relative, but we don't want it to be relative. We want it to be absolute.
5:28 We want to know that the entire column, sorry, the entire row is highlighted. If the call, uh, column a is not blank.
5:37 So what we can do here is add this absolute, this dollar sign in front of the, a we're going to do it in front of the B and in front of the C, because we absolutely want to know that that is, um, uh, that is filled in.
5:54 So now we have something very interesting. Uh, we actually have sort of the answer, right? We are. Let me just make sure to find a rank, um, correctly.
6:06 This is correct, right? We have these three things filled in and they're highlighted, right? If we delete one, it highlights it.
6:14 The issue is it's not the entire row. So if these three columns were set up, say it's D H and G only D H and G would be highlighted, not the entire column or entirely the entire row.
6:29 So how we fix that as we go to the apply to range, but we just take out the C and we go a one to 1000, and now this entire row is going to be highlighted no matter where it is.
6:41 Um, we can also do something like a two, if we want to not highlight the header row. Um, there we go.
6:49 So now, now there's another problem, right? This, this row three is, uh, highlighted and it's not supposed to be. So if we go back to our idea that there's absolute, and there's relative, these a one, the a is absolute, but the one is, is relative.
7:11 So what we need to do is just, actually, it's really simple. We just change that one to a two. Um, what it's doing is sort of just shifting everything down one row, and we just shifted to the correct row, right?
7:24 A two here, and we have the row two here to start this formula. Now this, we want the rows to be relative because we want it to change as it goes down, but we just have the starter here.
7:36 The eight to this two column needs to be two column in this custom formula. So now we have our answer.
7:43 We have custom formula is, um, I'm going to copy this whole thing, and I'm going to put it right here so you can see the whole thing.
7:52 So, and, uh, and in parentheses is not, is blank a two then comma or whatever your separator is not is blank B to C two.
8:05 And the key point is that the, a, B, C are absolute. So you put the dollar sign in front of it, um, that the row is the exact row that you use to apply to a range.
8:17 And then also the key is that your application to range, your apply to range is the entire row. Um, and I think that will get you where you're trying to go.
8:26 You're trying to highlight the entire row when some specific columns are filming and you can always change these columns. You can change it to, let's go back here, let me delete this.
8:41 And let's, we can probably add more. Right. Um, but let's just do D there. Uh, we're going to go there and now nothing is filled in, and then we empty and now that's in.
8:56 And so we can test this out, right? We can say something, something, something great, great. Right. We can go all the way down and test this.
9:07 Right. Um, so we have a, B and D are filled in. If we delete C it's still fill in. Great.
9:16 So this is our answer. Is this, this custom formula here with not as his like, and a very, very fun way to get, um, the entire row highlighted.
9:30 Thanks, bye.