Remove Duplicates In Excel

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.

Remove Duplicate Cells in Excel

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.

Remove Duplicates Using the Excel Remove Duplicates Command

The Remove Duplicates command is located in the 'Data Tools' group, within the Data tab of the Excel ribbon.

To remove duplicate cells using this command:

Select any cell within the data set that you want to remove the duplicates from, and click on the Remove Duplicates button.
You will be presented with the 'Remove Duplicates' dialog box 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.

Remove Duplicates Using the Excel Advanced Filter

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 column(s) to be filtered (column A in the example spreadsheet above);
(Alternatively, if you select any cell within the current data set, Excel will automatically select the entire range of data when you activate 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).
You will be presented with a dialog box showing you the options for the Excel advanced filter (see below).

Within this dialog box:
- Select the option Copy to another location;
- Make sure that the range that has been entered into the List range: field is correct;
- In the Copy to field, enter the location that you want to copy the new list to.
  (Note that this location must be in the current worksheet. In this example, cell C1 of the current Worksheet "Sheet1" has been selected as the 'copy to' location);
- Check the Unique records only box;
- Click OK.

Results of Removing Duplicate Cells Using the Excel Advanced Filter

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.

You can now delete the columns to the left of your new data list (columns A-B in the example spreadsheet) to return to the original spreadsheet format.

Remove Duplicates Using the Excel Countif Function

Warning: This method will only work if the contents of your cells are less than 256 characters in length, as Excel functions cannot handle text strings that are longer than this.

Step 1: Highlight the Duplicates

Another way to remove duplicates in a range of Excel cells is to use the Excel Countif Function.

In order to illustrate this, we will again, use the simple example spreadsheet (repeated on the rightabove), that has a list of names in column A.

In order to find any duplicates in the list of names, we enter the Countif function in column B of the spreadsheet (see below). This function shows the number of occurrences of each name up to the current row.

Highlight Excel Duplicate Cells Using Countif

As shown in the formula bar of the above spreadsheet, the format of the Countif function in cell B2 is:

=COUNTIF( A$2:A2, A2 )

Note that this function uses a combination of Absolute and Relative Cell References. Due to this combination of reference styles, as the formula is copied down column B, it becomes,

=COUNTIF( A$2:A3, A3 )
=COUNTIF( A$2:A4, A4 )
=COUNTIF( A$2:A5, A5 )
etc.

Therefore, the formula in cell B4 returns the value 1 for the first occurrence of the text string "Laura CARTER", but the formula in cell B7 returns the value 2 for the second occurence of this text string.

Step 2: Delete the Duplicate Rows

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.

In the simple example spreadsheet, it is easy to see, and to delete, the single duplicate row. However, if you have several duplicates, you might find it faster to use the Excel Autofilter to delete all the duplicate rows at once.

Use to Excel Autofilter to Delete the Duplicate Rows

The following steps show how to remove several duplicates at once, (after they have been highlighted using the Countif function):

Select the column containing the Countif function (column B in the example spreadsheet);
(Alternatively, if you select any cell within the current data set, Excel will automatically select the entire range of data when you activate the autofilter).
Click on the Filter button in the Data tab of your spreadsheet to apply the Excel Autofilter to your data;
Use the filter at the top of column B to select rows that are not equal to 1.
I.e. click on the filter and, from the list of values, uncheck the value 1;
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.

You can delete these rows by highlighting them, then right clicking with the mouse and selecting Delete Rows.
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.

Return to the Excel Formulas Page
Return to the ExcelFunctions.net Home Page