How to find and remove duplicates in Excel

Data may often contain duplicate or redundant data entries. When cleaning up a dataset you may need to find and remove duplicates in the data.

Finding and removing duplicate data in Excel is such a common task that Excel has a standard commands (buttons in the toolbar) to perform these tasks, which makes it very easy to do!

Find duplicate values

If you need to find duplicates with data then you can use the Duplicate Values command in the Conditional Formatting menu to highlight duplicate values in your data.

1. Select the cells you want to check for duplicate values
2. Click the Home ribbon –> Conditional Formatting –> Highlight Cells Rules –> Duplicate Values

3. In the Duplicate Values menu, select Duplicate and the type of formatting to use to highlight the duplicate values in your data

Find duplicates in Excel example

In the example below we have a list of cities for which we want to find the duplicate values. Using the steps above and selecting to format duplicates with “Light Red Fill with Dark Red Text” in the Duplicate Values menu, we receive the highlighted duplicates in column D below.

Find unique values

If you need to find unique values with data then you can use the Duplicate Values command in the Conditional Formatting menu to highlight unique values in your data.

1. Select the cells you want to check for unique values
2. Click the Home ribbon –> Conditional Formatting –> Highlight Cells Rules –> Duplicate Values

3. In the Duplicate Values menu, select Unique and the type of formatting to use to highlight the duplicate values in your data

Find unique values in Excel example

In the example below we have a list of cities for which we want to find the unique values. Using the steps above and selecting to format duplicates with “Green Fill with Dark Green Text” in the Duplicate Values menu, we receive the highlighted unique values in column D below.

Remove duplicate values

If you need to find and remove the duplicates in your data then the Remove Duplicates command is best option. This is a commonly used command which can be found in the Data ribbon. Remove Duplicates will remove all duplicate values in your selected data while leaving only unique values (one of each). Here’s how to use this function:

1. Select the cells you want to check for unique values
2. Click the Data ribbon –> Remove Duplicates

3. In the Remove Duplicates menu, select the columns which contain duplicates which you want to remove and click OK. You must select one or more. If you only selected one column of data, then just click OK.

Find and remove duplicates in Excel example

In the example below we have a list of cities for which we want to find and remove the duplicates. Using the steps above we can remove all of the duplicate values, leaving only one of each value in column D.