If you're using Google Sheets and you have a column of data here and
you're wondering if these colors also exist in this list, there's a pretty easy
way to do this where you can highlight the items here that match items over
here. Or you can do it the other way around and highlight items here that
don't match over here. Now, usually if you're doing this it's because your
lists are really big and you can't do it visually. But, I purposely made small
lists so it's easy to watch. We're gonna do it through Conditional Formatting. We
go up to the menu, go to Format, and select Conditional formatting. The
Conditional formatting menu options will pop up on the right-hand side and
there's everything you need right here. We'll just work from the top down.
The first thing is, we want to apply it to the range right here D2 to D11.
You can select that with your mouse but I'm just gonna type it in D2 with a
colon D11. Maybe move your mouse out of there, it'll show that that's all
highlighted now because it's only highlighting if it's not empty. So you're
gonna change that. You're gonna format the cells if, and
we're gonna make a custom formula, which is at the bottom. I had to use a scroll
bar to get down there. Just click on Custom formula is and then it gives you
a box that's waiting for the formula. You're gonna want to type this in, just
like you're typing into a spreadsheet cell. One of the differences
though is now it's not going to help you. Once you start typing the function,
it's not going to pop up with the help. So if you want to practice, you can
actually do the formula in a cell over here and it'll help you build it. But, I
know what I want to do so let's just do this.
You want to use a function called MATCH. MATCH is going to look at each cell
in the range and see it exists in the other range on the left. The first
parameter that you want - it's going to do this one cell at a time so start with D2.
Give it a comma. Now it's looking for the range that you want to try to
look up D2 in to see if it's a duplicate.
It's gonna be B2 but I'm gonna put a $ before the 2 because I
don't want this range to shift down every time it evaluates a cell on the
right. I want the range on the left stay the
same, so I'm fixing the cell reference. So B$2 to
B$8. Then it wants to know if this is going to be an exact
match. Let's just say zero for yes. So if it finds exactly Yellow. It's going to
highlight it and then close it off with the parentheses. You'll see that the box
that contains this function changed from red to blue meaning that the function
is now a valid function. It's already working. You can see it highlighted red
in green because red below is over on the list on the left but it didn't
highlight orange because orange isn't over here. If you click done, you've
highlighted everything that exists in the list over there. If you want to do
this the opposite way, and highlight the items in this list that aren't in the
list on the left, let's just change this formula a little a little bit. You
don't have to redo the formula. Just go back in it and click on it. But
you're gonna wrap it in a function called ISNA. This function is just
basically saying - look, if this function doesn't work, highlight it. Not if it does
work. After the ISNA, put a parenthesis and then go to the very end
and close it with the parenthesis. But, right here I am going to stop. You know it's not
working yet because it's surrounded in red. Put this parenthesis on and it'll be
surrounded in blue. It's inverted the function so Yellow is unique. It's not
unique in this list, but it doesn't exist over here and that's what we we're trying
to do. The same with orange and gray. Just clicked Done and that's what we
wanted to do. I hope that was helpful. A nice, little short tutorial for you. If
you like that and want to see other tutorials like it, if you could subscribe
in the lower right-hand corner you'll see them. Thanks!