,

Extract Duplicate Values in Excel

Learn how to quickly find duplicate values in Excel using a combination of UNIQUE, COUNTIF, and FILTER functions.

Extract Duplicate Values in Excel

Duplicate values are those that occur more than once in a list of values. To extract these in Excel, consider we have a sample list of employee names:

Extract Duplicate Values list of dat

Before we get to the formula, let us convert the same into a table (something we always recommend to do before applying any function to increase formula readability and for scalability (when data expands).

To convert your data into a table, select the data (as a whole), press CTRL + T and assign a name.

Extract Duplicate Values select data

Now, assign the range of values (in our employee column) a name from the Name Manager.

In our sample, let the list of data be named EMPLIST.

Duplicate values are those that appear more than once in our data, to extract them requires the use of COUNTIF, FILTER, AND UNIQUE functions in Excel. Let us break down this formula into chunks to understand it better.

Firstly, to get values that appear more than once, we apply the COUNTIF function to the original list.

// for values that appear more than once are duplicates, this returns TRUE

Extract Duplicate Values returns true

What does COUNTIF do?

Extract Duplicate Values COUNTIF funation

Let us extract only this list from our EMPLIST using the Filter function.

What does FILTER do?

Extract Duplicate Values FILTER function

This gives the following as an output:

Extract Duplicate Values extract more than once

Since this formula returns the list of employees that appear more than once, we can use the Unique function to extract these names only once.

What does Unique do?

Extract Duplicate Values UNIQUE function

If you need this data to be sorted, use the SORT function around the Unique formula just created to get the data sorted in alphabetical order.

What does SORT do?

Extract Duplicate Values SORT function

Once applied, the duplicate values are extracted as shown below:

Extract Duplicate Values extracted duplicates

Check our dedicated YouTube video that explains how to extract duplicate values in Excel:

Previous
Next

Leave a Reply

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

Launch offer

All plans are free for 14 days.

Enjoy the lifetime 50% OFF introduction offer for our first 100 customers.

Built by INDZARA

Terms and conditions