How to delete duplicates in Excel

If you enter records into a worksheet several times or combine them from multiple sources, some entries may get duplicated. Repeated values can infiltrate into only one column, several columns, or into all columns of your table. In such cases, you may want to get rid of dupes to get a clean dataset. Removing duplicate data may be irreversible, so it is always better to have a backup of your original worksheet.

In this tutorial, we'll discuss how to quickly remove duplicates or move them to another location with the help of the Duplicate Remover tool.

If your task is to delete duplicate lines that have the same values in all the columns, please follow the above link for the detailed guidance.

How to remove duplicates in Excel

  1. Start Duplicate Remover by clicking its icon on the Ablebits Data tab.
  2. Select the range where you want to remove duplicate entries.
  3. Tick the Duplicates option.
  4. To find and remove duplicates by key columns, leave only columns of interest checked and uncheck the other ones.
  5. Choose the Delete values option. Click Finish.

Delete duplicates by key columns.

Video: Removing duplicates by key columns

Video transcript

In this quick tutorial, I will show how to remove duplicates in an Excel sheet using the Ablebits Duplicate Remover add-in.

Here is our table. There are three columns, but now only the first column makes a difference - entries in the second and the third ones are not important. My task is to remove the rows that contain repeated values in the first column.

Let's start Duplicate Remover by clicking its icon on the Ablebits Data tab.

On the first step, the tool selects your table automatically, but you can change the search range if you select it right in your sheet.

By the way, please do not ignore this box. If it is checked, a safe backup copy of your sheet will be created. Excel doesn't let undo changes made by the add-in, so it is better to keep this box checked.

Here we select the type of data we need to find. As we are looking for duplicate values, we tick the first option.

Now we are to choose the key columns. As our task is to get a clear table without repeats in the first column, we choose it as the only key column and uncheck the other ones. This means that the add-in will not look at the values in the second and third columns - only at the first one.

By the way, by ticking this box you can exclude the header row from the search. Click the 1 header row phrase and enter the number of headers your table has. And click Next.

On the final step, let's choose the action. A backup of this sheet will be created, so I pick Delete values and click Finish without hesitations.

As a result, I get a clean list without duplicates.

How to move duplicates to another worksheet

  1. First of all, start Duplicate Remover by clicking the small down arrow below the tool's icon.
  2. Click the Duplicates Only option.
  3. Select the columns to look for duplicate values. To get all the columns checked at once, click the Select All button.
  4. Choose the Move to another location option, select the place for the moved repeats, and click Finish.

Video: Moving duplicates to a new sheet

Video transcript

What if you need to remove duplicates from your worksheet, but preserve them in another location? In such a case, take advantage of the Ablebits Duplicate Remover tool.

We have a three-column table where some entries are repeated. I want to get rid of the repeats, but to have them in this book in another worksheet.

Let's find the Ablebits Data tab and the Duplicate Remover tool. I click the small down arrow below the tool's icon and choose the Duplicates Only option.

I want to move the rows that have repeats in Column 1 and Column 2. Column 3 should have no impact on the result, so I uncheck it.

I keep this box checked as my table has 1 header row. If you have more header rows, enter the number of interest right here.

On the fourth step, I tick the Move to another location option. The tool immediately offers me to choose where I want to move dupes, and I select a new worksheet. I click Finish.

Thus, I get a clear list without duplicates in the first and second columns plus the removed duplicates collected in a separate worksheet.