How to Remove Duplicate values in Excel?

Remove Duplicates in Excel Worksheet

To remove duplicate values in Excel, follow these steps.

1. Open the Excel Workbook.
2. Select the Range of values that has duplicates values.
3. Select "Data" from Menu.
4. Click "Remove Duplicates" in Data Tools tab. (Shortcut - 'Alt + A + M')

You can see that the Excel has removed the duplicate entries in the selected range. Before displaying the results Excel will also display user a message box with number of duplicates removed and how many unique values are found. Note: This option is applicable to Excel 2007 or above. Finally, only unique values are being displayed. Also Read: Excel VBA Code to remove duplicates in Worksheet Range or Column

2. Remove Duplicates in Excel with Advanced Filter

This is an additional trick. It is useful, if you want to copy unique values to a new location by deleting duplicate values. These are the steps:

1. Choose the range of values with duplicates entries.
2. Click on 'Data' from Menu.
3. Select 'Advanced' in 'Sort & Filter' tab.
4. Click the check box near "Unique Records Only" to Omit Duplicates.
5. Choose a destination cell for output
6. Click OK.

The output is the same in both these cases. But, the first option has more flexibility to choose multiple row headers. Here in autofilter, we can only remove duplicates values from one column data at a time. If you want to see a illustration of this tricky formula, watch the below video and leave your comments.

YouTube Tutorial - Remove Duplicate Values in Excel File

This is a quick video that explains how to use "Countif" formula to remove duplicate values in excel. It is just a minute long video and will help you to learn the new technique. [embed]http://youtu.be/7wFQ6bYgBjk[/embed]