3 Ways To Delete Duplicates In Excel

Deleting duplicates in Excel can be a daunting task, especially when working with large datasets. However, there are several ways to accomplish this task efficiently. In this article, we will explore three methods to delete duplicates in Excel.

Method 1: Using the Remove Duplicates Feature

The most straightforward way to delete duplicates in Excel is by using the built-in "Remove Duplicates" feature. This feature is available in Excel 2010 and later versions. Here's how to use it:

• Select the range of cells that you want to remove duplicates from.
• Go to the "Data" tab in the ribbon.
• Click on the "Remove Duplicates" button in the "Data Tools" group.
• In the "Remove Duplicates" dialog box, select the columns that you want to check for duplicates.
• Click "OK".

Excel will then remove the duplicate rows from the selected range, leaving only unique rows.

Example:

Suppose you have a list of employees with their names and employee IDs, and you want to remove duplicates based on the employee ID.

Employee Name	Employee ID
John Smith	1234
Jane Doe	5678
John Smith	1234
Bob Johnson	9012

Using the "Remove Duplicates" feature, you can select the entire range (A1:B5) and choose to remove duplicates based on the "Employee ID" column. After clicking "OK", the duplicate row will be removed, leaving only unique rows.

Employee Name	Employee ID
John Smith	1234
Jane Doe	5678
Bob Johnson	9012

Method 2: Using Formulas

Another way to delete duplicates in Excel is by using formulas. This method is useful when you want to remove duplicates based on multiple criteria.

• Select the cell where you want to display the unique values.
• Enter the formula: =UNIQUE(range, [col_num])
• Press Enter.

The UNIQUE function returns an array of unique values from the specified range. You can then use this array to filter out duplicates.

Example:

Suppose you have a list of sales data with dates and product names, and you want to remove duplicates based on both the date and product name.

Date	Product Name
2022-01-01	Product A
2022-01-02	Product B
2022-01-01	Product A
2022-01-03	Product C

Using the UNIQUE function, you can enter the formula: =UNIQUE(A2:B5, 2)

The formula returns an array of unique values based on both the date and product name.

Date	Product Name
2022-01-01	Product A
2022-01-02	Product B
2022-01-03	Product C

Method 3: Using VBA Macro

If you want to automate the process of deleting duplicates in Excel, you can use a VBA macro. This method is useful when you have a large dataset and want to remove duplicates based on multiple criteria.

• Open the Visual Basic Editor (VBE) by pressing Alt + F11 or by navigating to Developer > Visual Basic.
• In the VBE, click Insert > Module to create a new module.
• Paste the following code:

Sub RemoveDuplicates() Dim rng As Range Set rng = Selection rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes End Sub

• Save the module by clicking File > Save.
• Go back to your Excel worksheet and select the range of cells that you want to remove duplicates from.
• Press Alt + F8 to open the Macro dialog box.
• Select the "RemoveDuplicates" macro and click "Run".

The macro will remove duplicates based on the specified columns.

Example:

Suppose you have a list of customer data with names and addresses, and you want to remove duplicates based on both the name and address.

Name	Address
John Smith	123 Main St
Jane Doe	456 Elm St
John Smith	123 Main St
Bob Johnson	789 Oak St

Using the VBA macro, you can select the entire range (A1:B5) and run the macro. The macro will remove duplicates based on both the name and address.

Name	Address
John Smith	123 Main St
Jane Doe	456 Elm St
Bob Johnson	789 Oak St

Delete Duplicates in Excel Image Gallery

We hope this article has helped you learn how to delete duplicates in Excel using three different methods. Whether you use the "Remove Duplicates" feature, formulas, or VBA macros, you can efficiently remove duplicates from your Excel worksheets. Share your thoughts and experiences in the comments below!