This page shows you how to remove duplicates in Excel using three different methods.
Note that these methods show how to remove duplicate cells from your spreadsheet. If you want to find and remove entire rows that are duplicated, see the Remove Duplicate Rows in Excel page.
For each of the methods decribed below, we use the above simple spreadsheet on the right, which has a list of names in column A.
We first show how to use Excel's Remove Duplicates Command to remove duplicates and then we show how to use Excel's Advanced Filter to perform this task. Finally, we show how to remove duplicates using the Excel Countif Function.
Recent versions of Excel (2007 and later) have the Remove Duplicates command, which is located in the Data Tools group, within the Data tab of the ribbon.
In order to use the Remove Duplicates Command:
You will be presented with the Remove Duplicates dialog box, as shown below:
This dialog box allows you to select which columns of your data set you want to check for duplicate entries. In the example spreadsheet above, we only have one column of data (the 'Name' field). Therefore we leave the 'Name' field selected within the dialog box.
Once you have ensured that the required field(s) are checked in the dialog box, click OK.
Excel will then delete the duplicate rows, as required and will present you with a message, informing you of the number of records removed and the number of unique records remaining (see below).
The resulting example spreadsheet is shown on the rightabove. As required, the duplicate cell A7 (containing the second occurrence of the name 'Laura CARTER') has been removed.
Note that the Excel Remove Duplicates command can also be used on data sets with multiple columns. An example of this is provided on the Remove Duplicate Rows page.
The Excel advanced filter has an option that allows you to filter unique records in a spreadsheet and copy the resulting filtered list to a new location.
This gives you a list that contains the first occurrence of a duplicated record, but does not contain any further occurrences.
To remove duplicates using the Advanced Filter:
Select the Excel Advanced Filter option from the Data tab at the top of your Excel workbook(or in Excel 2003, this option is located in Data→Filter menu).
Within this dialog box:
The resulting spreadsheet, with the new data list in column C, is shown on the rightabove.
It can be seen that the duplicate value "Laura CARTER" has been removed from the list.
Warning: The following method will not work if the contents of your cells are over 255 characters long, as Excel functions are limited to text strings that are less than 256 characters in length.
Another way to remove duplicates in a range of Excel cells is to use the Excel Countif Function.
As shown in the formula bar of the above spreadsheet, the format of the Countif function in cell B2 is:
|=COUNTIF( A$2:A3, A3 )|
=COUNTIF( A$2:A4, A4 )
=COUNTIF( A$2:A5, A5 )
Now that we have used the Excel Countif function to highlight the duplicates in column A of the example spreadsheet, we need to delete the rows for which the count is greater than 1.
The following steps show how to remove several duplicates at once, (after they have been highlighted using the Countif function):
You will be left with a spreadsheet in which the first occurrence of each value is hidden. I.e. only the duplicate values are displayed.
Remove the filter and you will be left with the spreadsheet shown above on the right, in which the duplicate in cell A7 has been removed.You can now delete the column containing the Countif function to return to the original spreadsheet format.