How to highlight Duplicates in Excel 2013

Is there an easy way to locate and highlight duplicates in a list in Excel?

Yes, Excel has many powerful tools to locate and highlight duplicates in a list or data range. Conditional formatting is one of the tool with which we can highlight duplicates.

Let's learn the process of highlighting duplicates with the help of an example:

1. Select the cell range where you want to find and highlight duplicates

2. Use the Conditional Formatting tool located in "Home" tab of the ribbon as mentioned below:

Conditional Formatting >>> Highlight Cells Rules >>> Duplicate Values [Shortcut: Alt+HLHD]

This will activate the "Duplicate Values" dialog box.

3. Select "Duplicate" from the dropdown in the dialog box and also select relevant highlight option

 To apply any customized format you can select "Custom Format..." from the dropdown list. The preview of format will appear while you are selecting from the dropdown.

4. Click "OK"

This will apply the desired format to duplicate values in the selected range.

IMPORTANT NOTES:

• The feature is NOT case sensitive.
• Excel will recognize the cells as duplicate only if both cells contains exactly same data.
• In case any extra spacings are present in any one of the cells, Excel will not recognize them as duplicates (If both cells have same extra spacings, then it will recognize as duplicates!!)