A short video tutorial: how to remove duplicates in one sheet

Detailed steps to find duplicates or uniques in one sheet

Open Find duplicates or uniques from the menu

Open the Add-ons menu, find the Remove Duplicates add-on and click on the "Find duplicates or uniques" tool to open it.

Step 1: Select your data

First select the sheet and range with the data you want to search in.

Note: If you want to delete duplicates, it's best to select the whole table so that the add-on removes the entire rows and you can avoid data corruption.

You can select any of the open sheets in the 'Select the sheet' drop-down list.

Please select the sheet that contains duplicates

'Select the range with your table' shows the current highlighted range in the selected sheet. You can alter it by changing the selection or by entering the range address in this field.

Use the Auto select button to automatically pick the range with all data you have.

You can choose to back up the original data by selecting the check box 'Create a backup copy of the sheet'

Click Next to proceed to the second step, or cancel the process by clicking Cancel

Step 2: Choose what to find

The add-on lets you check your table for 4 types of data:

Choose Duplicates to find replicated data

If you want to get rid of any repeating values in your table, pick the 'Duplicates' option. The add-on will keep unique values and the first entries of any duplicates you have. Your table will become clear of any extra replicated data.

Duplicates and their first occurrences

To see the original records as well as their copies, pick 'Duplicates +1st occurrences' on this step.

Unique values

Choose the 'Uniques' option to find only those records that don't have any duplicates in the sheet.

Unique vales and the first occurrences of the duplicates

This option lets you find everything except for the duplicates. The add-on will look for the first entries of duplicated data in addition to unique values.

You can return to step 1 by clicking Back; click Next to go on to the next step, or Cancel to call off the process.

Step 3: Select the columns to check

You will see a list of columns in your table. Please select the check boxes next to the key columns you want to check for duplicates.

Tip: If you select more than one column, a row will be considered duplicate if the values in all the selected columns are the same.

If you have header rows, make sure you tick off the check box 'My Table has headers' to see your column titles. If you don't have headers, unselect this box to display column names. You can use the '1st row content' field to see what kind of data you have in each column.

Choose columns you want to search

If you want to look for complete row duplicates, select all columns by using the top check box in the header row.

You can tick off the 'Skip empty cells' option to ignore duplicated blanks in the selected columns. If this check box is not selected, blank matches will be considered duplicates.

Use the 'Case sensitive' option to consider those values that differ only in case as unique values.

Note: Leading and trailing spaces in the cell values are ignored by default.

Click Back to return to the previous step, click Next to proceed to the last step, or cancel the process by clicking Cancel.

Step 4: Choose action for the found values

The add-on allows you to choose one of six ways to deal with the found values. Select the radio button next to the action you'd like to apply:

Color, copy, or remove the found duplicates from your sheet
  • Highlight the rows with the found values by picking the 'Fill with color' option. Click on the arrow next to this option to pick the color you'd like to use.
  • Choose to 'Add a status column' to mark duplicate rows in a separate column you can use for sorting data
  • 'Copy to another location' option allows you to review search results in another spreadsheet
  • Select another sheet for the found rows with the 'Move to another location' option
  • Choose to 'Clear values' to remove the found values in the selected key columns and leave all other data intact.
  • You can also remove all lines with the found values using the 'Delete rows' option.
Click Back to change the previous settings, cancel the process, or click Finish to find duplicate or unique values.

See the results

Once the add-on completes the search, you will see the summary of the results with the number of found values and the action that was applied.

See the summary with the number of found values

If you'd like to run the add-on again, click on the Start over button. Otherwise click Close.

See also:

Contact us

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Ultimate Suite 2018.3 Summer Offer
Excel add-ins and Outlook tools - Ablebits.com