In Excel, you can remove duplicates in 2 different way
- By tool
Remove duplicates from the ribbon Data
- By formula
Excel 365 and Excel 2021 provide the UNIQUE function
Remove Duplicates from the Ribbon
You can remove duplicate values from the ribbon with this menu Data > Remove Duplicates.
To extract values without duplicates
- Select the column with your values, including the headers
- Click on the menu Remove Duplicates
In the next dialog box, indicate if the first row of your selection is the header of your column. If so, this row will not be included in the process to remove duplicates.
Click on OK. The process to remove the duplicates starts immediately.
At the end of processing, a dialog box shows you the number of rows deleted and the number of unique values remain.
Remove duplicates on multi-column Excel
This tool is really impressive because it also works if you select more than 1 column. For instance, in this exercise, you want to extract the unique Last Name + First name.
- Select your 2 columns
- Activate the tool Remove duplicates
- Check the option My data has headers
- Click OK
This time, only 2 rows have been removed. It's because Bill MANVILLE and Bill JELEN are considered as 2 different values. When you select many columns, Excel merges the content of all the cells like if it was only one cell.
Remove duplicates with a formula
If you work with Microsoft 365 or Excel Online, you can return a list of data without duplicates with the UNIQUE function
But in some situations, you can not use this tool. It's not because there is a bug but because the data are too complex, like in this example.