1. Home
  2. MS Office

Excel 2010 Duplicate & Unique Values

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.

Leave a comment