Find Duplicates in a List with Formula
Finding duplicates in a range can be done with Excel simply with the use of conditional formatting. The following method shows how Excel’s conditional formula can be used to identify any values which are duplicated in a range of cells. In order to find the duplicated items in a list, say in column B, use this Excel conditional formatting rule can be applied.
All of the cells which are duplicates are in pink, conditional formatting was used with the above formula to produce this result.
To perform this action in Excel, highlight the column you wish to have the formatting applied to (column B in the above example). On the HOME menu choose conditional formatting.
Excel will create a dialog which asks if you want a certain format and checks duplicates are what you wish to show.
Simply click OK and the duplicate values will be highlighted in the colour from the drop down.
There are other ways to do this if the process needs to be part of an automated procedure then the following might be more helpful Find Duplicates with VBA It outlines the same procedure but with the assistance of vba.
The following Excel file is a working example of the above technique.
