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.
To quickly remove duplicate rows in your worksheet, perform the following steps:
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.
If you are not sure you need to eliminate duplicate rows but want to see them at a glance, get the repeated lines selected:
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.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
