How to Only Keep Duplicate Values in Excel (With Example)

Often you may want to only keep duplicate values in a dataset in Excel.

For example, suppose you have the following dataset that contains the names of various basketball teams:

Suppose you’d like to filter the dataset so that only the teams with duplicate values are kept in the dataset.

The following example shows exactly how to do so.

Example: Only Keep Duplicate Values in Excel

First, we need to identify the rows with team names that occur more than once.

To do so, we can type the following formula into cell B2:

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

The following screenshot shows how to use this formula in practice:

This formula checks if each team name in column A occurs more than once.

If the team name does occur more than once, the formula returns TRUE.

Otherwise, the formula returns FALSE.

Next, we can type the following formula into cell D2 to filter the values so that only the team names that occur more than once are kept:

=FILTER(A2:A13, B2:B13=TRUE)

The following screenshot shows how to use this formula in practice:

Noticed that the filtered dataset only contains the rows for the teams that occur more than once.

For example, the following teams have all been filtered out:

Each of these team names only occurred once in the original dataset.

Note that we used the FILTER function to filter column A for rows where the corresponding value in column B was TRUE.

This allowed us to filter the original list to only contain the values in the Team column for any team that occurred more than once.

The following tutorials explain how to perform other common tasks in Excel: