How to Remove Duplicate Rows in Excel: 3 Easy Ways

Duplicate rows often appear when you copy data from different worksheets, import reports from external systems, or combine multiple lists in Excel. If they are not removed, these repeated records may affect totals, PivotTables, reports, and data analysis results.

In this tutorial, you will learn 3 practical ways to remove duplicate rows in Excel: using Excel’s built-in Remove Duplicates feature, using Kutools for Excel to select and review duplicate rows before deleting them, and using VBA to automate the task.

• Why remove duplicate rows in Excel
• Method 1: Remove duplicate rows with Excel Remove Duplicates
• Method 2: Select, highlight, and remove duplicate rows with Kutools 👍(Safe & Visual)
• Method 3: Remove duplicate rows with VBA
• Which method should you use
• Frequently asked questions

Why remove duplicate rows in Excel

A duplicate row means that the same record appears more than once in your worksheet. For example, the same order, customer, product, and amount may be repeated in a sales list.

In the example above, the records for Order ID 1001 and Order ID 1002 appear twice. Removing duplicate rows helps keep only one clean copy of each record.

Method 1: Remove duplicate rows with Excel Remove Duplicates

Excel has a built-in Remove Duplicates feature that can quickly delete repeated rows from a selected range. This method is best when you want to remove duplicates directly and keep the first occurrence of each record.

1. Select the data range that contains duplicate rows.
2. Go to the Data tab, and click Remove Duplicates in the Data Tools group.
   
3. In the Remove Duplicates dialog box, set the options as follows:
   1. ① Check My data has headers if your selected range includes headers.
   2. ② Select the columns used to identify duplicates.
      • If you select all columns, Excel removes only completely identical rows.
      • If you select only one or some columns, Excel treats rows with the same values in those selected columns as duplicates.
      In this example, we select all columns to remove rows only when the entire row is duplicated.
   3. ③ Click OK.
      
4. Excel will show a message box with the number of duplicate values removed and unique values remaining. Click OK to close it.
   

Result: Excel removes the duplicate rows from the selected range and keeps the first occurrence of each duplicate record.

Method 2: Select, highlight, and remove duplicate rows with Kutools

If you want to check duplicate rows before deleting them, Kutools for Excel provides an easier and safer way. With the Select Duplicate & Unique Cells feature, you can quickly select duplicate rows first, highlight them if needed, review them, and then delete them from the worksheet. This method is especially useful when you want to keep the first occurrence and remove only the extra duplicate rows.

1. Select the range where you want to find and remove duplicate rows.
2. Click Kutools > Select > Select Duplicate & Unique Cells.
   
3. In the Select Duplicate & Unique Cells dialog box, do the following:
   1. ① Choose Each row in the Based on section.
   2. ② Under Rule, select Duplicates (Except 1st one).
      • Duplicates (Except 1st one): selects only the extra duplicate rows and keeps one copy.
      • All duplicates (Including 1st one): selects all rows in each duplicate group. If you delete them, all duplicate records in that group will be removed.
   3. ③ Optional: check Fill backcolor or Fill font color if you want to highlight the selected duplicate rows before deleting them.
   4. ④ Click OK.
      
4. A message box appears and tells you how many rows have been selected. Click OK.
   
5. Right-click one of the selected row numbers and choose Delete.

Result: The duplicate rows except the first occurrence are selected and deleted. One copy of each repeated record remains in the worksheet. If you applied a fill color or font color, the duplicate rows are highlighted before deletion, making them easier to review.

Method 3: Remove duplicate rows with VBA

This VBA code checks duplicate rows based on all columns in the selected range. It keeps the first occurrence and deletes the later duplicate rows.

1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module.
3. Copy and paste the VBA code into the module window.

    Sub RemoveDuplicateRows_KeepFirst()
   'Updated by Extendoffice 2026/5/22
       Dim rng As Range
       Dim hasHeaders As VbMsgBoxResult
       Dim firstDataRow As Long
       Dim r As Long, c As Long
       Dim key As String
       Dim seen As Collection
       Dim rowsToDelete As Range
       Dim cellValue As Variant
       
       On Error Resume Next
       Set rng = Application.InputBox("Select the range to remove duplicate rows:", "Remove Duplicate Rows", Type:=8)
       On Error GoTo 0
       
       If rng Is Nothing Then Exit Sub
       
       hasHeaders = MsgBox("Does the selected range include headers?", vbYesNoCancel + vbQuestion, "Header Option")
       If hasHeaders = vbCancel Then Exit Sub
       
       If hasHeaders = vbYes Then
           firstDataRow = 2
       Else
           firstDataRow = 1
       End If
       
       Set seen = New Collection
       
       Application.ScreenUpdating = False
       
       For r = firstDataRow To rng.Rows.Count
           key = ""
           
           For c = 1 To rng.Columns.Count
               cellValue = rng.Cells(r, c).Value
               
               If IsError(cellValue) Then
                   key = key & "[ERROR]" & CStr(rng.Cells(r, c).Text) & Chr(30)
               Else
                   key = key & CStr(cellValue) & Chr(30)
               End If
           Next c
           
           On Error Resume Next
           seen.Add key, key
           
           If Err.Number <> 0 Then
               If rowsToDelete Is Nothing Then
                   Set rowsToDelete = rng.Rows(r).EntireRow
               Else
                   Set rowsToDelete = Union(rowsToDelete, rng.Rows(r).EntireRow)
               End If
               Err.Clear
           End If
           
           On Error GoTo 0
       Next r
       
       If Not rowsToDelete Is Nothing Then
           rowsToDelete.Delete
           MsgBox "Duplicate rows have been removed.", vbInformation
       Else
           MsgBox "No duplicate rows were found.", vbInformation
       End If
       
       Application.ScreenUpdating = True
   End Sub
   

4. Press F5 or click the Run button in the VBA Editor to run the code.
5. In the pop-up box, select the range where you want to remove duplicate rows. Click OK.
   
6. When asked whether the selected range includes headers, choose Yes or No.
   

Result: Duplicate rows in the selected range are removed, and the first occurrence is kept.

Which method should you use

The best method depends on whether you want a quick built-in solution, a safer visual way to review duplicates first, or a code-based method.

For quick cleanup, Excel’s built-in Remove Duplicates feature works well. If you want to select, highlight, and review duplicate rows before deleting them, Kutools for Excel is the safer and more visual choice. VBA is useful when you prefer a code-based solution.

Frequently asked questions

How do I remove duplicate rows but keep the first one in Excel?

You can use Excel’s built-in Remove Duplicates feature. It removes repeated records and keeps the first occurrence. You can also use Kutools for Excel and choose Duplicates (Except 1st one) to select only the duplicate rows after the first occurrence.

How do I remove duplicate rows based on one column?

Select your data range, go to Data > Remove Duplicates, and select only the column you want to use for duplicate detection. Excel will treat rows with the same value in that column as duplicates and keep the first occurrence.

How can I find duplicate rows without deleting them?

Use Kutools for Excel’s Select Duplicate & Unique Cells feature. It allows you to select duplicate rows first. You can then review, highlight, copy, or delete them later.

What is the difference between Duplicates (Except 1st one) and All duplicates (Including 1st one)?

Duplicates (Except 1st one) selects only the repeated occurrences after the first record. This is useful when you want to keep one copy and remove the extra duplicates.

All duplicates (Including 1st one) selects every row in each duplicate group, including the first occurrence. This is useful when you want to identify all records that belong to duplicate groups.

Can I highlight duplicate rows before deleting them?

Yes. With Kutools for Excel, you can select duplicate rows and apply fill color or font color at the same time. This makes it easier to review duplicate records before deleting them.

Can Excel remove case-sensitive duplicate rows?

Excel’s built-in Remove Duplicates feature is not ideal for case-sensitive duplicate detection. If you need to distinguish between uppercase and lowercase text, you can use the Case sensitive option in Kutools for Excel’s Select Duplicate & Unique Cells feature.

Conclusion

Removing duplicate rows is an important step in cleaning Excel data. For a quick cleanup, Excel’s built-in Remove Duplicates feature works well. For a safer process, Kutools for Excel lets you select and review duplicate rows before deleting them. For repeated cleanup tasks, VBA can help automate the process.

If you often work with large datasets or need to check duplicates carefully before deleting them, the Kutools method can save time and reduce the risk of deleting important records by mistake.