[Easy] How to find and remove duplicates in Excel?

How to find and remove duplicates in

In this article, we’ll learn how to remove duplicates in Excel and clean our spreadsheets. Duplicate values in a dataset are annoying and may lead to substantial errors in any calculation which is undesired.

Also read: How to unhide and hide columns in Excel?

Different Ways to to Find and Remove Duplicates in Excel

Excel provides for methods to identify and remove duplicates to keep the dataset clean and compact. We will discuss methods to find and highlight duplicates which will help us determine what data to keep and what data to delete.

1. Highlight duplicates in Excel with conditional formatting

Follow these steps to highlight duplicates using conditional formatting:

  • Select the cells you want to check for duplicate values.
Find and Remove Duplicates in Excel
  • Click on Conditional Formatting in the Styles group on the Home tab.
Conditional formatting styles tab
  • From the list that appears, click Highlight Cells Rules.
Find and Remove Duplicates in Excel
  • From the Highlight Cells Rules, click the Duplicate Values option.
Find and Remove Duplicates in Excel
  • This opens the Duplicate Values dialog box, select the highlighting format and click OK. Here, I have selected the Green Fill with Dark Green Text formatting.
duplicate values dialog

The final result looks like this:

Find and Remove Duplicates in Excel

2. Remove Duplicates in Excel

The Remove Duplicates feature permanently erases duplicate data, so it is advisable to copy the original data to another worksheet to prevent accidental loss of any crucial data. Follow these steps to effectively remove duplicates in Excel:

  • Select the cells you want to check for duplicate values.
selection for duplicates 1
  • Click on Remove Duplicates in the Data Tools group on the Data tab.
remove duplicates data tools
  • Select the columns from which you wish to remove duplicates from the Remove Duplicates dialog box that appears and click OK.
Find and Remove Duplicates in Excel
  • A popup appears that tells us how many duplicate values were found and removed, and also the number of unique values that remain. Click OK.
Find and Remove Duplicates in Excel

The final result looks like this:

duplicates removed

3. Filter for unique values

Filtering for unique values is akin to removing duplicate values with the only difference being that filtering only temporarily hides the duplicate values. Follow these steps to filter unique values from the dataset:

  • Select the cells you want to check for duplicate values.
selection for duplicates 2
  • Click on Advanced in the Sort & Filter group on the Data tab.
advanced sort filter
  • From the Advanced Filter dialog box that appears, choose whether to filter the list in-place or copy to another location.

We will see how both the options work out in the following section:

  • Click Filter the list in-place radio button, check the Unique records only box, and click OK.
filter in place

The final result looks like this:

filter in place results

Notice how rows 5 and 8 are compressed to hide them as the filter for unique values only hides the duplicate values and doesn’t remove them.

  • Click the Copy to another location radio button, select the cells where you want to copy the unique values, check the Unique records only box, and click OK. Here, cells C2 to C9 are selected to copy the unique values.
advanced filter copy another location

The final result looks like this:

final results unique copy

Conclusion

In this tutorial, we learned how to highlight and duplicate values for easy identification. Then we learned how to permanently remove duplicates in Excel and also temporarily hide duplicate values.

References