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:

- Spurs
- Kings
- Warriors
- Wizards
- Heat

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.

**Additional Resources**

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

Excel: How to Count Duplicates

Excel: How to Highlight Duplicate Rows

Excel: How to Find Duplicates Using VLOOKUP

Excel: How to Remove Duplicate Rows Based on Two Columns