How to delete duplicates in excel

Answer

To delete duplicates in Excel, you can use the built-in Remove Duplicates feature or formulas to identify duplicates before deletion.

Using the Remove Duplicates Feature

1. Select the range of cells or the entire table where you want to remove duplicates.

2. Go to the Data tab on the ribbon.

3. Click on Remove Duplicates.

4. In the pop-up window:

   • Check the columns where duplicates should be identified.

   • Uncheck columns that should be ignored.

5. Click OK.

6. Excel will show a summary indicating how many duplicate values were removed and how many unique values remain.

Example:
A table contains names and email addresses. Checking both columns will remove rows where both name and email match another row. Checking only the email column removes rows with duplicate emails even if names differ.

Using Conditional Formatting to Identify Duplicates

1. Select the range where duplicates need to be highlighted.

2. Go to the Home tab.

3. Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.

4. Choose a formatting style to highlight duplicates.

5. Review highlighted cells and delete them manually if needed.

Using a Formula to Identify Duplicates

1. Select a new column next to your data.

2. Enter the formula: 

   =IF(COUNTIF($A$1:$A1, A1)>1, "Duplicate", "Unique")

3. Drag the formula down the column.

4. Filter the "Duplicate" values and delete those rows.

Notes:

• The Remove Duplicates feature permanently deletes rows. Consider creating a backup.

• Conditional Formatting helps to review duplicates before deleting.

• Formulas allow more flexibility for complex data structures.

This method works the same in both Windows and Mac versions of Excel, but the location of tabs may vary slightly.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report