🔥Highlight Rows in a Table from a Selection in a Data Validation List 👍🏻

In this tutorial, I'm going to show you a really nice little trick for highlighting entries in a table based off of a selection in a data validation dropdown list. So if we take a look at the data that we're going to be using, I have a basic table of student names, the subject those students took and their pass mark that they achieved. Now, what I want to be able to do is I want to be able to select a student in cell C4 from a data validation dropdown list and have it highlight every time it finds that student in the table below. So here we're going to use a combination of data validation, a formula and conditional formatting. So let's get going. Now, the first thing we need here is we need to build a data validation dropdown list of all of the students. Now, some of these student names are repeated. So the first thing I need to do is grab myself a unique list of all of the students. Now, I've shown you how to do this in another video using the unique function, but the unique function is reasonably new and a lot of people still don't have unique. So I'm going to show you a different way that you can just grab a unique list from the selected range. So what we're going to do is select all of the students, control C, and then over in column F, I'm just going to control V to paste those in. And now what I'm going to do to get a unique list is I'm simply going to remove the duplicates. So we need to select the range up to data. And then in the data tools group, we have a remove duplicates button. I want to remove the duplicates from column F. That is correct. Let's click on OK. And you can see eight duplicates were found, 15 unique values remain. When I click on OK, I now have a unique list. So that's another way of doing that as opposed to using the unique function. So now I have this unique list. I can build my data validation dropdown. That's the second step. So in cell C4, we're going to jump back up to data, data validation. We're going to create ourselves a list and our source is what we have in column F. Click on OK. So now I should find that I can switch between the different student names from this dropdown at the top. Nice and straightforward. Remember, we can always right click and simply hide our little helper column just there. So now we have the first part set up. We have a dropdown list where we can select our student names. The second part is we want to be able to highlight those names in the table below. So this is going to involve conditional formatting and a little formula. So the first thing we need to do is select all of the rows within this table because we want our formatting to highlight the entire row and not just the cell where it finds the name. So we're going to go up to home into conditional formatting and we're going to create ourselves a brand new rule. And we're going to use a formula to determine which cells to format. So basically what we want to say here is if we type in equals, we want to select the first name in the list, which is in cell B8. Now notice that by default, this cell reference is absolute. We have those dollar symbols in front of the row and the column. So it's locked to that cell. And what I need is I need this conditional formatting to be able to travel across the row because I want to highlight in a color the entire row and not just that cell. So this can't be absolute. Otherwise it's just going to color in the cell and not the row. So I'm going to press F4 twice to turn that into mixed referencing because now we're simply locking the column and not the row. And that's exactly what we want. So what we need to say here is if cell B8 is equal to what's been selected in the data validation dropdown. And this one is fine to leave as absolute referencing because we always want to reference exactly this cell. So if that's true, we want to apply some formatting. So I'm going to say fill with a light green background fill. Let's give it a dark green border around the outside and let's make the font. Let's make it bold. And I'm going to make that dark green as well. I think that's enough. Let's click on OK and OK again and check it out because we have Jake selected in the dropdown. It's highlighted that entire row. If I was to select Annie, it's picking up wherever it mentions Annie. So this is a really cool little way of being able to highlight specific students in a list. And of course, this can be applied in many different scenarios, but those are the basic principles of how you can use conditional formatting along with data validation to highlight rows in a table. If you enjoyed this video, then smash that old like button. Give me a follow. I am feeling quite ill today. So, you know, a follow would make me feel a lot better. And I will see you guys in the next video.