What is the excel formula to remove duplicate rows

Answer

Excel does not have a single formula that automatically deletes duplicate rows. Removing duplicates is usually done using built-in features or helper formulas to identify duplicates first.

Method 1: Using the Built-In “Remove Duplicates” Feature

1. Select the range of cells or the entire table where duplicates might exist.

2. Go to the Data tab on the Ribbon.

3. Click Remove Duplicates.

4. In the dialog box, choose the columns that should be checked for duplicates.

5. Click OK.

6. Excel will display a message showing how many duplicate rows were removed and how many unique rows remain.

Note: This method permanently deletes duplicate rows. Keep a backup if needed.

Method 2: Using a Formula to Identify Duplicates

You can use a formula to mark duplicates, then filter or delete them manually.

1. Suppose your data is in columns A to C, starting from row 2.

2. In a new column (e.g., column D), enter the following formula in D2: 

   =IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)>1,"Duplicate","Unique")

3. Drag the formula down to the last row.

4. Filter column D for Duplicate and delete those rows.

Explanation:

• COUNTIFS counts how many times the combination of values in columns A, B, and C has appeared up to the current row.

• If the count is greater than 1, the row is a duplicate.

Method 3: Using Power Query

1. Select your table and go to Data → Get & Transform → From Table/Range.

2. In the Power Query editor, select the columns to check for duplicates.

3. Click Remove Rows → Remove Duplicates.

4. Close and load the cleaned table back into Excel.

Power Query is efficient for large datasets and prevents accidental deletion of data.

Notes:

• Removing duplicates only considers exact matches. Minor differences in text or spacing will not be detected as duplicates.

• Formulas like COUNTIFS work for both numeric and text data.

• The built-in Remove Duplicates tool is faster for small datasets, while formulas or Power Query provide better control for larger or dynamic datasets.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report