Step by Step Method to Remove Duplicate in Excel

 

Microsoft Excel is broadly utilized application particularly in Business analysis, but it become a little bit confusing when it required to remove or eliminate the duplicate data on excel.

In excel, removing duplicate data from excel from the very huge data is common task for the user.  After you combine numerous tables, and when numerous individuals have get to the same record, then there are many chances of repeating the multiple data insert by the different user.

The bigger the database in excel, the higher chances of experiencing duplicate records. Then the final outcome will portray the incorrect picture and erroneous results, which must be accurately taken care and learn how to remove duplicate values in excel.

We are going outline how to remove duplicate data in excel, by using estimated data of Disease in the world.

How to Find and Remove Duplicate Data

The duplicate data in excel May valuable but we get confused and failed understand it. In order to find, highlight and review the duplicate data before deleting is better than removing all the duplicate data straightway.

1. First of all, select the number of cells containing duplicate values that ought to be removed.
2. At the top tab, click on the option ‘Data’ then ‘Remove Duplicate’ and then checking / uncheck the columns, now after identify you can remove duplicate data in excel.
3. Then click ‘OK’.

 

Technique to Remove Duplicate Data

In Microsoft excel there is built-in tool for the identification of duplicate data and deleting the repeated data from the data base. You can remove duplicate data by the following steps in excel.

Step No. 1

To begin with, select the particular range of data in the table from which you need to remove the duplicates. In case you click on a single cell, the excel will consequently establish the range for selection in the following step.

 

Step No. 2

Next, in the main tab, there is ‘DATA’ tab, where you will find the option in 3^rd Separator, Remove Duplicates, just click on it.

Step No. 3

After the selection, there would appears a dialog box from where you may select the columns you need to evaluate for the duplication of data and further, if your data already with a heading, then select the check box ‘My data has headers’ and then click ‘ok’. Before going onward, after selection of header option, first raw of the data will be selected and not considered for duplicate data.

Step No. 4

At this step, all the duplicate rows will be deleted and display a dialog box of number of count. This dialog box will show the summary of selected data in which the total number of duplicates values found and removed with how many unique values remains there with no changes.

Step No. 5

Now, you can see that automatically number of rows reduced and blank rows are auto selected from where the data deleted.

Let’s go onward, learn and understand that how to use the Advance filter option for the removal of duplicate values.

 

How to Filter Unique Data or Removing of Duplicate Data

In order to list the values either for removal of duplicate value or filter the unique value, both are serve from same procedure.

Though, the difference exist there, upon filtering for unique value, the duplicate data will get temporarily hidden and while the option for removal of duplicate data, it will delete the duplicate data at once. Further, removing duplicate data is removed, by doing the procedural method instead of any shortcut key in excel.

Also, it is very important while finding duplicate data, the format should be same in every aspect.

Steps To Filter The Unique Value

The following steps must follow to filter for unique values.

1. Firstly, select the range of cells in excel.
2. Now, in the main home tab, you will find in last corner the filter option, in drop down menu of Sort & Filter, which is ‘Editing group’ in excel.
3. The popup box will appear in the advance filter option and just click on the filter list for filtering the unique value.

 

How the Advance Filter Options Works

In excel, the ‘Advanced filter’ option is available to filter the duplicate data and copy the unique e data to other location. Following are the steps that you will learn how the advance filter option will work.

• Just click on a first cell of the table or select the whole table data from which you need to remove the duplicates.

• Now, check the ‘advance filter’ option in the ‘Data’ tab

Data Tab -> in the group of ‘Sort & Filter’ -> Advanced

• When you click on the advanced option, the dialog box will open, which shows the list of options for advance filtering.

 

• Option of “copy to another location” must be check mark in order to copy the unique data to the different cell location.

 

• Check the range of your data within the ‘list range field’ in dialog box.

 

• There were two option shown, where you need to select the ‘copy to another location’ filed, and selects the range for copying the unique data values.

 

• In the dialog box, there is check box must to checked ‘Unique records only’ and then click OK.

• Now, you will see that the unique values have been copied to cell H1.

In excel, these are in-built functionalities so that we can easily remove duplicates.

 

Conditional Formatting for Unique Data or Duplicate Data

 

There is auto option available in excel that you would highlight the duplicate data. Whereas, duplicate data cannot be highlighted in Excel in the Pivot Table Report Value area and for other than pivot table, below are the steps to be followed.

• Select the cells you need to check for duplicate data.
• In the home tab, you will find the conditional formatting option for Duplicate values.

Home Tab -> Conditional formatting -> Highlight cell rule -> Duplicate Value

 

• Upon the selection of duplicate values, a new popup box will open.
• In the popup box, there is option shows ‘value with’ option, from where you select the formatting for the duplicate value from the drop down menu and then click ok.

The duplicate cell highlighted in the selected table. It is remember that, using the feature of remove duplicate, will permanently delete the duplicate cell content, so before deleting the duplicate data, you should make backup of your data or make 100% accuracy of identified duplicate data.

Conclude

From this article we have learned multiple techniques and approaches to identify and delete the duplicate records.  If you are interested in learning some special techniques of excel then shortly we will start the complete excel course for our users. Please feel free to ask any question within the comments area. Our experts will get back to you on the same.