Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]

Finding, Highlighting, and Removing duplicates are an integral part of data cleansing in Excel. With that being said this tutorial with guide you to find, highlight & remove duplicates in Excel.

Find Duplicates in Excel

In the column, Ice Cream Flavors, I’ve got a list of different ice cream flavor names. I’ve created a blank column named “Duplicate” to identify the ice cream flavor names as “Duplicate” and “Unique”.

To do all of these, I’ll be using a formula consisting of the IF and COUNTIF functions.

Here’s the Syntax of the Formula

IF(COUNTIF(range, criteria),value_if_true,value_if_false)

Usage Guide

Step_1: Type

=IF(COUNTIF($B$2:$B2,B2)>1,"Duplicate","Unique")

In cell C2.

Formula Explanation

The COUNTIF function compares the content of cell B2 with that of the range $B$2:$B2. If it finds any matching more than once, it returns TRUE. Otherwise, it returns FALSE.

When the COUNTIF function returns a TRUE, the IF function returns “Duplicate”.

When the COUNTIF function returns a FALSE, the IF function returns “Unique”.

In the range, of $B$2:$B2, the first cell is totally locked. So it remains standing still. But the last cell of the range,$B2 is not totally locked. Its column number, B is locked but the row number, 2 to free to change. Thus it changes as $B2, $B3, $B4, etc.

In case of the first occurrence of a duplicate value, the COUNTIF function finds a value unique. But from the next second and onward occurrences of a value, the COUNTIF function identifies a value as duplicate.

Step_2: Press ENTER to insert the formula into cell C2.

Step_3: Now select the entire column from cell C2 to cell C10.

Step_4: Then press CTRL + D.

This will instantly copy down the formula applied in cell C2 with changing cell references.

As the formula is applied to the entire cell, all the ice cream flavor names in column B will be marked either as “Duplicate” or “Unique” in column C.

Final Result

🔗 How to Find Duplicates in Two Columns in Excel (7 Methods)

🔗 4+ Methods to Filter Duplicate Values in Excel

Highlight Duplicates in Excel

Highlighting the duplicate values is very easy in Excel. You can use Conditional Formatting to easily do that.

Now let me show you the whole process in detail.

Step_1: Select the range of data to highlight.

Step_2: Click on the Home tab.

Step_3: Now select the Styles group.

Step_4: Click on the Conditional Formatting drop-down.

Step_5: Navigate Highlight Cells Rules ⋙ Duplicate Values.

Step_6: Select a formatting color combination from the Duplicate Values dialog box.

Step_7: Then click OK.

Final Result

All the duplicate values in the selected range will be highlighted with the chosen color combination.

🔗 How to Compare Rows in Excel for Duplicates (7 Ways)

🔗 How to Find Similar Text in Two Columns in Excel (5 Ways)

Remove Duplicates in Excel Using Conditional Formatting

Excel offers several methods to remove duplicate values from your spreadsheet.

Here I’m going to show you remove duplicates using the Conditional Formatting feature in Excel.

Step_1: Create a blank column first.

For example, I’ve created a blank column in column C named “Duplicate”.

Step_2: Type the formula in cell C2.

=IF(COUNTIF($B$2:$B2,B2)>1,"Duplicate","Unique")

🔴 Read an explanation of how this formula works from_here.

Step_3: Press ENTER to insert the above formula in cell C2.

Step_4: Double-click on the lower-right corner of cell C2 to auto-fill the formula till cell C10.

Now all the duplicate ice cream flavor names will be identified as “Duplicate” in the column, Duplicate.

Step_5: Select the range C2 to C10.

Step_6: Follow: Home menu ⋙ Styles group ⋙ Conditional Formatting drop-down ⋙ Highlight Cells Rules ⋙ Text that Contains command.

Step_7: Insert the text “Duplicate” and select your preferable cell color in the “Text that Contains” dialog box.

All the cells having the text “Duplicate” will be highlighted in the column, “Duplicate”.

Step_8: Select the entire Duplicate column with the column header too.

Step_9: Navigate Data menu ⋙ Sort & Filter group ⋙ Filter command.

Step_10: Click on the drop-down icon at the lower-right corner of the column, Duplicate.

Step_11: Select “Duplicate” only from the context menu.

Step_12: Now hit OK.

Only the duplicate items will be filtered out with all of them selected.

Step_13: Now press CTRL + – from your keyboard. Or, you can right-click on the selected filtered items and choose Delete from the context menu.

Anyways, both actions prompt a small dialog box that asks, “Delete entire sheet row?”

If you want to delete all the duplicate rows,

Step_14: Then just hit OK to proceed.

Final Result

All the duplicate items will be deleted straightway leaving the unique rows left behind.

🔗 5 Ways to Find Matching Values in Two Worksheets in Excel

🔗 5+ Formulas to Find Duplicates in One Column in Excel

Conclusion

I’ve discussed some effective techniques to find, highlight, and delete duplicates in Excel. I expect you’ve found this article useful. You can read more articles relating to Excel from the Blog page of our website. Have a fantastic workday!

🔗 How to Remove Duplicates in Excel [13 + Different Methods]

🔗 4+ Ways to Find Duplicates in a Column and Delete Rows in Excel

🔗 Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]