How to remove duplicate cells in an Excel sheet

We receive a collection of data, and at a certain point we realize that some of it is duplicated.

We must analyze the data, knowing that duplications are errors.

In this article, we are going to see three ways to eliminate duplicate cells.

Remove duplicate cells in Excel

For each of the methods described below, we use the simple spreadsheet below, 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 accomplish this task. Finally, we show how to remove duplicates using the function Countif of Excel .

Remove duplicates using Excel's Remove Duplicates command

The command Remove duplicates it is found in the “Data Tools” group, inside the tab Data of the Excel ribbon.

To remove duplicate cells using this command:

• Select any cell within the dataset you want to remove duplicates from and click the button Remove duplicates.
• You will be presented with the “Remove Duplicates” dialog shown below:

• This dialog allows you to select which columns in your dataset 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 in the dialog box.
• After making sure the required fields are selected in the dialog box, click OK. Excel will then delete the duplicate rows, as required, and present you with a message, informing you of the number of records removed and the number of unique records remaining (see below).

• Above the message there is also the table resulting from the deletion. As requested, duplicate cell A11 (containing the second occurrence of the name “Dan BROWN”) has been removed.

Note that Excel's Remove Duplicates command can also be used on datasets with multiple columns. An example of this is provided on the Remove Duplicate Rows page.

Remove duplicates using Excel's advanced filter

Excel's Advanced Filter has an option that lets you filter unique records in a spreadsheet and copy the resulting filtered list to a new location.

This provides a list that contains the first occurrence of a duplicate record, but contains no further occurrences.

To remove duplicates using the advanced filter:

• Select the column or columns to filter (column A in the example spreadsheet above);(Alternatively, if you select any cell within the current dataset, Excel will automatically select the entire data range when you enable 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 found in the menu Data → Filter ).

• You will be presented with a dialog box showing options for Excel's advanced filter (see below). Inside this dialog box:

The resulting spreadsheet, with the new list of data in column C, is shown above.

You may notice that the duplicate value “Dan BROWN” has been removed from the list.

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

Remove duplicates using Excel's Countif function

This method will only work if the cell contents are less than 256 characters in length, as Excel functions cannot handle longer text strings.

Step 1: Highlight duplicates

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

To illustrate this, we will once again use the simple example spreadsheet, which has a list of names in column A.

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

As shown in the spreadsheet formula bar above, the format of the function Countif in cell B2 it is :=COUNTIF( $A$2:$A$11, A2 )

Please note that this feature uses a combination of absolute and relative cell references. Because of this combination of reference styles, when the formula is copied into column B, it becomes,

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

Therefore, the formula in cell B4 returns the value 1 for the first occurrence of the text string “Laura BROWN,” but the formula in cell B7 returns the value 1 for the second occurrence of this text string.

Step 2: Delete duplicate rows

Now that we have used the Excel function Countif To highlight 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 delete the single duplicate row. However, if you have multiple duplicates, you may find it quicker to use Excel's automatic filter to delete all duplicate rows at once. Use Excel's automatic filter to eliminate duplicate rows

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

• Select the column containing the function Countif (column B in the example spreadsheet);
• Click on the button Filter in the tab Data of the spreadsheet to apply the Excel automatic filter to your data;

• Use the filter at the top of column B to select rows that are not equal to 1. That is, click on the filter and, from the list of values, deselect the value 1;
• You will be left with a spreadsheet where the first occurrence of each value is hidden. That is, only duplicate values ​​are displayed. You can delete these lines by highlighting them, then right-clicking and selecting Delete lines .

• Remove the filter and you will end up with the spreadsheet, where the duplicates have been removed. Now you can delete the column containing the function Countif to return to the original spreadsheet format.

Ercole Palmeri