What is the formula for removing duplicates in excel

Answer

Excel does not have a single formula that directly removes duplicates in-place. However, you can use formulas to identify duplicates and then remove them using filters or additional steps.

Method 1: Using the UNIQUE function

A formula can extract unique values from a range. The syntax is: 

=UNIQUE(array)

• array is the range of cells you want to check for duplicates.

• The result is a new list with only the unique values from the original range.

Example:

If column A has the values:

A1: Apple

A2: Orange

A3: Apple

A4: Banana

Enter the formula in another column: 

=UNIQUE(A1:A4)

The output will be:

Apple

Orange

Banana

This formula works on both Windows and Mac.

Method 2: Using COUNTIF to identify duplicates

You can mark duplicates with a formula, then filter them out. 

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

• $A$1:$A$100 is the range to check.

• A1 is the current cell.

• "Duplicate" marks repeated entries, "Unique" marks first occurrences.

Example:

For values in A1:A5:

Apple

Banana

Apple

Orange

Banana

The formula in B1:B5 will output:

Duplicate

Duplicate

Duplicate

Unique

Duplicate

You can filter the "Duplicate" rows and delete them manually.

Method 3: Remove Duplicates Feature

This is not a formula but an Excel built-in feature:

1. Select the range.

2. Go to Data → Remove Duplicates.

3. Select the columns to check for duplicates.

4. Click OK.

Excel will delete all repeated entries, keeping only the first instance.

Summary

• Use UNIQUE to generate a new list with only unique values.

• Use COUNTIF to flag duplicates before removing them manually.

• Use the built-in Remove Duplicates feature for immediate removal.

This ensures accuracy and flexibility depending on whether you want to keep the original data or replace it with unique values.

If you want, I can create a formula that automatically removes duplicates in-place without using the built-in tool, which is a bit more advanced but fully automated. Do you want me to do that?

Yes · 0 No · 0 Add Comment · 0

Add ·

Report