How to remove duplicate rows in Excel

If different users often modify your Excel worksheets or you combine them from multiple resources, you probably face the necessity to remove duplicate rows quite often. Deleting repeated lines manually may consume hours if your spreadsheet has dozens of rows. Or maybe you are not sure that the repeated rows must be eliminated and want to find and select them?

In this tutorial, we show how to get rid of duplicate rows in Excel or find and select them using just one tool - Duplicate Remover Wizard.

If you are interested removing duplicates by key columns, please follow the above link to see how.

How to delete duplicate lines

Remove duplicate rows in Excel.
To quickly remove duplicate rows in your worksheet, perform the following steps:

  1. On the Ablebits Data tab, in the Dedupe group, click the tiny down arrow below the Duplicate Remover icon.
  2. In the drop-down list, hit Duplicates Only.
  3. To get all the columns selected, tick the box near the Columns word.
  4. Pick the Delete values option and click Finish.

Video: How to get rid of repeated lines in Excel

Video transcript

If your task is to delete duplicate rows from your Excel table, the Ablebits Duplicate Remover tool will help you out. Let's have a look at the way we can do it.

In the Excel ribbon, let's find the Ablebits Data tab and the Dedupe group. Click on the Duplicate Remover icon.

Now we are to choose the search range. The add-in will automatically pick the range that you select in your worksheet.

Dealing with duplicates removal in Excel, it is better to have a backup copy of the original dataset then to restore the mistakenly deleted entries. If the Backup this worksheet box is checked, the tool will create a backup copy of your sheet right in this workbook.

On the second step, we shall select the type of data to look for. As we are deleting duplicate lines, we pick the Duplicates option.

The wizard wants us to choose the key columns. As our task is to remove the repeated rows, we shall select all the three columns of the table. If your table has more columns, you can get all of them selected by clicking the Select All button. The utility will delete the row only if it finds duplicate values in all the columns.

We leave the My table has 1 header row box checked since the table has a header row. If needed, you can enter any number of header rows by clicking the 1 header row phrase.

On the final step, we tick Delete and hit the Finish button.

Now the table has no repeats, only unique rows are left. A new sheet in the workbook has been created, this is a backup copy of the original dataset.

How to find and select duplicate rows

Find and select duplicate rows in Excel.
If you are not sure you need to eliminate duplicate rows but want to see them at a glance, get the repeated lines selected:

  1. On the Ablebits Data tab, find the Dedupe group and the Duplicate Remover icon. Click the small down arrow below the icon.
  2. Select the Duplicates Only option.
  3. As you are searching for duplicate rows, check all the columns as the key ones. To do it qucikly, click the Select All button.
  4. Choose the Select values Delete values option and click Finish.

Video: How to find duplicate lines in Excel

Video transcript

In this tutorial, I will show how to find and select duplicate lines with the help of the Ablebits Duplicate Remover add-in.

I go to the Ablebits Data tab, find the Duplicate Remover icon and click the down arrow below.

I choose the Duplicates Only option. This is the short way that saves our time and brings us to the third step of the wizard. The tool selects the table automatically, but we can change the range right in the sheet.

I leave this box checked since my table has 1 header row. If you have more header rows, feel free to click on the 1 header row phrase and enter the needed number.

Now let's select the columns that contain duplicates. As I want to find the repeated rows only, I check all the three columns, you may have more of course. The utility will select the row only if it finds duplicate values in all the columns. I click the Next button.

On the final step, we simply pick the Select values option and click Finish.

All the repeated lines in my table have been selected by Duplicate Remover wizard.

Responses

chad says:
June 16, 2023 at 8:17 pm

I have a sheet with over 9000 rows. When I try to find duplicates the tool keeps timing out. How can I stop this

Maryia Vehera (Ablebits Team) says:
June 16, 2023 at 9:59 pm

Hello,

Thank you for the question. For us to be able to understand the problem better, we need to know more details. Could you please send us to support@ablebits.com a sample workbook with your data and a screenshot of the add-in window with the selected options? We'll try to reproduce the issue on our end and find its cause.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.