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.

 

img1

 

  • 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.

img2

 

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

img3

 

  • 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.

img4

 

  • All duplicates will get highlighted with the color.

img5

 

This is how we can find duplicates in Microsoft Excel.

 

xlsx-1567

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>