How to Find Duplicates in Excel

Duplicates are those which appear more than one time. Finding and highlighting duplicates in Excel is a common task that helps in data analysis and management. While working with a large Excel worksheet or converting many small worksheets into bigger ones, you may find many duplicates in it. In this article, we will look at different ways we can find duplicates in Excel.

How to Find Duplicates in Excel

To identify duplicates in Excel, you can use the COUNTIF Function. The formula may vary slightly depending on whether you want to include the first occurrence or not. Here are the steps to identify duplicates in Excel:

How to Find Duplicate Records Including 1st Occurrences

Suppose you have a list of items in column A that you want to check for duplicates. To find Duplicates, including the first occurrences, enter the following formula in cell B2(considering A2 is the topmost cell):

=COUNTIF(A:A,A2)>1

Copy the formula from cell B2 and drag the fill handle down to copy it to other cells in column B.

This will apply the formula to each cell and evaluate if it is a duplicate. This formula will return TRUE for duplicate values and False for unique values.

Note: If you want to find duplicates in a specific range of cells rather than the entire column, use a locked range by using the dollar ($) sign. For example, if you want to search for duplicates in cells A2:A10, use the formula:

=COUNTIF($A$2:$A$10,A2)>1

To make the result more accurate, you can use the IF Function. This allows you to assign labels to duplicate and unique values. For example:

=IF(COUNTIF($A$2:$A$10, $A2)>1,”Duplicate”,”Unique”)

This formula will return “Duplicate” for duplicate records and “Unique” for unique records.

If you only want to identify duplicates and leave unique values blank, modify the formula as follows:

=IF(COUNTIF($A$2:$A$8,$A2)>1,”Duplicate”,””)

This modified Formula will return “Duplicate ” for duplicate records and leave the unique records blank.

How to Find Duplicate Values in Excel

To do so, follow the steps below:

Step 1: Enter the Data

Before finding the duplicates, it’s essential to ensure that the data is properly formatted and organized in Excel.

Step 2: Select the data

Finding duplicate elements in the column. First, highlight the data in which we want to see whether there are any duplicate elements or not.

Step 3: Go to the Home tab and Select Conditional Formatting

Then, click on Home on the Ribbon. In the Styles group, click Conditional Formatting.

Step 4: In Conditional Formatting, Select Highlight Cells Rules and Choose Duplicate Values

Step 5: Select Duplicate from the drop-down and Select Color

In the Duplicate Values box, we have to select the formatting which we want to apply to the duplicate values, and then click OK.

We will find the duplicate elements

How to Find Triplicates in Excel

By default, Excel highlights duplicates( north) and triplicates ( 2). Follow the below steps to highlight only triplicates:

Step 1: Firstly clear all your previously applied rules.

Step 2: Now select a range of cells.

Step 3: Select the Conditional Formatting in the Styles group in the Home tab.

Step 4: Click the new rule

Step 5: Select ‘use a formula to determine which cells to format’.

Step 6: Enter a formula =COUNT($A$2:$A$9,A2)=3

Step 7: Select a formatting style and click OK.

Excel highlights the Triplicates (integer and string).

How to Find Duplicate Rows in Excel

Use COUNTIFS (with a S at the end) instead of COUNTIF to locate and highlight duplicate rows in Excel.

Step 1: Select the range A1:C16

Dataset

Step 2: Go to the Home tab and Select Conditional formatting

Click Conditional Formatting under the Styles group on the Home menu

Step 3: Click on New Rule

Excel New Rule

Step 4: Select ‘Use a formula to determine which cells to format’

Step 5: Enter the formula

=COUNTIFS(Business,$A1,Gender,$B1,Ethnicity,$C1)>1

Step 6: Select a formatting style and click OK

New Formatting Rule

Result: Excel highlights the duplicate rows.

Highlighted Duplicates

How to Highlight Duplicates in a Range (Multiple Columns)

You can use one of the following ways to find all instances of the same item in all the columns.

Highlight duplicates in Multiple columns including 1st occurrences

If the first instance of an item that appears in the data set more than once is known as a duplicate, use Excel’s built-in rule for duplicates.

You can also create a conditional formatting rule with this formula:

COUNTIF(range, top_cell)>1

Highlighting Entire Rows based on Duplicate Values in one Column

If you want to highlight whole rows based on duplicated records in a specific column follow the below steps:

First, select the whole rows, and then create a rule with one of the following formulas:

If you are finding duplicates from A2 to A10.

To highlight duplicate rows excluding 1st occurrences:

=COUTNIF($A$2:$A$2, $A2)>1

To highlight duplicate rows including 1st occurrences:

=COUNTIF($A$2:$A$10,$A2)>1

where A2 is the first cell and the A10 is the last cell in the column that you want to check for duplicates. You should know the use of absolute and relative cell references.

Highlighting Consecutive Duplicates Cells in Excel

There might occur some situations where the user does not need to highlight all duplicates in a column but show only consecutive duplicate cells. To do this, select the cells with the data( excluding the column header) and create a conditional formatting rule with one of the following formulas:

To highlight consecutive duplicates without 1st occurrence:

=$A1=$A2

To highlight consecutive duplicates with 1st occurrences:

=OR($A1=$A2, $A2=$A3)

FAQs on Duplicates in Excel

Q1. Can Excel identify duplicates without deleting them?

Answer:

Yes, we can highlight the duplicate values by using Conditional Formatting without deleting the duplicates.

Q2. Is there a formula to remove duplicates?

Answer:

The unique values in a column are returned by the function UNIQUE. It is only accessible through Excel Online or Microsoft Office 365.

Q3: How you can Highlight duplicates in Excel in a couple of clicks?

Answer:

• With any cell in your table selected, click the Dedupe table button, and the clever add-in will pick up the whole table.
• The Dedupe Table dialog box window will open with all the columns selected automatically, and the color duplicates option chosen by default now click ok.

Q4: How can I filter duplicates in Excel?

Answer:

To filter unique values, follow the below steps:

Step 1: Click data

Step 2: Select Sort and Filter

Step 3: Now choose Advanced.