Excel 2010 Duplicate & Unique Values

by on
15

Want to find duplicate or unique entries in Excel 2010? The option is there but the location has been changed. Please note that this post is not about removing duplicate rows, if this is what you are looking for then go here.

Those who work with Excel know how useful it is to find duplicate/unique values in the spreadsheet. It can help us better understand data, also it can be a great way to cross-check and verify the values.

Duplicate Values

After you have entered all values, select the columns and rows in which you want to search for duplicate values. You can select multiple columns and rows by holding down Ctrl key and then clicking them. When done, click Conditional Formatting under Home tab, select Highlight Cells Rules and finally select Duplicate Values.

Excel 2010 Duplicate Values

Next, select the color in which you want the duplicate values to be highlighted in and hit OK.

Duplicate Values Excel

Now all duplicate values will be highlighted and it will become far easier to read the spreadsheet and find some mistakes(if any).


Unique Values

As a bonus tip, you can also find Unique values and highlight them. Simply select Unique from the drop down menu, select the color of highlighting and hit OK.

Excel 2010 Unique Values

ASAP Utilities (3rd Party Add-in)

The only problem is that the duplicate values are highlighted in same color, thus confusing the users. If this is your problem, try out the free Add-in for Excel called ASAP Utilities.

This add-in adds some great functions to Excel, including option to find and remove duplicate cells. With this add-in you can find duplicate entries and mark them in different colors.

At the moment of writing, this add-in works for Excel 2003, Excel 2007, and Excel 2010 (32-bit) only.

Download ASAP Utilities

This add-in is free for personal use. If you are looking to use it commercially then you will have to buy it.

Advertisement

  • Pingback: I WOULD LIKE SOME BEST PRACTICES IN REGARDS XX XXXXXXXX DUPLICATES… — Online Computer Support & Repair

  • Guest

    Thanks, it worked very well for me. It helped a lot

  • much appreciative

    Thank you for explaining how to identify duplicates.  I tend to write a VBA routine for every little thing I need done simply because I don’t have much “user” experience.

  • Johnny Rock

    Change the color for the “Unique” formatting and you won’t need the 3rd party add-in!

    • J So

      Liked the tip! but need to find monthly “rent” expense then and in “phone” and other expenses that were “forgoten” at the time the workbook was built.  My friend had a small business and i thought that i had taught him to ” CLAIM ” all the monthly expenses at one time, his reasoning,
       ” I did not have the money to pay that then and payed it later. “  my thinking is and was and is that it’s ” monthly ” it should be ” claimed ” monthly.  So now i am trying to pull all this together and make sure that “ all got paid and CLAIMED ” for his ‘ year end ‘ reports.  Got some thing that does that, with out having to set up an Acces Database his business is just to small, and he has closed it, but i sill need to get the numbers together for him.  Friends!!! t.. he.

  • Pingback: Reconciling SPNs | Sev17

  • Debbie Loffell

    This has got to be one of the most useful things I could have read about! thank you – hours of future time already saved :-)

  • S. Dwight

    Super useful! I enter a lot of data daily and get frustrated when I find I have entered duplicates. I applied this conditional formatting to a whole column and any new data I enter will be automatically highlighted if it is a duplicate. This tip has saved me so much time!!

  • Guest

    Great tip, worked like a charm.

  • Guest

    Great tip, worked like a charm.

  • Lsd Onlinesubscriptions

    Thanks this is great.  What if you are searching for duplicate name entries (e.g. a name such as Paul Brown entered as Brown Paul) in a row?  This option doesn’t seem to show these kinds of duplicates.

  • Swarup Das

    Thanks dear very useful…………

  • fillyfanatic

    Very helpful. tx

  • Ruth Kehoe

    New discovery for me – glad I don’t have to use Access to find similar values. Love it.

  • SS

    Very Useful. Thanks.

  • lc

    Hey now that I have highlighted the duplicate cells how do I delete them in an efficient manner. I dont care to highlight them!!!!

  • Mahmoud

    This is exactly what i needed … Thank you!