Identifying the Duplicate Data in Excel sheet

Finding the duplicate data is quite simple in Excel. You do not require writing any formula but following a few mouse clicks in the menu/Toolbar.

In this tutorial, I will show you step by step of how to find the duplicate data in rows/columns in different ways. This is followed by steps for deleting the duplicate data.

Step 1 – Find the duplicate data

First of all, select the cells or range in your excel sheet that you want to identify for duplicate data.

For demonstration, I have selected the A column only as shown below:

remove dupes select row

Step 2 – Locate the Conditional Formatting Button

In the Home tab, find the Conditional Formatting as shown below:

duplicates excel

Step 3 – Find Duplicate Values option

Go to Highlight Cells Rules –> Duplicate Values

find duplicates values

Step 4 – finding the duplicates

After clicking Duplicate Values menu option, a dialog box appears as shown in the following graphic:

duplicates dialog

You will see, the duplicate values are highlighted as soon as “Duplicate Values” dialog appears.

In the dialog box, you can see “Format cells that contain” dropdown with two options: Duplicates and Unique. The Duplicate is the default value.

Excel duplicates format

In next dropdown, you may select the “value with” and select the formatting style that you want. Either use pre-defined or select the “Custom format” option for customizing the appearance of duplicate data.

You are done for identifying the duplicate data!

You may also choose the Unique option for getting the unique values for the selected cells.

How to remove duplicate data in Excel Sheets?

Before exploring further for searching duplicate data in specific rows or columns and identifying duplicates with certain rules in this tutorial; let us have first a look at the steps of removing duplicate data.

Removing duplicate data is even simple than searching it. Again, it is all based on a few clicks. Follow these steps:

Step1 – Selecting the data

Select the data that you want to find and remove duplicates. For the demo, I have selected column A and B (Product and Quantity).

Excel remove duplicate

Step 2

Go to the “Data” tab and find “Remove Duplicates” button:

Excel remove data menu

The dialog for “Remove Duplicates” will appear. There, you may see and confirm which columns to remove data from. The  “My data has headers” checkbox will take the headings from columns.

Excel remove duplicate options

If you want to remove from all columns then press, “Select All button”.

I selected only “Product” column and see how many records are eliminated:

Excel duplicate delted

Removing duplicate rows

Excel removed all identical data except the first one in the above example. If I select all columns (Product and Quantity) then see the output before and after using Remove Duplicates tool:

Excel duplicate bef a

You can see, two duplicate rows are deleted from the excel sheet.

You should keep the copy of data before using remove duplicate tool. As such, data delete permanently as using this option.

For immediate recovery of data, you may undo the operation from menu or press Ctrl+z key.

Highlight duplicates only for the specific value

Back to searching duplicates; in our first way of finding the duplicate values, we selected the cells and all duplicate values were highlighted. What if you want to highlight the only specific value in the complete sheet for duplicate occurrence?

For that, you may use the “New Rule” under the “Conditional Formatting” option as follows.

First, have a look at this example sheet:

Excel duplicate value

Now, we want to highlight only the duplicate occurrence of “Maze”.

Step 1:

Select the cells where you want to find and highlight the specific value. I selected from A1 to C10.

Step 2:

Again go to the Home à “Conditional Formatting” and select “New Rule”.

Excel new rule

In the “New Rule Formatting” dialog box, select the “Format only cells that contain”

Excel duplicate contains

Under “Edit the Rule Description”, select specific text from the first dropdown with “containing” in second dropdown and enter “Maze” text:

conditional-formatting

Use the format option for highlight color as per your liking and press OK. See the result:

duplicates highlight

You can see, all Maze values are highlighted for the selected cells.

You may use the other options in the dropdown for specifying the numeric value with “equal to”, “greater than or equal to”, “between” for two numbers etc. Similarly, if you have a date column, you may also select “Dates occurring” option.

Another quick way of highlighting the filtered duplicate values

The second way of highlighting the specific duplicate cells based on given text, numbers, dates etc. is even simpler. For that, again go to the Conditional formatting under the Home and select “Highlight Cells Rules”.

Unlike the first example in this tutorial for selecting the duplicate value, this time select “Text that contains” option.

duplicates highlight 2

Enter the text and formatting style and see how it highlights the duplicate values in the selected cells.

duplicate text

Note that, these tools are not specifically for duplicates checking (as we can see the second last option “Duplicate Values”). You may also use the Between option for specifying two numbers and Excel will highlight numbers in between that are not duplicate.

However, for our question of how to get filtered duplicate values highlighted, you can use that approach.

How to count the duplicates?

For getting the count for the duplicate values, you may use the Excel COUNTIF function. The COUNTIF function takes the range where you want to look up and the text that needs to be tested for duplication.

See the following example where I am using the same excel sheet as in above examples and getting the count of “Wheat” in the sheet. The COUNTIF formula used as follows:

=COUNTIF(A2:A10,”Wheat”)

The result:

duplicate count

A detailed tutorial is written about the COUNTIF in Excel.