Duplicates in excel data is an inevitable curse. No matter how much careful you are when we receive, share or shift data. Duplicates are bound to happen.
And when you’re dealing with a large dataset of thousands this problem becomes very difficult to solve manually.

In this excel tutorial, we’ll learn how to remove duplicates from excel.

What are duplicates in excel?

Duplicate values are repeated data in your dataset. In a dataset, often rows or cells get repeated. This makes the entire dataset more complicated as any function you’ll use will also take into account the duplicate data.

How to Find Duplicates in Excel

The first step to find the duplicates values in excel so that we can remove them. There are multiple ways to do that. Although, this excel tutorial will only cover how to get rid of duplicates in excel.

If you don’t know how to highlight & find duplicates, we highly recommend you first read “Finding Duplicates In Excel”

Remove Duplicates in Excel

Let’s see how we can get rid of the duplicates in excel.

Method 1: Remove Duplicates from Single Column in Excel

If your duplicate data is present in only one column then let’s see how to remove duplicates in excel column:

1. First select the column containing duplicate values
   
   
   
2. Go to Data. Select Data tools and select the Remove Duplicates feature
   
   
   
3. The Remove Duplicates dialog box will appear.
   1. Make sure ‘My data has headers’ option is checked if the column we selected has headers. If the option is checked, it’ll tell Excel to exclude to the first row while finding and removing duplicates in excel.
   2. Make sure the column which is checked is the one we want to remove duplicates from.
      

4. Click OK

Voila! This will delete duplicates in excel in that specific column. Excel will show you how many duplicate values were removed.

Please Note: Make sure before you remove duplicates, you create a backup copy of original data. As this method would alter your data as cells will shift up. You may want to use Excel Advanced Filter method to get unique values to a different location.

Method 2: Remove Duplicates Excel Advanced Filter

Advanced filter in excel is best for removing duplicate values and also creating a data copy at the same time. Let’s see how to delete duplicates excel using advanced filter:

1. Select the entire data of table.
   
   
   
2. Go to Data tab. Select Advanced filter. The Advanced Filter dialog box will appear.
   
   
   
3. First, select the ‘Unique record only’ check box.
   
   
   
4. In Action, you have 2 option. “Filter the list, in-place” will just remove the duplicates and update the selected dataset.
   While ‘Copy to another location’ will first copy the selected data to another location and remove duplicates from the same. This will keep your original data intact.
   
   
5. If you have selected ‘Copy to another location’ then the ‘Copy to’ box will be enabled. You then would have to select where you want to copy this data.
   
   
   
6. Once done, hit OK

This will eliminate duplicates in excel as well as keep your old data intact if you select copy to another location.

Method 3: Remove Duplicate Rows Excel

In a lot of cases the same row gets duplicated multiple times. Maybe due to an entry error where someone might have made an entry twice in the dataset. In such a case, we don’t want to just remove duplicates in one column but delete duplicate rows in excel. Here’s how:

1. First, select the entire data.
   
   
   
2. Go to Data tab. Select Remove Duplicates.
   
   
   
3. In the Remove Duplicates dialog box. Make sure you’ve selected all the columns. As well as check the ‘My data has headers’.
   
   
   
4. Hit OK
   

Now if you see your data those duplicate rows are now gone. This is how to delete duplicate rows excel.

Conclusion

Duplicate values are an inevitable part of working with large data. Luckily removing duplicates in excel is now much easier thanks to the different methods. If you would like to learn more do read Finding Duplicates In Excel”

I hope this article was helpful to you and explained you in depth how to remove duplicates excel and working with duplicates in excel.If you have any questions or tips, I’d love to hear them in the comment section below.