How to Highlight Duplicate Entries in Excel

 Highlight Duplicate Entries

How to Highlight Duplicate Entries in Excel

In this blog, we will learn about how to “Highlight Duplicate Entries” in Excel. 

Sometimes, we have a huge amount of data set in Excel. And again we merge that data set with another data set. So there are some more possibilities to have of duplicate entries in it.

It is quite easy to find duplicate entries in a small amount of dataset. But in some cases, data may be huge. And in that case, it would be difficult to find duplicate entries within a dataset.

 

There are multiple options to find the duplicate values within a dataset in Excel. 

In this tutorial, we will learn about two ways to find the duplicate entries in the dataset.

 

Highlight Duplicate Entries within one column

 

Here we have a data in one column and there are some duplicate values are also appearing in this data.

Highlight Duplicate Entries

 

So, in this case, we will use two methods to find duplicate values quickly.

Method 1

 

Highlight Duplicate Entries With Conditional Formatting

 

Use “Conditional Formatting” to highlight duplicate records with one column. Select all the records by pressing “Ctrl + Shift + Down Arrow”.

 

Go to “Home” tab –> Conditional Formatting –> Highlight Cells Rules –>Clcik “Duplicate Values”. See below image:- 

 

Duplicate_Entries_1

 

When we click on ‘Duplicate Values”, a box will appear with a drop-down option of “Duplicate” and “Unique” records. 

When we select “Duplicate” it will show duplicate records only and select “Unique” it will show us unique records only. It is amazing, just try it.

 

 

Duplicate_Entries_2

 

If we are looking for “Duplicate Values” we will select “Duplicate” and press “OK”. All the duplicate records are highlight now. If we need to find unique records, then we will select “Unique” in place f “Duplicate”. We will get all the “Unique” records.

 

                                      Method 2

 

Highlight Duplicate Entries With Countif Formula

 

Here we will use another method to highlight duplicate entries in a dataset.

First of all, we will select all the data by pressing “Ctrl + Shift + Down Arrow”.

 

Go to “Home” –> Conditional Formatting –> Click “New Rule”

Duplicate_Entries_3

 

 

Once click “New Rule” below given image will appear.

 

Duplicate_Entries_4

 

Select the rule “Use a formula to determine which cells to format”. Write the given formula in the box “Format Values where this formula is true”.

 

Type formula “=COUNTIF($A1:$A24, A1)>1” and then click on “Format” button given below to give a color to duplicate entries.  See image:-

 

Duplicate_Entries_5

 

 

Click “OK”

Duplicate_Entries_06

 

See, in the previous image where the preview box was blank, now filled with the selected color.

 

Once, we come to this stage, press “OK” button. See the image below, duplicate records have been identifying.

Duplicate_Entries_06

 

These are the easiest way to find the duplicate entries.

Now we will apply the same method to find the duplicate values in two columns.

 

How to Highlight Duplicate Entries Within Two Column

Method 1

Highlight Duplicate Entries With Conditional Formatting

 

Here we will learn how to find duplicate entries when to compare two columns. This is my favorite method to find duplicate values within two columns.

 

First of all, we need to select one column data by selecting top cell and press “Ctrl +  Shift + Down Arrow”. Then keep pressing “Ctrl” button and select the second whole column data with the help of mouse.

 

All the data is selection mode now. Go to “Home”–> Conditional Formatting –> Highlight Cell Rules –> Duplicate Values.

 

Duplicate_Entries_08

 

Here we can see in the in the box there are two option “Duplicate” and “Unique”. If we select “Unique” from the drop-down, all the “Unique” record will be visible. If we select “Duplicate” only duplicate records will be visible.

 

Method 2

Highlight Duplicate Entries in Two Columns with Countif Formula

 

Now we will find duplicate values with the help of “COUNTIF” function in two columns. As we discussed earlier in this blog, “How to find duplicate entries in one column with COUNTIF Formula”. Here we will learn how to find the duplicate in two columns with COUNTIF formula.

 

First of all, select all the dataset where to find the duplicate entries. Go to “Home” tab choose “Conditional Formatting” click “New Rule”. The following box will appear and then choose “Use format to determine which cells to format” from the given option. Write the formula “=COUNTIF($A$1:$B$19, A1)>1”  in the below-given formula box.

 

Duplicate_Entries_09

Now click on “Format” button to choose a color for duplicate entries in the data set.

 

Duplicate_Entries_10

 

Select color and press “OK”. Following image will appear.

Duplicate_Entries_11

Again press “OK” button, see duplicate entries in the given below image. And see the result below:-

 

Duplicate_Entries_12

All the duplicate records are now identified.  Now remove duplicate entries and enjoy the unique dataset.

 

Leave a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.