How to delete duplicates in Excel

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.

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.