- [Instructor] Okay so here's our table of data
that we're going to try to look for duplicates inside of.
I didn't make it very long,
just so it was easier to show you.
Imagine, most of the times when you're doing this
you might have a couple hundred or a couple thousand rows,
it's going to work the exact same way.
And I'll show you ahead of time the duplicates
that we expect to find, is that we put this one in here,
Laurie Boynton's in here twice,
and Ynes Parr's in here twice.
So let's start out with the conditional formatting way
to find these duplicates.
So we're going to highlight the entire range
that we're going to look at.
And we're going to go to format.
We've already done it over here,
that's why you see this menu.
But to get that menu to come up, you will go to format
and conditional formatting.
Did that, so we'll come over here
and we're going to add another rule.
The apply to range is already filled out
in this case, it's going to be A2 to C23.
It's just easier to select that with your mouse
before you start.
And under format roles, let's do the drop down
and format cells if, and we'll go down to
custom formula is.
And then I already have this formula on my clipboard.
I'm going to put it in the description of the video.
And you can also copy this entire worksheet
if you want to visit the website that I linked up there.
But you can just use these formulas.
So let's paste this, and then we'll talk
about what's happening here.
So this formula is going to vary based on your data.
The inside of the formula is repeated for each column.
So that function is going to get shorter
or longer depending on how many columns your table has,
but it's going to be the same basic thing
and what it's doing, let's come over to the left.
So conditional formatting works from the top down
and from the left to the right.
So that formula that we put in the custom formula is box,
evaluates A2 first.
So keep in mind it's written as if it's only looking
in A2 and then when it moves down into the right,
it's going to increment the same way it would
if you auto filled it inside the spreadsheet.
So in other words, everywhere that A2 is in that formula
and it's not fixed with a dollar sign,
it's going to become B2 here.
And it's going to become A3 here.
It's very important where those dollar signs are
because when you put a dollar sign in a formula,
it fixes the formula so when you move it around,
the part that has a dollar sign doesn't move.
If you want to see more information on how to fix formulas,
I would check out the video that I've linked
in the upper right hand corner before you go any further.
Check that out and come back if you want
to have full understanding.
Or just copy and paste the formula, that's fine too.
You don't have to understand how your car works
to drive it right?
So if we look at the table that we highlighted,
the formula is working by highlighting any instance
that is repeated below it.
So it's highlighting the first occurrence of Laurie
and by the time it gets his second one,
it doesn't see Laurie again below it,
so it doesn't highlight that one.
And if that's all you need to do,
then you're done, right?
But there's always different situations
and you may need a situation where you want
to use a function to do the highlighting.
You don't want to use conditional formatting
for whatever reason.
So let's go over to the right.
So let's say we're done on the conditional formatting.
Let's put in another column and we're going
to call it a duplicate question mark.
So is this duplicated?
Scrap this formatting so it looks like a header
And we're going to do another formula here,
let me grab it on my little cheat sheet.
So the formula that we're going to use here
is we're going to say, sayin' hey if when you count this,
if you see this same value more than once,
write duplicate or leave it blank.
Type enter.
And Liddon Skipper isn't duplicated.
We'll drag this formula down.
So I just went into that blue rectangle
and I hovered over the lower right hand portion
of it where it's a square.
I'm holding down the left mouse key now
and I'm dragging down.
Then I'll let go of the left left mouse key.
It's going to auto fill that formula all the way down.
An this identified the duplicates.
So this behaved a little bit differently
than the conditional formatting.
It identified the value, after it's duplicated
instead of before.
I don't know which way is better, right?
Depends on what you're doing but these are doing
the same thing but they're doing a little bit differently.
So if you want to see how to remove these duplicates,
you don't even have to check for them first.
You don't have to identify them,
you can use the function just to remove them.
It's called unique, and that video's coming up next.
Thanks.