Which Of The Following Can Be Used To Eliminate Multiple Occurrences Of Data?

Excel Remove Duplicates (Table of Contents)

Remove Duplicates in Excel
How to Remove Duplicates in Excel?
- #1 – Using Conditional Formatting followed by Filter feature
- #2 – Using Remove Duplicates feature in Excel
- #3 – Using COUNTIf Function along with Filter feature in Excel

Remove Duplicates in Excel

Remove Duplicates in excel is used for removing the indistinguishable cells of one or multiple columns. This is very piece of cake to implement. To remove duplicates from any column, first select the column/south from where we demand to remove duplicate values, then from the Information menu tab, select Remove Duplicate nether data tools. We will get the window for the same, where we tin can select and unselect the columns from where nosotros want to remove duplicate values.

In that location are three methods of finding and removing indistinguishable data:

Using Provisional Formatting followed by the Filter feature of Excel.
Using the Remove Indistinguishable feature in Excel.
Using the COUNTIF function.

How to Remove Duplicates in Excel?

Let's sympathize the piece of work of removing excel indistinguishable values from a given dataset with the below examples.

You can download this Remove Duplicates Excel Template here – Remove Duplicates Excel Template

You lot tin can remove duplicate records in a very simple and easy manner to excel.

#1 – Using Conditional Formatting followed by Filter feature

For Conditional Formatting

There are indistinguishable occurrences of information in the below table.

Select a range of cells or tables. Become to the Home Tab in the Excel Ribbon. Click on the Provisional Formatting drib-downwardly list. Select Highlight Cells Rules and then click onDuplicate Values.

A indistinguishable Values dialog box volition appear. Select either Duplicate or Unique selection that you lot want to highlight. In this example, we have selected theDuplicate choice.

Choose the colour and font of the highlighted cells in values witha driblet-down list and and so click onOK.

Later using theConditional Formattingfeature, the table will expect like beneath:

To clear Conditional formatting.

Select a range of cells or tables. Go to the Domicile Tab in the Excel Ribbon. Click on the Conditional Formattingdriblet-downwardly list. SelectClear Rules and then click onClear Rules from selected cells.

How to use Filter for finding Duplicate values in Excel?

The Filter characteristic allows a user to filter selected data in the spreadsheet. The Advance filter feature allows copying of the resulting filtered listing to a new location.

Select the column heading of the table.
Go to the Data tab in the Excel Ribbon. Click on Filter.

The table will look similar below:

To remove duplicates from any cavalcade, click on the Filter drop-down icon in the column header. For example, click on Filter by Color and selectFilter by Cell ColororFilter by Font Color.

This volition show duplicated values which you may delete.

The output will be every bit shown below:

How to articulate Filter?

Select the column header of the tabular array. Click onFilter.

Then the filter will exist removed.

Points to Retrieve:

The deletion in the above process is done manually.
Provisional formatting is used to highlight duplicate cells, whereas Filter->Filter by Prison cell Color or Filter by Font Color feature is used to filter highlighted duplicate cells in excel.

#two – Using Remove Duplicates characteristic in Excel

Select the range of cells or table. Side by side, go to the Data Tab in the Excel Ribbon. In the Data tool grouping Clicks onRemove Duplicates.

Remove Duplicates dialog box will appear. To delete duplicate values, select one or more columns that contain possible duplicates. Here in the above case, we accept selected Proper noun andCode nocolumns, as both the columns contain duplicate values, and and then click OK.

A bulletin box will exist displayed, indicatingthe number of indistinguishable values that were institute and removed and how many unique values remain. In case of no duplicate value, information technology will display the message. No duplicate values found, and so click OK.

The output will exist as shown below:

Points to exist Remember:

It is an inbuilt feature in the Data tool of MS-Excel.
It removes Excel duplicate data permanently.

#3 – Using COUNTIf Office along with Filter feature in Excel

A COUNTIF function is an easy method of removal of duplicate values in excel. Depending on whether you desire to notice indistinguishable values with or without first occurrences, at that place will be a slight difference in writing the formula.

A formula to find duplicates in Excel, including the first occurrence:

Duplicate in words

=COUNTIF(range, criteria)>1

Here, a range in column A and the topmost prison cell for criteria is A3. Input the formula shown below in the C3 cell. Then, select the C3 cell and drag to copy the formula till C11. The result volition appear as True or False.

Duplicate in numbers

=COUNTIF(range,criteria)

Hither, Range is column A, and the topmost cell for criteria is A3. Input the formula shown below in the D3 jail cell. Select the D3 cell and elevate to re-create the formula till D11. The result will announced in the count, i.e. number.

A formula to notice duplicates in Excel without get-go occurrence:

If you lot desire to filter or remove excel duplicates after finding them, the above formula will create some defoliation equally it marks True to all the identical records. And then, we accept to use the following formula to keep the listing's unique values and delete all subsequent repetition of values. Let's modify our Excel duplicate formula by using absolute (by freezing the cell by using $ sign), and relative cell references wherever appropriate.

Duplicate in words

Hither the unique value volition be displayed as Fake, whereas the subsequent duplicate values will be displayed as TRUE.

Duplicate in numbers

Hither the unique value will be numbered as ane, whereas duplicate values will be numbered as 2.

Utilise Filter along with the above cases for finding Duplicate values in excel

Select the column header of the table. Go to the Data tab in the Excel Ribbon. Click on the Filter feature.

A dropdown arrow volition appear beside the cavalcade header.

To remove excel duplicates, click on the filter drop-downwardly icon in the column header. DeselectSelect All. Select True and and so click on Ok.

This volition show duplicated values which you may delete.

The same procedure is to be followed while selecting the drib-downwardly filter arrow of column Duplicates in Numbers.Here the deviation is instead of Truthful/False; the choice will be given in numbers, i.due east. i,2.

Points to be Remember:

Hither, data are being removed manually using the correct formula.
A filter is used to make the job easy.

Conclusion

It is ever suggested to copy the original range of information or table to another worksheet or location before deleting or removing duplicates permanently from the worksheet.

Recommended Manufactures

This has been a guide to Remove Duplicates in Excel. Here we talk over how to remove duplicates in excel using three different methods along with practical examples and a downloadable excel template. You may too look at these useful excel tools –