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.

Using IF and COUNTIF function to Find Duplicates in Excel

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.

Formula result : Find Duplicates in Excel

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

Find Duplicates in Excel: 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.

Selecting cells to Highlight Duplicates in Excel

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.

Using Conditional Formatting to Highlight Duplicates in Excel

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.

Highlight Duplicates in Excel: Final Result

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

Using IF and COUNTIF Function to find duplicates in Excel

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.

Duplicate values and identified in Excel using a formula

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

Using conditional formatting to highlight duplicates in Excel

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.

Selecting highlighted duplicates to remove duplicates in Excel using conditional formatting

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

Using Filter command to filter out the duplicates in Excel

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.

Filtering out the duplicates in Excel

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

Filtered duplicates in Excel

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.

Remove Duplicates in Excel Using Conditional Formatting

Final Result

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

Output: Remove Duplicates in Excel Using Conditional Formatting

🔗 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]

(Visited 60 times, 1 visits today)

Similar Posts

Leave a Reply

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