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.
- Click on Conditional Formatting in the Styles group on the Home tab.
- From the list that appears, click Highlight Cells Rules.
- From the Highlight Cells Rules, click the Duplicate Values option.
- 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.
The final result looks like this:
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.
- Click on Remove Duplicates in the Data Tools group on the Data tab.
- Select the columns from which you wish to remove duplicates from the Remove Duplicates dialog box that appears and click OK.
- 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.
The final result looks like this:
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.
- Click on Advanced in the Sort & Filter group on the Data tab.
- 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.
The final result looks like this:
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.
The final result looks like this:
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
- Find and remove duplicates – Excel (microsoft.com)
- Filter for unique values or remove duplicate values – Excel (microsoft.com)