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:
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.
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.
=COUNTIF(EMPLIST, EMPLIST)>1 // for values that appear more than once are duplicates, this returns TRUE
What does COUNTIF do?
Let us extract only this list from our EMPLIST using the Filter function.
=FILTER(EMPLIST, COUNTIF(EMPLIST, EMPLIST) > 1))What does FILTER do?
This gives the following as an output:
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.
=UNIQUE(FILTER(EMPLIST, COUNTIF(EMPLIST, EMPLIST) > 1))What does Unique do?
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.
=SORT(UNIQUE(FILTER(EMPLIST, COUNTIF(EMPLIST, EMPLIST) > 1)))What does SORT do?
Once applied, the duplicate values are extracted as shown below:
Check our dedicated YouTube video that explains how to extract duplicate values in Excel:
