Highlight Duplicates in Excel

How to Highlight Duplicate Values in Excel?

Below are the steps to find or highlight duplicate values in Excel.

• Step 1: Select the range of list that contains duplicate values. (Select only the data range, not the entire worksheet)

• Step 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

The excel shortcut keys to highlight duplicate excel values is:

• Step 3: Once you click on Duplicate Values, it will open up the below dialogue box. Here we can select the type of formatting in excel we want. We can highlight both the cell and the text with three different colors, we can only fill the cell, we can highlight only the text values, or else we can highlight the duplicate cells borders.

 

• Step 4: Once the formatting type is selected, it will highlight all the duplicate excel values in the range. Here I have selected Light Red Fill with Dark Red Text.

Highlight Duplicate Values in the Entire Worksheet

We can not only highlight the duplicate excel values in a single column; rather, we can do this task across the entire worksheet as well.

I have data in the first two columns. I want to give conditional formatting so that in the future if I enter any kind of duplicate values, it should highlight the duplicate value for me.

• Step 1: Select the entire invoice number column. The reason we need to select the entire because here I am creating a validation so that if any of the duplicate entries are made in the future, it should automatically notify me the duplicate value is made.

 

• Step 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

• Step 3: Select the color you want to highlight. Here I have selected Green to fill with Dark Text.

• Step 4: Here, I have already found four duplicate values.

• Step 5: Now, try to enter any of the duplicate invoice numbers it will find and highlight the duplicate excel entry.

Remove Duplicate Values in Excel

Finding and Highlighting the duplicate values is definitely a needful skill at this point in time, and you learned that in the previous example. In this section of the article, I will show you how to remove those duplicates from the selection.

• Step 1: Select the duplicate data range.
• Step 2: Go to Data > Remove Duplicates.

The shortcut key to Remove Duplicates in Excel is:

• Step 3: Once you have selected the Remove Duplicate Values option, it will open up the below dialogue box. Since I have only one column, it is showing that single column header here.

Note: Make sure My data has headers checkbox is ticked.

• Step 4: Click on the Ok button. It will instantly remove all the duplicate values from the selection. It will also show you the prompt on how many duplicates are removed and how many unique values are remaining.

Look at the above image; it has given a dialogue box stating 3 duplicate values found and removed; 16 unique values remain.

Things to Remember

• We can select different formatting styles under the custom formatting option.
• While removing the duplicates, it is very important to select the correct column header; otherwise, there are chances our data goes for a toss.
• Rely on conditional formatting for highlighting future duplicates as well.
• Finding and Highlighting duplicate excel values makes the data more accurate.
• There are many other cool options residing under conditional formatting.

Recommended Articles

This has been a guide to Find and Highlight Duplicates in Excel. Here we discuss how to find and highlight duplicate values in Excel using the Conditional Formatting Method along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

• Excel Borders
• Highlight Every Other Excel Row
• Removing Duplicates in Excel
• Advanced Filter in Excel