How To Remove Duplicates In Excel

6.3 min read|Last Updated: January 6th, 2024|Categories: excel|
table of content

Microsoft Excel is not always used as a calculation tool. We often use it as a database to store different types of information such as sales numbers, inventory management of goods, or even a list of contact information. In this article, we will find how to remove duplicates in Excel and get rid of the data we don’t need.

Using Excel in this manner will inevitably lead to the duplication of data. You’re going to end up with a lot of duplicated entries in rows, such as the same contact information for a person repeated a bunch of times, or the inventory information of a product repeated twice. This type of duplication of data can actually be very detrimental to your operations.

So in this tutorial, we’re going to look at how to delete duplicates in Excel. The methods described here apply to most versions of Microsoft Excel. So let’s get to it.

Remove Duplicate Rows with a Built-in Tool

In Excel version 2007 onwards, there is a built-in tool called Remove Duplicates, which can be quite useful for automatically removing duplicate entries. 

This tool can remove absolute duplicates, which are entire rows or columns that contain duplicate entries or partial records, which removes parts of the entries that are found to be duplicated.

follow these steps to use the tool:

  1. Select the range of data that you want to check for duplicates, or select the entire sheet with Ctrl +A
  2. Navigate to the Data tab and then the Data Tools group. Click on the Remove Duplicates button. 

Note: If you face such a remove duplicates warning window while selecting your range, you need to choose the “Expand the Selection” option, which allows you to remove the duplicated data.

Remove duplicates warning window in excel
  1. In the new window, you can select the columns you want to check for duplicate entries.
  • If you want to delete all the duplicate rows with the same entries, you have to check the box for all of the columns
  • If you’re looking to remove duplicate entries in some columns partially, then only select the checkmark for desired columns. 
  • Select or deselect the My data has headers depending on your data

4. Click OK.

Remove duplicate rows with remove duplicates option under data tab in excel ribbon

You’re done now. All the duplicate entries will be deleted automatically, and the dialogue box will show the number duplicates found and removed.

Please remember that this tool will keep the first unique instance that it finds from all entries. So it will start deleting the records, from the second instance onwards. It will eventually leave one individual record for each entry. If you want to remove ALL duplicate records, you will have to use other tools.

 

 

Move Duplicate Records to Another Location.

With this method, you can separate the duplicate values from the unique and move them to another location.

  1. Select the cell range you want to remove from duplicates or select the whole sheet
  2. Go to the Data tab and Sort & Filter group, then click on Advanced

Note: Here you need to determine the label for each column, otherwise you will face such an error which enables you to keep the process by clicking “OK”.

microsoft excel cannot determine which row in your list or selection contains column labels
  1. In the new dialog box, follow these steps:
    1. Select the Copy to another location button
    2. Make sure the correct range is selected
    3. In