How to find duplicate data in Excel

Wondering how to find duplicate information in Excel? Quickly find and delete duplicate data in Excel with this handy function. (formula) While there are many ways to find duplicate data this is the fastest and most efficient, in my opinion. Make sure to follow the tip on your header row or you will have erroneous results.

Learn more on my blog post at http://bit.ly/1OLvypE

---

Closed Caption:

in this video I'm going to show you a
quick and easy way with a rather magical
formula in my opinion that you can find
duplicate data now this comes in really
handy
really no matter what size list you have
in Excel that you're looking to find
your duplicates but comes in especially
handy if you have hundreds or thousands
of rows worth of data now there
certainly are a number of ways in which
you can go about finding duplicate and
the reason i like the formula i'm going
to show you is because your data doesn't
need to be sorted at any particular
manner as you can tell in my list here i
have things kind of all over the place
and you can see well for example here we
have sara bareilles online 10
we also have her again on line 15 so
this particular function as i mentioned
your list here doesn't need to be sorted
in any particular manner and some of the
other options available for finding
duplicates actually require you to have
your list sorted so this particular
formula just saves you basically an
extra step
so I have my list here what I'm going to
do next is I'm going to grab the formula
you can see it right here
and what this is is an if max function
and it's also counting
so what it's going to do is basically
what it's telling you is if the maximum
count is over 1 enter in the word
duplicate if that the result does not
have more than one enter in
no duplicate now one thing one word of
advice in this particular case you can
see the range defined as a 1 through 11
I actually have 20 rows here as you can
see so i need to change this before I
copy it down to make sure i have the
proper range defined i'm going to
quickly change the 11 here - 20
you can see the blue highlight here all
along the side that just is a visual
indication to you of what range you've
just defined in the toolbar above i'm
going to go ahead and hit enter
so now i have that in i'm going to
control C shift page down
well Paige up that went really far shift
just arrow
there we go so now we can see visually
duplicate vs no duplicate
now here's the fun time saving part i'm
going to go ahead i'm going to filter
this data and any time you use a filter
you want to insert a row and you want to
make sure that you have headers
ok so what I'm going to do then is i'm
going to highlight this just because I
like to highlight things my header rows
in Excel 2007 you're going to click over
to data and you're going to click filter
now you'll see that will drop down boxes
have appeared on both columns a and
columns B i want to sort based on the
word duplicate so i'm going to come over
here to column B grab the down arrow by
clicking select all that's actually
going to unselect everything so you can
see if everything is unselected now I
want to select only duplicate go ahead
and hit ok and as you can see it's
quickly returned to me just the fields
which have the kit data so it's
certainly a lot easier than sorting the
list manually and so now what I'm going
to do is I'm going to grab that entire
range and i'm going to delete everything
on my duplicates have been deleted
I'm gonna come back to my filter go
ahead and tell it's old and the only
thing now that is left on my list
our names where there's absolutely no
other duplicates in the sheet
I'm so that's how easy it is using this
particular formula let you look at it
again and it will also be listed in my
blog post if you're not following me on
twitter i tweet daily excel tips my name
on twitter is at em
ange1 or you can visit my website at the
virtual asst . com
have a great day