How to remove duplicate rows in excel

Answer

To remove duplicate rows in Excel, use the Remove Duplicates command or formulas with helper columns. Each method deletes all but the first occurrence of duplicate rows, preserving the remaining data in place. These approaches are accessible via the Data tab and suit data cleaning in clinical trials or statistical workflows.

Remove duplicates command

The Remove Duplicates command is the fastest built-in method for deleting duplicate rows in Excel.

1. Select the data range containing duplicates.
2. Go to Data > Data Tools > Remove Duplicates.
3. Choose the columns Excel should use for duplicate detection.
4. Click OK to execute the removal.

Excel identifies rows matching across selected columns and deletes extras, keeping the top instance. A confirmation dialog displays the count of removed rows and unique values remaining.

Formulas with helper columns

Formulas provide visibility into duplicates before permanent deletion.

1. Create a helper column concatenating key fields using a formula such as =A2&"|"&B2&"|"&C2.
2. Use COUNTIF to identify duplicates in the helper column: =COUNTIF($D$2:D2,D2)>1.
3. Filter the helper column for TRUE values.
4. Select all visible rows except headers.
5. Right-click and select Delete Row.
6. Clear the helper column after completion.

This method allows review of which rows will be deleted before taking permanent action.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report