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, Highlight & Remove Duplicates in Excel.xlsx

Table of Contents

Find Duplicates in Excel Using IF & COUNTIF with Customized Message

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.

Syntax

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

Formula

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

Formula Explanation

The COUNTIF function compares the content of cell B2 with that of the range $B$2:$B2. The function counts the occurrences of the value in this expanding range, and the IF statement evaluates whether the count is greater than 1. If the count is greater than 1, it indicates that the value appears more than once in the range, and the formula returns “Duplicate”. Conversely, if the count is 1 or less, signifying that the value is unique within the range, the formula returns “Unique”. This formula is designed to dynamically identify duplicate values as it is copied down to different rows in column B.

To find duplicates with IF and COUNTIF functions, follow these steps below:

Select a blank cell
Type this formula: =IF(COUNTIF($B$2:$B2,B2)>1,”Duplicate”,”Unique”)

Press ENTER to insert the formula into cell C2.
Now, select the entire column from cell C2 to cell C10.
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.

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

4+ Methods to Filter Duplicate Values in Excel

Find Duplicates in Excel with Conditional Formatting Highlighting Cells

Highlighting 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. So, go through these steps below to highlight cells containing duplicates:

Select the range of data to highlight.

Click on the Home tab.
Now, select the Styles group.
Click on the Conditional Formatting drop-down.

Navigate Highlight Cells Rules ⋙ Duplicate Values.
Select a formatting color combination from the Duplicate Values dialog box.
Then, click OK.

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

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.

Formula

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

To remove duplicates in Excel using the Conditional Formatting, go through these steps below:

Create a blank column first.
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.
Press ENTER to insert the above formula in cell C2.

Double-click on the cell C2 to auto-fill the formula and select the range.
Go to Home menu > Styles group > Conditional Formatting drop-down > Highlight Cells Rules > Text that Contains command.
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”.

Select the entire Duplicate column with the column header too.
Navigate the Data menu > Sort & Filter group > Filter command.
Click on the drop-down of the column, Duplicate.

Select “Duplicate” only from the context menu and hit OK.
Only the duplicate items will be filtered out with all of them selected.
Now, press CTRL + – from your keyboard.
Or, right-click to choose Delete from the context menu.
Then, just hit OK.

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

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!

Frequently Asked Questions

Is there a quick way to highlight in Excel?

To quickly highlight cells in Excel:

Select the range of cells you want to highlight.

Use the keyboard shortcut:
For Windows, press ALT + H + H to open the Home tab, then press H for the Fill menu, and finally press H for the Highlight Cells option.
For Mac, press Command + Option + H to open the Home tab, then press H for the Fill menu, and finally press H for the Highlight Cells option.

This shortcut allows you to highlight cells without using the mouse. Adjust the keys based on your operating system. This quick method streamlines the highlighting process, making it efficient and convenient.

How do I show only duplicates in Excel?

To show only duplicates in Excel:

Select the column or range where you want to identify duplicates.
Go to the Home tab on the ribbon.
Click on Conditional Formatting in the Styles group.

Choose Highlight Cells Rules from the drop-down menu.
Select Duplicate Values from the sub-menu.
In the dialog box, choose a formatting style for the duplicates and click OK.

This will highlight or format cells with duplicate values, making them easily visible. Adjust the range and formatting options as needed.

How do I auto highlight in Excel?

To automatically highlight specific values in Excel:

Select the range of cells where you want the highlighting to occur.

Go to the Home tab on the ribbon.
Click on Conditional Formatting in the Styles group.
Choose New Rule from the drop-down menu.

In the “New Formatting Rule” dialog box, select “Format cells that contain“.
Choose the condition (e.g., “Cell Value,” “Specific Text,” etc.) and set the criteria.
Click on the Format button to choose the formatting style for the highlighted cells.

Click OK to apply the formatting.

The specified cells will now be automatically highlighted based on the defined rule. Adjust the range and formatting options according to your requirements.

How do I remove highlighted cells in Excel?

To remove highlighted cells in Excel:

Select the range of cells from which you want to remove the highlighting.
Go to the Home tab on the ribbon.
Click on Conditional Formatting in the Styles group.

Choose Clear Rules from the drop-down menu.
Select “Clear Rules from Selected Cells” to remove highlighting only from the current selection.

This will remove any conditional formatting or highlighting applied to the selected cells, leaving them in their default appearance. Adjust the range and options as needed.

Rate this post