5 Ways To Delete Duplicates In Excel

Are you tired of dealing with duplicate data in your Excel spreadsheets? Duplicate entries can lead to inaccurate analysis, incorrect conclusions, and wasted time. Fortunately, there are several ways to delete duplicates in Excel, and we'll cover five effective methods in this article.

Whether you're a beginner or an advanced Excel user, you'll find a technique that suits your needs. So, let's dive in and explore the world of duplicate deletion in Excel.

Why Delete Duplicates in Excel?

Before we dive into the methods, let's quickly discuss why deleting duplicates is important. Duplicate data can lead to:

• Inaccurate analysis and conclusions
• Wasted time and resources
• Difficulty in maintaining data integrity
• Inefficient data processing

By deleting duplicates, you can ensure that your data is accurate, reliable, and efficient.

Method 1: Using the "Remove Duplicates" Feature

Excel provides a built-in feature to remove duplicates. Here's how to use it:

1. Select the range of cells that contains duplicates.
2. Go to the "Data" tab in the ribbon.
3. Click on "Remove Duplicates" in the "Data Tools" group.
4. In the "Remove Duplicates" dialog box, select the columns that you want to check for duplicates.
5. Click "OK" to remove the duplicates.

This method is quick and easy, but it has some limitations. It only works on a single range of cells, and it doesn't allow you to specify which rows to keep or delete.

Method 1.1: Using the "Remove Duplicates" Feature with Multiple Ranges

If you have multiple ranges of cells that contain duplicates, you can use the "Remove Duplicates" feature with multiple ranges. Here's how:

1. Select the first range of cells that contains duplicates.
2. Go to the "Data" tab in the ribbon.
3. Click on "Remove Duplicates" in the "Data Tools" group.
4. In the "Remove Duplicates" dialog box, select the columns that you want to check for duplicates.
5. Click "Add Level" to add another range of cells.
6. Select the second range of cells that contains duplicates.
7. Repeat steps 4-6 until you've added all the ranges.
8. Click "OK" to remove the duplicates.

This method allows you to remove duplicates from multiple ranges of cells, but it can be tedious if you have many ranges.

Method 2: Using Formulas to Delete Duplicates

You can use formulas to delete duplicates in Excel. Here's one way to do it:

1. Create a new column next to the range of cells that contains duplicates.
2. Enter the formula =COUNTIF(A:A, A2)>1 in the new column, assuming the duplicates are in column A.
3. Copy the formula down to the rest of the cells in the new column.
4. Select the entire range of cells, including the new column.
5. Go to the "Data" tab in the ribbon.
6. Click on "Filter" in the "Data Tools" group.
7. Filter the data to show only the rows with a value of "TRUE" in the new column.
8. Select the filtered rows and delete them.

This method allows you to delete duplicates based on a specific column, but it requires creating a new column and using formulas.

Method 2.1: Using Formulas to Delete Duplicates with Multiple Columns

If you want to delete duplicates based on multiple columns, you can modify the formula to use the COUNTIFS function. Here's how:

1. Create a new column next to the range of cells that contains duplicates.
2. Enter the formula =COUNTIFS(A:A, A2, B:B, B2)>1 in the new column, assuming the duplicates are in columns A and B.
3. Copy the formula down to the rest of the cells in the new column.
4. Select the entire range of cells, including the new column.
5. Go to the "Data" tab in the ribbon.
6. Click on "Filter" in the "Data Tools" group.
7. Filter the data to show only the rows with a value of "TRUE" in the new column.
8. Select the filtered rows and delete them.

This method allows you to delete duplicates based on multiple columns, but it requires creating a new column and using formulas.

Method 3: Using Power Query to Delete Duplicates

Power Query is a powerful tool in Excel that allows you to manipulate and transform data. Here's how to use Power Query to delete duplicates:

1. Select the range of cells that contains duplicates.
2. Go to the "Data" tab in the ribbon.
3. Click on "From Table/Range" in the "Get & Transform Data" group.
4. In the "Power Query Editor" window, select the column(s) that you want to check for duplicates.
5. Click on "Remove Duplicates" in the "Home" tab.
6. Click "OK" to remove the duplicates.

This method is quick and easy, and it allows you to delete duplicates based on multiple columns.

Method 3.1: Using Power Query to Delete Duplicates with Multiple Tables

If you have multiple tables that contain duplicates, you can use Power Query to delete duplicates across tables. Here's how:

1. Select the first table that contains duplicates.
2. Go to the "Data" tab in the ribbon.
3. Click on "From Table/Range" in the "Get & Transform Data" group.
4. In the "Power Query Editor" window, select the column(s) that you want to check for duplicates.
5. Click on "Merge Queries" in the "Home" tab.
6. Select the second table that contains duplicates.
7. Repeat steps 4-6 until you've merged all the tables.
8. Click on "Remove Duplicates" in the "Home" tab.
9. Click "OK" to remove the duplicates.

This method allows you to delete duplicates across multiple tables, but it requires using Power Query.

Method 4: Using VBA to Delete Duplicates

You can use VBA (Visual Basic for Applications) to delete duplicates in Excel. Here's an example code:

Sub DeleteDuplicates()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long
    
    Set ws = ActiveSheet
    Set rng = ws.Range("A1:A100")
    
    For i = rng.Rows.Count To 2 Step -1
        If Application.WorksheetFunction.CountIf(rng, rng.Cells(i, 1).Value) > 1 Then
            rng.Rows(i).Delete
        End If
    Next i
End Sub

This code deletes duplicates based on a specific column, but you can modify it to delete duplicates based on multiple columns.

Method 4.1: Using VBA to Delete Duplicates with Multiple Columns

If you want to delete duplicates based on multiple columns, you can modify the VBA code to use the COUNTIFS function. Here's an example code:

Sub DeleteDuplicates()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long
    
    Set ws = ActiveSheet
    Set rng = ws.Range("A1:B100")
    
    For i = rng.Rows.Count To 2 Step -1
        If Application.WorksheetFunction.CountIfs(rng.Columns(1), rng.Cells(i, 1).Value, rng.Columns(2), rng.Cells(i, 2).Value) > 1 Then
            rng.Rows(i).Delete
        End If
    Next i
End Sub

This method allows you to delete duplicates based on multiple columns, but it requires using VBA.

Method 5: Using Add-ins to Delete Duplicates

There are several add-ins available that can help you delete duplicates in Excel. Here are a few examples:

• ASAP Utilities: This add-in provides a range of tools, including a duplicate remover.
• Able2Extract: This add-in allows you to extract data from PDFs and delete duplicates.
• Power Utilities: This add-in provides a range of tools, including a duplicate remover.

These add-ins can be useful if you need to delete duplicates regularly, but they may require a subscription or a one-time payment.

Delete Duplicates Image Gallery

We hope this article has helped you learn how to delete duplicates in Excel. Whether you're using the "Remove Duplicates" feature, formulas, Power Query, VBA, or add-ins, there's a method that's right for you.