How to Highlight Duplicates in Excel?

We use Excel to save or accumulate information. Working with large Excel sheet, we may duplicate records or information and further, it becomes difficult for us to identify duplicates. Well, Excel provides a facility to highlight duplicates.

Analyzing the highlighted duplicates, you can determine whether you have to keep that record or you have to delete it. In this section, we will discuss the steps to highlight duplicates in excel.

Steps to Highlight Duplicates in Excel 2016:

Method 1

Step 1: First select the range of cells where you want to highlight the duplicates. Like in the image below, I have selected the range of cells A2:A17.

Step 2: In the Home tab you have a Styles group, here you have to click on Conditional Formatting drop-down button.

Step 3: From the displayed menu of Conditional formatting select Highlight Cell Rules which will further provide you options, and from these options you have to select Duplicate Values….

Step 4: A Duplicate Values window will appear where you can format the cell in your way. In this dialog box, we have two dropdown boxes. In the first drop-down box you have two options, ‘Unique’ & ‘Duplicate’ as we have to highlight the duplicates, select Duplicate.

In the second drop-down box, you have to make choice about how your duplicate would appear. Like, I have selected an option which will make the background of the cells with duplicate values, as yellow and the text would be in dark yellow colour as you can see in the image below. Click OK.

Method 2

Step 1: Again you have to select the range of cells where you want to highlight the duplicates.

Step 2: In the Home tab under the Styles group click on Conditional Formatting drop-down button. It will display options from where you have to select ‘New Rule’.

Step 3: A New Formatting Rule window appears here, under ‘Select a Rule Type:’, select the option where you can format cell that has unique or duplicate value.

Under ‘Edit Rule Description’ select ‘duplicate‘ in the dropdown box. Then click on Format button to format the cell with a duplicate in your way.

Step 4: Format Cells window will appear. Under the ‘Font’ tab you can change the font type and font colour. Under the ‘Border’ tab you can select a border for a cell with duplicate value. Under the ‘Fill’ tab you can select the background colour or pattern for the cell with duplicate value. Click OK.

In the New Formatting Rule window, again click OK

Step 5: You can see in the image below, that the duplicates are highlighted in the selected range.

In the New Formatting Rule window, under ‘Select a Rule Type:’ you can select the option which allows you design a formula, to identify which cells have to be formatted and use Format button to highlight duplicates.

So, this is all about the steps of highlighting the duplicates in excel. You can explore more methods to do the same. Hope the content is useful. Keep reading.