A Lot of times we need to be able to see if our dataset has duplicates.

To highlight duplicate in excel is very easy!
With this neat trick you can find duplicate and can be left with unique values.

All you need to do is to select your set of data then go to
Conditional formatting ->> Highlight Cell Rules ->> Duplicate Values
I will show it to you in a quick animation.

How to find duplicate in excel leaving unique values?
You can do it with a formula too!
Here we going to write a COUNTIF formula, to count the number of occurrences in our dataset.
Then we will wrap our COUNTIF in an IF formula that will evaluate the COUNTIF.

Basically we will evaluate the result of the COUNTIF with the IF formula.
What we saying in the formula below is IF the “number of occurrences is more than 1”, then give me 1 as a result (TRUE), else give me 0 as a result (FALSE).

This way when we put this formula in to the Conditional formatting, we command the conditional formatting:
IF result is TRUE (1), then apply it, if result is FALSE (0) then don’t.

But I need to get rid of the duplicate values.

Here is how you can do this:
When you highlight your dataset, you head to the Data tab on the ribbon.

There you choose Remove Duplicates.
It will pop up a window asking you which column you want to remove duplicates from. Hit O.K. then you done.

Come and visit us.

Come and visit excelangel on Facebook to find and join the growing community of true office workers.

Oh, I almost forgot.
Drop us a like while you there…

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]