Home » Function » Lookup functions » How to find duplicate values in Excel?

How to find duplicate values in Excel?

Reading time: 2 minutes
Last Updated on 11/05/2023 by Frédéric LE GUEN

How to find the duplicate values in your Excel columns? It's very easy with a single formula.

How to find duplicates values

Combination of FILTER and COUNTIF functions

To find the duplicate values, we need to associate the FILTER and COUNTIF functions.

  1. COUNTIF will be used as a logical test to know if a value is present more than one time
  2. FILTER will use the logical test to return only the duplicate values

In our example, the data have been added to a Table (Insert > Table), and the table name is tbl_Data. This information will be used in the reference of the formula

Create the test to find the duplicate values

Since the dynamic array functions have been added to Excel, you can write the standard Excel functions differently. For instance, with COUNTIF, you can write a range of cells as criteria, and not only for one cell like it us to be.

=COUNTIF(tbl_Data[Value],tbl_Data[Value])

COUNTIF to count how many time each value

And then, we simply convert this function into a logical test for all the values greater than 1

=COUNTIF(tbl_Data[Value],tbl_Data[Value])>1

Logical test when there is duplicate values

Keep only the duplicate values

To keep only duplicate values, we will insert the previous logical test as a criterion for the function FILTER.

The beauty of FILTER is to return the values when the test is TRUE.

=FILTER(tbl_Data[Value],COUNTIF(tbl_Data[Value],tbl_Data[Value])>1)

Formula to return duplicate values

Keep each value once

Of course, it's not necessary to return every duplicate value. So you can embed the previous formula into the UNIQUE function.

=UNIQUE(FILTER(tbl_Data[Value],COUNTIF(tbl_Data[Value],tbl_Data[Value])>1))

Keep only one duplicate value

Leave a Reply

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

How to find duplicate values in Excel?

Reading time: 2 minutes
Last Updated on 11/05/2023 by Frédéric LE GUEN

How to find the duplicate values in your Excel columns? It's very easy with a single formula.

How to find duplicates values

Combination of FILTER and COUNTIF functions

To find the duplicate values, we need to associate the FILTER and COUNTIF functions.

  1. COUNTIF will be used as a logical test to know if a value is present more than one time
  2. FILTER will use the logical test to return only the duplicate values

In our example, the data have been added to a Table (Insert > Table), and the table name is tbl_Data. This information will be used in the reference of the formula

Create the test to find the duplicate values

Since the dynamic array functions have been added to Excel, you can write the standard Excel functions differently. For instance, with COUNTIF, you can write a range of cells as criteria, and not only for one cell like it us to be.

=COUNTIF(tbl_Data[Value],tbl_Data[Value])

COUNTIF to count how many time each value

And then, we simply convert this function into a logical test for all the values greater than 1

=COUNTIF(tbl_Data[Value],tbl_Data[Value])>1

Logical test when there is duplicate values

Keep only the duplicate values

To keep only duplicate values, we will insert the previous logical test as a criterion for the function FILTER.

The beauty of FILTER is to return the values when the test is TRUE.

=FILTER(tbl_Data[Value],COUNTIF(tbl_Data[Value],tbl_Data[Value])>1)

Formula to return duplicate values

Keep each value once

Of course, it's not necessary to return every duplicate value. So you can embed the previous formula into the UNIQUE function.

=UNIQUE(FILTER(tbl_Data[Value],COUNTIF(tbl_Data[Value],tbl_Data[Value])>1))

Keep only one duplicate value

Leave a Reply

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