Highlight Duplicates

Looking for a quicker way of highlighting duplicates in a data range? Then look no further than the Highlight Duplicates conditional formatting option. Having duplicates in a data range can lead to disorganization and confusion in your data list. Follow the steps below as we explain how your data can be cleaned up by removing duplicates.

 Applies To: Excel 2003, 2007 and 2010

  1. For this example; reference will be made to the screen shot below


2.  Select the data range A2:A20

3.  Select the Home Tab and as per screen shot below

4.  Select OK

5.  All the duplicates in the data range  will be highlighted as given below

6.   The duplicate values can then be deleted


If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email [email protected] and yours could be the next Tip of the Week.

Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations