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.
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.
Next, select the color in which you want the duplicate values to be highlighted in and hit OK.
Now all duplicate values will be highlighted and it will become far easier to read the spreadsheet and find some mistakes(if any).
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.
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.