Highlight Duplicates in Excel

How to find duplicate in Excel by using Conditional Formatting?

In this article, we will learn how to find duplicates and more in Microsoft Excel 2010.

To find the duplicates, we will use the “COUNTIF” function in “Conditional formatting”.

We will use the COUNTIF function to count the number of names, phone number and email id and Conditional formatting to highlight the duplicate values.

Let’s take an example to understand how we can find duplicates.

  • We have data in range A1:C15. Column A contains Student name, column B contains Mobile no. and column c contains Email Id.
  • Now, we want to find out the duplicates.




  • Select the range A2:C15.
  • Go to the “Home Tab” and then in the “Styles” group, select “New Rule” from the Conditional Formatting dropdown menu.



  • “New Formatting Rule” dialog box will appear. Now, select a rule type “Use a formula to determine which cells to format”.



  • Write the formula in the box of “Format values where this formula is true:”
  • =COUNTIF($A$2:$C$15,A1)=2
  • Click on “Format” button. A ‘Format Cells’ dialog box will appear.
  • Go to Fill tab and Font, choose the color as per the requirement and click on OK.



  • All duplicates will get highlighted with the color.



This is how we can find duplicates in Microsoft Excel.



Download – Highlight Duplicates in Excel – xlsx

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube