how to delete duplicate in excel

Data Management is the best term to explain Excel uses. You can use Excel in multiple ways, whether for your data management, data accuracy, data analysis, and a lot of other uses you can make for your offices, home, and personal uses as well. However, if you are using this Excel so you should know about every tool. Unfortunately, we cannot guide you about all the options or tools of Excel in one Article, but we will tell you today “how to delete duplicate in Excel” which has so much importance especially if have a large amount of data and you don’t want to any duplicity of data. For this, you can follow this guide from which you can find your duplicate in an easy way in a large amount of data and also can remove the duplicate items from your Excel data. Here’s how to do so;

How to Find Duplicate in Excel

Excel has an in-built tool to find duplicate items in Excel, and that makes the process very easy. Let’s try it out: 1. First, choose all the cells in the name and employee number columns. Make sure that you open the Home tap, and select “Conditional Formatting”> Highlight Cells Rules>Duplicate Values.

How to Find Duplicate in Excel

2. Doing so will open a new window. Now in the drop-down below the text Format Cells that contain: select Duplicate.

Find Duplicate in Excel

3. Next, select any highlighting options such as we will select here “Yellow Fill with Dark Yellow Text and click OK. When you scroll through the list, you’ll see that any call that matches another cell exactly has been highlighted in yellow.

Yellow Fill with Dark Yellow Text and click OK

5. You will notice that here the duplicate items has been highlighted with the other items which have no duplicity.  Some of the duplicates are far apart, and that’s not convenient. Let’s filter the unique names so we can only see the duplicated items. To filter this, you need to simply right-click on a cell that is highlighted, and then choose Filter>Filter by Selected Cell’s Color.

Yellow Fill with Dark Yellow Text

6. You can also add a filter from the data tab. Select Filter, which will add the filer arrows to the top cells in each column. Click on this arrow in the name column, and choose Filter by Color, then select the dark yellow color.

yellow color

7. Now, you’ll see only the highlighted cells, and you can deal with them how you wish. This was easy enough? Whatever, go ahead, we will cover some strategy to remove these duplicates now, and otherwise, there is no benefits to explain the long theory of finding the duplicates in Excel.

finding the duplicates in Excel

How to Remove Duplicates Value with Advanced Filters

In actual, there are various ways to delete or remove the duplicates in your data form from the ribbon. One of the ways is doing it from the advanced filters. Let’s see how you can do so:

1. Choose a cell inside the data and go to the Data tab and click on the Advances filter command. Doing so will open up the Advances filter window.

2. Next, you can choose to either Filter the list in place or Copy it to another location. When you filter the list in place will hide rows containing any updates while copying to another location will create a copy of the data.

How to Remove Duplicates Value

3. Excel will guess the range of data, but you can adjust it in the List range. The criteria range can be left blank and the copy to field will need to be filled if the copy to another location option was chosen.

4. Now, check the box for unique records only and press OK and you will eliminate the duplicate values.

How to Remove Duplicates Value with Advanced Filters

The advanced filter can be a handy option for getting rid of your duplicate values and creating a copy of your data at the same time. But the advanced filter will only be able to perform on the entire table.

How to Remove Duplicates Value using Remove Duplicated Command

1. Choose a cell inside the data from which you want to remove duplicates and go to the Data tab and click on the Remove Duplicate command. Excel will choose then select the entire set of data and open up the “Remove Duplicates window.”

Remove Duplicates Value using Remove Duplicated Command

2. Now, you need to specify that if the data contains column headers in the first row. If this is checked, then the first row of data will be excluded when finding and removing duplicate values.

3. You can then select which column to use to determine duplicate. You can choose here, Select All and Unselect All button above you can use it you’ve got a long list of columns in your data.

use to determine duplicate

4. When you press OK, Excel then removes all the duplicates values it could find and present you with a summary count of how many values were removed and how many values remain. This command will alter your data while retaining the original data intact.

Remove Duplicates window


Leave a Reply

Your email address will not be published. Required fields are marked *