How to Remove Duplicates in Excel: 5 Best Methods

Ever wonder how to remove duplicates in Excel?

Either you want to remove the entire duplicate row or, on the other hand, specific columns like names or IDs; however, you want to keep one.

You can remove duplicates manually, but it takes a lot of time, and you will still not get an accurate result.

This guide will demonstrate, in addition, the 5 best methods for cleaning up identical values quickly and, furthermore, as per your expectations.

In this guide, we will see how to purge duplicates using, first of all, Excel’s built-in remove duplicates tool. Additionally, we will explore the COUNTIF Function. Furthermore, we will examine the Advanced Filter. Moreover, we will also look into the highlight duplicate values tool and, finally, the VBA Macro.

I have explained all these methods step by step so that you can understand them quickly, without any doubt.

How to Remove Duplicates in Excel

So, let’s understand how to remove redundancies from rows and columns in a Microsoft Excel sheet. To begin with, we will explore the 5 most popular methods.

Method 1: Use the Remove Duplicates Built-in Tool

The Remove Duplicates feature is, indeed, the easiest way to remove twins in Microsoft Excel. Specifically, you can use this feature to remove duplicates from column data or, alternatively, from rows.

First of all, let us see how to remove duplicate cells from a column but keep one.

1. Remove duplicate values from a column

I have a column with a list of customer IDs with a few duplicate values; therefore, I want to keep unique IDs and, consequently, remove photocopies.

Before removing duplicate values from the column:

Here is how I can do it.

• I am selecting column A.
• Navigating to the Data tab > Data Tools
• Clicking on the Remove Duplicates icon.

It will show me the Remove Duplicates dialog box.

• Now, I can see that the Customer ID and the My Data has headers checkboxes are already selected for me. However, you need to select them if they are not already selected.
• And clicking on the OK button.

It has removed duplicate values, but kept the first occurrence from column A. You can see it in the Image.

If you have selected multiple columns and want to delete identical values from any specific column, then you can select that specific column only in the remove duplicates dialog box.

After removing duplicate values from the column

Now let’s see how to remove duplicate rows while retaining the first instance.

2. Remove identical rows in Excel

I have an Excel sheet with duplicate rows.

Before removing duplicate rows

To clean up duplicate rows and keep the first instance in Excel:

• Select all rows.
• And, consequently, follow the same steps that I followed earlier to delete duplicate values from the column.

You can use this method to remove duplicates based on two or more columns. It will delete the entire duplicate row based on one column.

Shortcut: If you are looking for the remove duplicates shortcut, you can press Alt + A + M in Windows to open the Remove Duplicates tool quickly.

Do you want to join columns or split text before removing duplicates? You can read this on how to combine two columns in Excel and how to split text into columns in Excel.

Now, let us see how to delete duplicates in Excel using a formula.

Method 2: Use COUNTIF Function to Delete Duplicates

You can use this method to keep the original data intact. This method will identify and filter duplicate entries instead of deleting them directly.

I have an order detail data with Order ID (Column A), Product (Column B), and Quantity (Column C) columns. However, I want to remove duplicate values from the product (Column B) only. Therefore, I will proceed to identify the duplicates. Subsequently, I will remove these duplicates to ensure that each product is listed only once. Ultimately, this will provide a clearer overview of the products in the order detail data.

To remove duplicate values from column B:

• Insert a new column D next to my dataset (e.g., “Duplicate Count”).
• Enter the Formula given below in the D2 cell.

=COUNTIF(B:B, B2)

This formula will check how many times a value appears in column B.

If you find any row with a value greater than 1 in the “Duplicate Count” column, it has a duplicate value.

Now you can remove those duplicates manually or by filtering datasets (Data tab → Filter).

Method 3: Use Advanced Filter

This is a great alternative to filter unique values through the advanced filter in Excel.

Let’s say I have a list of customers’ details with duplicate rows. I want to keep the first entry so that I can prepare a list with unique Customer IDs.

To delete duplicates from the spreadsheet:

• First, select all rows.
• Next, navigate to Data > Sort & Filter
• Finally, click on Advanced.

In the Advanced Filter dialog box, choose:

• Copy to another location (if you want to keep the original list intact and get a filtered version elsewhere).
• Set Copy to = Sheet1!$G$2; specifically, Sheet1 is the sheet name, and furthermore, $G$2 refers to the G2 cell.
• Now, select the Unique records only checkbox.
• Then, click on the OK button.

That’s it. It will populate the new list with unique customer IDs.

Method 4: Use the Conditional Formatting

Conditional formatting is the best option if you want to identify duplicates in Excel without deleting them.

You can, indeed, highlight duplicate values using the conditional formatting feature of Excel; furthermore, this allows for better data visualization and analysis.. Once duplicates are highlighted, you can delete them manually from the spreadsheet if you want.

For example, I have a customer detail sheet with a duplicate customer name in column B.

To highlight duplicate customer names:

• Select the customer name column in the spreadsheet.
• Then navigate to Home tab > Styles tool in the ribbon.
• Click on Conditional Formatting > Highlight Cells Rules > Duplicate Values.

• The Duplicate Values window will be populated.

• Keep the default selected value “Light Red Fill with Dark Red Text” selected in the “Values with” drop-down and click on the OK button.

Now duplicate values are highlighted. You can remove them without shifting cells as per your requirement.

This is the best method to find duplicates before deleting them.

Tip: If you are looking to remove duplicate date-time, but they are in separate columns, then first of all, you should merge them into one column. Don’t know how to do it? Learn here how to combine date and time in Excel.

Method 5: Remove duplicates using VBA Macro

If you don’t want to perform manual actions or write formulas, you can automate removing duplicates using a VBA Macro.

This guide will explain how to delete duplicate entries from an Excel sheet table using VBA with a practical example.

Steps to remove duplicate values using a Macro

Note: You can not UNDO once you run the macro. So don’t forget to take data backup before using the macro to delete duplicates.

Suppose you have two columns (Customer ID and Customer Name) with duplicate entries. See the given table.

Spreadsheet data before removing duplicates

I want to eliminate duplicate values from the Excel spreadsheet using a Macro.

Here are the steps:

• First, press the shortcut Ctrl + F11; then, it will open the “Microsoft Visual Basic for Applications” window.
• Now, click on the Insert menu and select the Module submenu (or press Alt + I + M) in the “Microsoft Visual Basic for Applications” window. Consequently, it will open the VBA Code Editor.

• Copy-paste the given code below into the code editor.

VBA code to remove duplicates in Excel

Sub RemoveDuplicates()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim dataRange As Range
    
    ' Set the active worksheet
    Set ws = ActiveSheet

    ' Find the last used row and column
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

    ' Define the range dynamically
    Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

    ' Remove duplicates
    dataRange.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

• Close the Code Editor and the “Microsoft Visual Basic for Applications” window.
• Press shortcut Alt + F8. It will open the Run Macro dialog box as shown in the screenshot below.

When you run the macro, it will find the last used column and row to define a range.

Then it will use Range.RemoveDuplicates method to eliminate all identical values from the active sheet.

Spreadsheet data after removing duplicates using a macro

Troubleshooting Common Issues When Removing Duplicates in Excel

I think it is easy to delete identical values or rows in Excel after learning the 5 best methods. But sometimes, we face problems while cleaning up identical data from an Excel sheet.

I have faced a few common problems while removing duplicates and identified solutions for the same. Let me list them out here so you do not need to face the same issue.

Issue 1: Duplicates Not Being Removed

Possible Causes: This issue arises when there is a hidden space or a formatting difference. Also, it can be due to the case sensitivity in text data.

Fix: You can use the =TRIM(A1) function to remove extra space. Additionally, you can use the =CLEAN(A1) function to clean unwanted characters. Furthermore, the =EXACT(A1, B1) function can be utilized for case-sensitive duplicates.

Issue 2: Unexpected Data Loss

Possible causes: This happens when you select the wrong column or datasets.

Fix: Double-check you have selected the right column before proceeding to remove duplicate data. For best practice, always create a backup before running the Remove Duplicates tool.

Issue 3: Remove Duplicates Button Greyed Out

Possible Causes: This problem arises when the Excel sheet is in protected mode or data is in a table format.

Fix:

• If your sheet is in protected mode, then you can remove it from Review > Unprotect Sheet.
• If the data is in table format, then you need to convert it to a range from Table Design > Convert to Range.

Issue 4. Remove Duplicates Only Removes Exact Matches

Excel will remove only exact duplicate rows. However, if you want to remove partial matching rows, you can use conditional formatting from Home > Conditional Formatting > Highlight Duplicate Values.

Download Free Excel Practice Sheet to Remove Duplicates

Want to master the different ways to remove duplicates in Excel?
Download this free Excel practice sheet with sample data and try five different methods:

• Remove Duplicates Built-in Tool
• COUNTIF Function
• Advanced Filter
• Conditional Formatting
• VBA Macro for automation

Download the Excel file here and practice step by step!

Wrapping Up: Clean Your Data with Ease

Now you know how to remove duplicates in Excel using built-in tools, filters, and formulas. Therefore, apply these methods to keep your data clean, accurate, and error-free!

Aravind

Hi, I’m Aravind, a seasoned Automation Test Engineer with 17+ years of industry experience. I specialize in tools like Selenium, JMeter, Appium, and Excel automation. Through this blog, I share practical tutorials, tips, and real-world insights to help testers and developers sharpen their automation skills.