**How do you find the duplicate values in your Excel columns by formula? With Excel 365, you need only 2 functions**

## Combination of FILTER and COUNTIF functions

To find the duplicate values in Excel by formula, you must associate the FILTER and COUNTIF functions.

**The COUNTIF function**We will create a logical test with this function to find duplicate values

**The FILTER function**This function will be used to keep only the duplicate values

In our example, we added the data into a table (Insert > Table), and the table name was ** tbl_Data**. This information will be used in the references 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])

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

## 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)

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))

Whatever the number of items you have, 2 times, 3 times, .... they will be returned by the formula 😀👍

## Related articles

- Remove Duplicates values in Excel
- UNIQUE Function – Remove duplicates with formulas
- In Excel, How to highlight missing values?